w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Copying a String Array into a Range of cells (in MS Excel using VBA)
Like this Sub StringArrayToRange() Dim strArr(3) As String strArr(0) = "one" strArr(1) = "two" strArr(2) = "three" Range("A1:A" & UBound(strArr) + 1) = WorksheetFunction.Transpose(strArr) End Sub also, this for more examples and tutorial EDIT: this documentation explains why the WorksheetFunction.Transpose was used

Categories : Excel

copying range of cells and pasting to the first empty row more effective code? Excel
First, you don't have to use .Select or .Activate on cells/worksheets that you'll manipulate, just reference them directly. That can really slow down your code. The .Copy method can take a Destination parameter, so you don't have to use .Paste somewhere else. Also, the .End property isn't very reliable. It's better to use UsedRange instead. You can do that pretty much in only 1 line. Worksheets("New Search").Range("A3:J3").Copy Destination:=Worksheets("Past Searches").UsedRange.Columns(1).Offset(1, 0)

Categories : Excel

Looping through Excel workbooks, copying a range of cells from each and pasting to a master workbook
foreach (string stdFile in fileNames); The semi-colon at the end of this line terminates the statement immediately, so stdFile is not available in the code further down. Remove the semi-colon. There is a straight-forward introduction to Excel Interop here at dotnetperls.

Categories : C#

Excel Compare two cells in one sheet to two in another, copy a certain range of cells if they are equal
edited July 23 - 10:30EST no VBA or macro needed you can use simple formulas and some copy and paste on sheet2, at the end of the data columns, create a new column (AD?) with this formula =IF(AND(IFERROR(VLOOKUP(A4,[book1]sheet1!$C$4:$C$14,1,FALSE),"")<>"",IFERROR(VLOOKUP(D4,[book1]sheet1!$L$4:$L$14,1,FALSE),"")<>""),[book1]sheet1!T4,X4) and copy it over the next 6 rows (AD to AI) I assumed the first row being 4 and the formula is searching 10 rows in sheet 1, adjust as needed then copy down to last of your data rows if you have a match, the sheet1 data will be displayed otherwise, sheet2 data will be there copy this chunk, go to X4 and use EDIT - PASTE SPECIAL - VALUES if excel 2003 or click the PASTE button on upper left and choose PASTE VALUES if Excel 2007+ I rec

Categories : Excel

Excel macro not copying the data into the right cells
Here you have a simple code copying (and transposing) a range between two sheets. Dim fromRange As Range, toRange As Range Set fromRange = Sheets("Sheet1").Range("A1:A3") Set toRange = Sheets("Sheet2").Range("A1") fromRange.Copy toRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True You can call this code recursively (while iterating through all the columns in the original range). Bear in mind that ranges can also be defined through Cell and thus: Set fromRange = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(3, 1)) This code is identical to the Set fromRange above, although this one might be more helpful when being in a loop. For example: Dim iniRow As Integer, maxRow As Integer, col As Integer, maxCol As Integer iniRow = 1 maxRow = 20 col = 0 ma

Categories : Excel

copying different ranges of cells in excel according to styling
You have full control on all the cells in the PivotTable. Sample code: Dim curpivottable As PivotTable Set curpivottable = ActiveSheet.PivotTables(1) Dim curRange As Range Set curRange = curpivottable.TableRange1 For Each cell In curRange.Cells If (cell.Font.Bold) Then 'This is a bold cell End If Next cell Bear in mind that curRange includes all the contents in curpivottable, from the first row (where the title is). -- UPDATE My answer above was the generic way to deal with ranges in PivotTables. If all what you want is just copying/pasting a range, you can do it directly (is the same if the range is in the PivotTable or not). Example: Dim origRange As Range Set origRange = Sheets("Sheet1").Range("A2:A500") Dim destRange As Range Set destRange = Sheets("Sheet2").Range

Categories : Excel

