adam_neb
Board Regular
- Joined
- Jun 9, 2002
- Messages
- 101
I recorded a macro to reference a cell in another workbook. When I recorded the macro, I used the key strokes to GoTo B65536, then control up-arrow to get to the last cell in column B that had data. I did this because each week a new value is added in column B for each new week. When I ran my macro it pulled fine, EXCEPT when I added new information to Column B. The macro returned an absolute reference.
I looked in my reference book "VBA and Macros for Microsoft Excel" by Mr. Excel, and I see where he shows using Range("B65536").End(xlup), but I am having a bugger of a time getting my macro to return the correct value when Column B has been updated. Here is my macro:
Sub wbfsc()
'
' wbfsc Macro
' Macro recorded 9/27/2006 by adam_neb
'
'
Application.Goto Reference:="R2C3"
Selection.FormulaR1C1 = "='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'!R194C2/100"
End Sub
What changes to I need to make so that it looks for the last populated cell in Column B and return that value? When I run the macro as-is, the results in Cell C2 is
'='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\[2006 Fuel Prices.xls]2006 Fuel Prices'!$B$194/100
Note the absolute reference of $B$194.
Can anyone offer any direction?
Thanks!
Adam
I looked in my reference book "VBA and Macros for Microsoft Excel" by Mr. Excel, and I see where he shows using Range("B65536").End(xlup), but I am having a bugger of a time getting my macro to return the correct value when Column B has been updated. Here is my macro:
Sub wbfsc()
'
' wbfsc Macro
' Macro recorded 9/27/2006 by adam_neb
'
'
Application.Goto Reference:="R2C3"
Selection.FormulaR1C1 = "='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'!R194C2/100"
End Sub
What changes to I need to make so that it looks for the last populated cell in Column B and return that value? When I run the macro as-is, the results in Cell C2 is
'='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\[2006 Fuel Prices.xls]2006 Fuel Prices'!$B$194/100
Note the absolute reference of $B$194.
Can anyone offer any direction?
Thanks!
Adam