Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Variable in a Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Variable in a Formula
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 -- Regards, Tom Ogilvy "VexedFist" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Variable in a Formula
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 "VexedFist" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a variable in a formula | Excel Discussion (Misc queries) | |||
formula containing a formula-dependent variable - how? | New Users to Excel | |||
Using a variable in a VBA formula | Excel Discussion (Misc queries) | |||
Help With a Variable Formula | Excel Programming | |||
Variable in a formula | Excel Programming |