Searching for matching cells and copying row data
This should get you started. Was the statement within the If block a typo? I think you might have meant to copy from one workbook/worksheet to the other (not from the same one)? Sub Firm_Number_Transfer() Dim i As Integer Dim x As Integer Dim row As Integer Dim oldRow As Integer Dim found As Boolean Dim xlBook As Workbook, xlBook2 As Workbook Dim xlSheet As Worksheet, xlSheet2 As Worksheet Dim rng As Range, cell As Range, rng2 As Range, cell2 As Range xlBook2 = Workbooks("PM Firms - Step 1 - REVEIWED") xlSheet2 = xlBook2.Worksheets("Sheet1") Set rng2 = xlSheet2.Range("B2:B4843") xlBook = Workbooks("PM Firm Contacts - Step 2 - REVIEWED") xlSheet = xlBook.Worksheets("Sheet2") Set rng = xlSheet.Range("B

Categories : Excel

VBA Blank Cells offsetting data when copying
"I think i have some of the syntax wrong" is not helpful. You need to tell us the error message you receive and the line that this error refers to. But this is probably wrong: WS.Range(.Cells(10, 7)) unless .Cells(10, 7) happens to contain a cell address. If you want further assistance you need to ask a specific question. Added If Cells(10, 7) = "" Then I assume this should be looking in the "Macro Test Sheet" so this should also be preceded by a dot, otherwise it is looking in whatever is currently the ActiveSheet.

Categories : Vba

Copying Dynamic Cells/Rows Into New Sheet or Workbook
assuming your data is grouped, ie, not something like: batman batman robin robin batman but instead: batman batman batman robin robin Then this will grab all contiguous chunks and shove them in their own workbook. It also assumes everything is in column A, so change that if you need to. Sub grabber() Dim thisWorkbook As Workbook Set thisWorkbook = ActiveWorkbook last = 1 For i = 1 To 18 'my sample had 18 rows, replace it with how many you have If Range("A" & i) <> Range("A" & (i + 1)) Then Range("A" & last & ":A" & i).Copy Set NewBook = Workbooks.Add NewBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues last = i + 1 thisWorkbook.Activate End If Next i End Sub

Categories : Vba

vba range (cells(), cells()) copy past to other worksheet with out activating the other worksheet
You didn't really give us a lot to go on here, so I'll post one way and see if it gets you going: Sub test() Dim columnReferenceSource As Long Dim rowReferenceSource As Long Dim columnReferenceDest As Long Dim rowReferenceDest As Long Dim r As Range rowReferenceSource = 696 columnReferenceSource = 60 rowReferenceDest = 23 columnReferenceDest = 60 Set r = Range(Sheets("A").Cells(rowReferenceSource, columnReferenceSource), Sheets("A").Cells(rowReferenceSource, columnReferenceSource + 18)) r.Copy Destination:=Sheets("B").Cells(rowReferenceDest, columnReferenceDest) End Sub Obviously you'll have to set the row and column numbers for your situation. If you can provide more details as to what you're trying to do, I'm sure we can give a m

Categories : Excel

copying and pasting range depending on a condition
For Each c In Worksheets("fsr").Range("O:O") 'This will slow your macro as Excel will search all 1048576 rows 'I suggest you define the range of data in another sheet Starting Row | 1 Ending Row | 30 Sub The_One() startrow = Worksheets("Sheet1").Cells(1,1) endrow = Worksheets("Sheet1").Cells(2,1) For x = startrow to endrow If Cells(x,"O").Value = "p" Then Range("E" & x, "H" & x).Copy Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "C").PasteSpecial xlPasteAll End If Next End Sub

Categories : Excel

Excel with c#: Exculde first rows when copying a range
You could try to get it with something like this: Excel.Range dataWithoutFirstRow = mySheet.Range[mySheet.UsedRange.Cells[2, 1], mySheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)];

Categories : C#

Copying values from range in Excel to bookmark in Word
This is of course the important part: With objWord.ActiveDocument .Bookmarks("monthtable").Range.Text = ws.Range("C6").Value ' here i need range of values to be selected instead of a single cell End With Here you need to cycle through the ws.Range("..."), and for each cell in that range, concatenate that value onto the .Bookmarks.Range.Text value (Rather than setting it equal, which would overwrite). It's probably a good idea to first concatenate this into a string variable and then set the bookmark to that string variable's value, since that would avoid some potential interop issues.

Categories : Excel

Excel VBA -Copying a range of multiple rows to another sheet
I could get the following code to copy as expected. This was on Excel 2007- it could be that you're on an earlier version that behaves differently. Public Sub Test() With Sheets("XYZ") .Range("8:8,9:9,10:10,11:11,12:12,13:13,266:266,472:472").Copy .Cells(.Cells(.Cells.Rows.Count, 1).End(xlUp).Row + 1, 1).Select .Paste End With End Sub

