Edit Macro to select cell using .end(xlup)

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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
Joe:

Thanks for the direction! One question though: the data I need is not in the "active sheet" that is open and which contains the macro.

I replaced

Selection.FormulaR1C1 = "='H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'!R194C2/100"

with your suggestion

ActiveSheet.Range("B65536").End(xlUp).OffSet(1, 0).Select

but I do not get any data when I run the macro.

So, do I need to change

ActiveSheet.Range

to the name I the workbook that contains the data I need?

My macro starts with moving to the cell that need to hold the data

Application.Goto Reference:="R2C3"

now how do I edit my macro to bring back the data I need?

I tried changing ActiveSheet.Range.Range("B65536").End(xlUp).OffSet(1, 0).Select

to 'H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls'.Range("B65536").End(xlUp).OffSet(1, 0).Select

but that did not bring over the data.

A little more help would be great!
Thanks again!
Adam
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
It is not as stright forward as one would think.


Sub myImport()
'Standard module code, like: Module1!
Dim myRng As Range

Workbooks.Open Filename:="H:\Dept\Marketing\FUEL\Weekly Updates\Weekly E-Mail Attachments\2006 Fuel Prices.xls"

Set myRng = Workbooks(2).Sheets("Sheet1").Range("$B$194")

Workbooks(1).Sheets("Sheet1").Range("B65536").End(xlUp).Offset(1, 0).Value = myRng.Value

Workbooks(2).Close
End Sub
 

Forum statistics

Threads
1,137,366
Messages
5,681,071
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top