Using a Variable in a Formula

[VexedFist]

Tom Olgivy provided this to find the Last row in a spreadsheet that has
data in it, for another user here on the Forums.

Can anyone tell me how I can use the value "rw' to replace N1000 in the
below Macro?

Sub FindAndUseLastRow()
Dim rw As Long
if not isempty(Range("A" & rows.count)) then
rw = rows.count
else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End if

Range("L2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,1,""-"")"
Range("M2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,2,""-"")"
Range("N2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,3,""-"")"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N1000), Type:=xlFillDefault
Range("L2:N1000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

 

[Guest]

Sub FindAndUseLastRow()
Dim rw As Long
if not isempty(Range("A" & rows.count)) then
rw = rows.count
else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End if

Range("L2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,1,""-"")"
Range("M2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,2,""-"")"
Range("N2").FormulaR1C1 = "='MACRO HiPath 4000
OptiDat.xls'!ExtractElement(RC16,3,""-"")"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N" & rw), Type:=xlFillDefault
Range("L2:N" & rw).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

 

[John]

Range, takes a string so you could convert the rw Long data type to a string
and use that instead:

Sub FindAndUseLastRow()
Dim rw As Long
Dim sRange As String
If Not IsEmpty(Range("A" & Rows.Count)) Then
rw = Rows.Count
Else
rw = Cells(Rows.Count, "A").End(xlUp).Row
End If

Range("L2").FormulaR1C1 = "='MACRO HiPath 4000"
OptiDat.xls '!ExtractElement(RC16,1,""-"")"
Range("M2").FormulaR1C1 = "='MACRO HiPath 4000"
OptiDat.xls '!ExtractElement(RC16,2,""-"")"
Range("N2").FormulaR1C1 = "='MACRO HiPath 4000"
OptiDat.xls '!ExtractElement(RC16,3,""-"")"
Range("L2:N2").Select
sRange = "L2:N" & CStr(rw)
Selection.AutoFill Destination:=Range(sRange), _
Type:=xlFillDefault
Range(sRange).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Hope that helps

Best regards

John