Categories : Excel

How prevent copying web application interface code (HTML+JavaScript)
Everything that can be obfuscated by a process, can be de-obfuscated by another process. How safe is it? It is not. But you have not explained what the threat model is. How can it be misused by understanding the javascript? Is the problem confidentiality, integrity or identity? HTTP has something for all of this. Various HTTP-based identity schemes such as OAuth or even basic authentication exists while SSL can provide you with confidentiality and integrity.

Categories : Javascript

SQL Server Express - how to prevent copying database into debug folder?
The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .indebug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end! The REAL solution in my opinion would be to install SQL Server Management Studio Express (if you haven't already) create your database in SSMS Express, give it a logical name (e.g. MyDatabase) connect to it using its logical database name (given when you create it on the server), and let the server handle all the file-related details; don't mess around with physical files and user instances. In that case, your connection string wo

Categories : C#

How do I prevent a jQuery extend deep copy from copying some of my custom properties?
Properties added through Object.defineProperty with the enumerable argument set to false won't be caught by jQuery.extend. a = { b: 'b'}; Object.defineProperty(a,'bb',{ get: function() {return "bb";},enumerable: false}); c = {}; jQuery.extend(c,a,a,a,a); a.b // "b" a.bb // "bb" c.b // "b" c.bb // undefined

Categories : Javascript

Function using IF and AND and OR on a range of cells
Beware, not properly tested by me!: =--AND(NOT(ISERROR(VLOOKUP("stringA",A:A,1,0))),OR(NOT(ISERROR(VLOOKUP("stringB",A:A,1,0))),NOT(ISERROR(VLOOKUP("stringC",A:A,1,0))))) Note I have covered the entire column cell1:cell10 (I assumed ColumnA) but range can be restricted to A1:A10 say, if desired.

Categories : Excel

Add cells to range of columns only
Sub Add_Project() Dim strNewProject As String Dim iRow As Long strNewProject = InputBox("Enter Project Name") If Len(strNewProject) = 0 Then Exit Sub 'Pressed cancel If WorksheetFunction.CountIf(Columns("B"), strNewProject) > 0 Then MsgBox "Project already exists" Exit Sub End If iRow = WorksheetFunction.Match(strNewProject, Columns("B")) + 1 Intersect(Range("A:K"), Rows(iRow)).Insert xlShiftDown Cells(iRow, "B").Value = strNewProject End Sub

Categories : Excel

How do I get a range of cells in PHPExcel?
There is, the rangeToArray() method: $objPHPExcel->setActiveSheetIndex(0)->rangeToArray('A1:C3'); Wondering why I bother documenting these methods in the first place, but here's the argument list as well: /** * Create array from a range of cells * * @param string $pRange Range of cells (i.e. "A1:B10"), * or just one cell (i.e. "A1") * @param mixed $nullValue Value returned in the array entry * if a cell doesn't exist * @param boolean $calculateFormulas Should formulas be calculated? * @param boolean $formatData Should formatting be applied to cell values? * @param boolean $returnCellRef False - Return a simple array

Categories : PHP

Clear cells out of the used range
You can delete everything outside of your actual used range. You can either do this manually with selecting a row and ctrl+shift+down (and ctrl+shift+right with a column selected) or you can do it programatically by identifying where your data actually ends (either via a COUNTA or xlRight style of command) and then delete everything after those points. The manual way is good for if it's just a one one off cleanup operation, and if you're doing it in the macro then you may as well just use the method for determining the range to keep to go straight to selecting it, and not bothering to delete outside of the range.

Categories : Excel

Why does Range work, but not Cells?
The problem is that Cells is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Range or Cells or Rows or UsedRange or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to: In a sheet's class module: that sheet regardless of what's active In any other module: the ActiveSheet You qualify the Range reference, but the Cells reference is unqualified and is likely pointing to the Activesheet. It's like writing ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value which of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure ev

Categories : Vba

Need If statement to check range of cells
This is pretty close to what you need. c probabaly should be a variant... With Worksheets("Raw Data").Range("EF4:EF500") Set c = .Find("Barker Library") If Not c Is Nothing Then Call LocationPivot("Barker Library") End If End With

Categories : Excel

VBA seems to disappear after I protect a range of cells
If the VBA changes cells that you have locked then you need to update the VBA to unlock the worksheet before running the rest of the code. Then complete the Sub by locking the worksheet. 'Add to beginning of the Sub Sheets("worksheet_name").Unprotect Password:="password" 'Add to end of the Sub Sheets("worksheet_name").Protect Password:="password"

Categories : Vba

Excel: finding range of cells with value
Something like? =SUMIF(A1:A12,"7/17/2013",B1:B12) Of course change the ranges to match your data, the first range is the criteria range, then the date, then the sum range

Categories : Excel

Select range without filtered cells
There are functions like DAverage and Dsum that allow you to do calculations over a range and specify certain criteria.

Categories : Excel

Find a string on a cells range
Try the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) For i = 1 To Cells(1, 1).End(xlToRight).Column If InStr(Cells(1, i), "Test") Then Cells(2, i) = "OK" End If Next End Sub

