PDA

View Full Version : [SOLVED] VBA EXCEL Range syntax



equalizer
05-22-2015, 01:25 PM
I don't understand syntax for range.
Why does this work:

For i = 1 To 10
Range("A" & i & ":D" & i).Copy
Next
But this doesn't work:


For i = 2 To lastRow
num = WorksheetFunction.Match(Cells(i, 1), Range("A" & lastRow), 0)
Next


Why do I need to use


For i = 2 To lastRow
num = WorksheetFunction.Match(Cells(i, 1), Range("A" & lastRow), 0)
Next




What A1:A mean? Why can't I use

Range("A" & lastRow), 0

SamT
05-22-2015, 01:34 PM
Range("A1:A" & LastRow) is the big Range from (A1 to An) Where n = LastRow (Range("A1:An")

Range("A" & LastRow) is the sinlge cell at Range("An")

Range ("A1" & n) is the single cell at Range("A1n") ie if n = 13 then ("A113")

Note that when you use Range("X" & LastRow) then VBA is converting the Long (number) to a String. ie If LastRow = 3, then VBA converts LastRow to "3".

mperrah
05-22-2015, 02:10 PM
Well put SamT,
The use of a loop is usually to look in a range of values form a start point to an end point.
using "A" & lastrow will only look in a single cell.
using "A1:A" & lastrow) will look from A1 to A(lastrow) a range of cells, not just 1

For i = 1 To 10 Range("A" & i & ":D" & i).Copy Next
this will copy A1 to D1, then A2 to D2 and so on


For i = 2 To lastRow num = WorksheetFunction.Match(Cells(i, 1), Range("A" & lastRow), 0) Next
this is saying to find Cell(2,1) in the range(A(lastRow) the first pass
then find Cell(3, 1) in range(A(lastRow) the second pass : it only looks in one cell for the match each pass (maybe that's the goal here?)

mperrah
05-22-2015, 02:15 PM
if there is nothing else in the row you can use this to copy all the cells:

Sub vbax52593C()
' duplicate A and D row and shift down
Dim x, lr

lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = lr To 2 Step -1
If Cells(x, 2).Value = "A" Or _
Cells(x, 2).Value = "D" Then
Rows(x).Copy
Rows(x).Offset(1).Insert shift:=xlDown
Application.CutCopyMode = False
Else
'MsgBox ("No match at " & x)
End If
Next x
End Sub

equalizer
05-23-2015, 07:06 AM
I obviously made an copy/paste error in question on third block of code

Why do I need to use should state


For i = 2 To lastRow
'num = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)
Next

I was asking why I needed format "A1:A" instead of just "A".

You two have explained it. The formatting style was confusing me.

Thanks