Categories : Excel

Get Range of Groups of Cells Excel C++/cli
Worksheet->Cells is supposed to be the entire spreadsheet. You're asking it for a Range^ representing all cells in the entire worksheet, and that's what you get. (Quoting MSDN: "Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).") A Range object simply represents a group of cells; it's not innately tied to sub-tables within a worksheet, like your "A1:C2" and "F1:G2" are. To get the sub-tables, use the CurrentRegion property. I believe the way to do that is to get cell A1 and call CurrentRegion to get A1:C2, and on F1 to get F1:G2. (Obviously, better design would be to have your two sub-tables in their own sheets within the workbook, if possible.)

Categories : Dotnet

IOS 6 with ARC, How to prevent Table View to not ask for new Cells when they are not discarded
You're right that it will always ask for cells that have been "off screen". If you keep strong references to the cells, you will have to release them yourself. You could store the cells in an array or dictionary (indexed by row), and return the existing cell if it's in the list, otherwise create a new cell when iOS asks for one.

Categories : IOS

Macro to execute only if range of cells all contain the word "OK"
In two steps might be simplest: If a contiguous range, say E5:J5 where Row5 is the relevant one, add =COUNTIF(E5:J5,"OK") somewhere in your worksheet, say K5 (adjust formula to suit), then in your macro, test the value in K5 and if not =6 exit with your error message, else proceed to your 'push order' macro.

Categories : Excel

How to Copy-PasteSpecial cells from filtered range
Four things: .SpecialCells(xlCellTypeVisible) returns a reference to a range, but you don't use it You can't use both Destination:= ... and .PasteSpecial with one Copy. Choose one. You mean .PasteSpecial Paste:=xlPasteValues not .PasteSpecial Paste = xlPasteValues You activate and filter sheet "Additional Existing Raw Mat.", then refer to a filter on Sheet12. Are you sure thats right? Update: How to use Copy PasteSpecial .Copy Sheet8.Range("H" & AddRow + 1).PasteSpecial Paste:=xlPasteValues

Categories : Excel

Excel VBA Range Merge Cells and offset
Woah, I'm really surprised this works at all! Range is a keyword in VBA and Excel, so it is very surprising to me you are able to use that as a variable name without problems. You can troubleshoot problems like this a lot easier by adding a debug statement: 'Add month header Debug.Print "Range Address: " & range.Address & vbTab & "i:" & i Call AddCalendarMonthHeader(MonthName(i), range) Debug.Print "Range updated00: " & range.Address 'Add weekdays header Debug.Print "Range updated0: " & range.Address Set range = range.Offset(1, 0) `<---- this is the line where the Offset loses the entire row Debug.Print "Range updated1: " & range.Address This results in the following: Range Address: $B$2:$H

Categories : Excel

Determine range encompassed by merged cells
Try this Sub Demo() Dim r As Range Set r = Range("YourRange") With r.MergeArea MsgBox "Merged Range start row = " & .Row & vbNewLine & _ "Merge Range stop row = " & .Row + .Rows.Count - 1 End With End Sub

Categories : Excel

vba pass a group of cells as range to function
As written, your function accepts only two ranges as arguments. To allow for a variable number of ranges to be used in the function, you need to declare a ParamArray variant array in your argument list. Then, you can process each of the ranges in the array in turn. For example, Function myAdd(Arg1 As Range, ParamArray Args2() As Variant) As Double Dim elem As Variant Dim i As Long For Each elem In Arg1 myAdd = myAdd + elem.Value Next elem For i = LBound(Args2) To UBound(Args2) For Each elem In Args2(i) myAdd = myAdd + elem.Value Next elem Next i End Function This function could then be used in the worksheet to add multiple ranges. For your function, there is the question of which of the ranges (or cells) that can passed

Categories : Excel

Can you use Cells instead of Range inside of an Autofill function?
You're very close. Here's a version with your variables declared (which you really should do) and the Select statements eliminated (also a good practice): Sub ExampleThing() Dim Counter As Long Dim i As Long Counter = 13 For i = 1 To Counter Range("A" & i) = Rnd Next Range("B1").Formula = "=(PI()*A1)" Range("B1").AutoFill Destination:=Range(Cells(1, 2), Cells(Counter, 2)), Type:=xlFillDefault End Sub

Categories : Vba

Display a non contiguous range of cells to a list box
perhaps Private Sub CommandButton1_Click() Dim rng As Range Set rng = Range("A1:E10") With ListBox1 .ColumnCount = 3 .ColumnWidths = "50;50;50" ' load 1st, 3rd and 5th columns of range into listbox .List = Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3, 5)) End With End Sub

Categories : Vba

Excel-VBA Before Save Lock a range of cells
Your code will behave differently depending on the active sheet when saving. Additionnaly, I wouldn't recommend to overload the reserved name "Cell" with a local loop variable. This will lead to unexpected behavior. You should remove references to ActiveSheet. If your wish is to protect the entire workbook, I would suggest iteration over the worksheets: Sub ProtectAll() Dim wSheet As Worksheet Dim myCell As Range For Each wSheet In Worksheets wSheet.Unprotect Password:="****" For Each myCell In Range("H5:H24,J5:J24") myCell.Locked = (myCell <> "") Next myCell wSheet.Protect Contents:=True, Password:="****", UserInterfaceOnly:=True Next wSheet End Sub NB: you have to put the code in a code module.

Categories : Vba

VLOOKUP and fill all cells in a date range
You are making conditions between different types/values (which, logically, do not hold): dispo.Cells(1, c).Value = txtdepart would work if both variables have the same type (and content), that is, string = string or date = date. Apparently you are expecting dates an thus you can use the following code to make completely sure: If Format(CDate(dispo.Cells(1, c).Value), "dd-MM-yyyy") = Format(CDate(txtdepart), "dd-MM-yyyy") Then Equivalent problem for If dispo.Range("A" & l).Value = vehicule Then Both members have to be identical. Above you are writing vehicule = 1 and thus this condition wlll only verify if dispo.Range("A" & l).Value equals 1. No idea what you want to write in the "x" part.

Categories : Excel

Split an Excel range into single cells
Not too clear about your case but I have this .. This will give example to extract multi range to single range .. Sub SplitRange() Dim rn As Range Dim c As Range Set rn = Range("A1:A8") For Each c In rn.Cells MsgBox StrRange(c.Row, c.Column) Next End Sub Function StrRange(ByVal nRow As Single, ByVal nCol As Single) As String Dim sC As String Dim nC, nRest, nDivRes As Integer sC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" nC = Len(sC) nRest = nCol Mod nC nDivRes = (nCol - nRest) / nC If nDivRes > 0 Then StrRange = Mid(sC, nDivRes, 1) StrRange = StrRange & Mid(sC, nRest, 1) & Format(nRow) End Function Hope this help and everyone that need something like this ..

Categories : Excel

Use VBA to copy all visible and non-empty cells in a range
Full admission that I did not try your code, but you can also try the following With Sheets("Sheet1").Range("S2:S5000") Application.Intersect(.SpecialCells(xlCellTypeVisible), _ .SpecialCells(xlCellTypeConstants)).Copy End With Making the open ended assumption that the reason for copying is to paste somewhere else, you can update the above code by using With Sheets("Sheet1").Range("S2:S5000") Application.Intersect(.SpecialCells(xlCellTypeVisible), _ .SpecialCells(xlCellTypeConstants)).Copy _ Destination:= Sheets("destSheet").Range("destRange") End With

Categories : Excel



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.