Help tweaking code to set variable

samst

Board Regular
Joined
Feb 12, 2003
Messages
69
Good Morning All

The follwing bit of Code (Thanks Tod!) Sets cell A2 in the sheet called "Temp" to a Variable. Then looks at the sheet called Data and looks for the column Header "Name" and if it finds it It pastes the contents of A2 into the first available row in in the column Name.

Here's my problem: The code only seems to work for the cell A2 how Can i make it work for the rest of the cell in column A? I've made multiple attempts and have been unuccessful. I also was not able to find anything on the board as I'm not really sure what to look up.

ANy thoughts would be appreciated. ANd please let me know if I didn;t make any sense.
Thanks a mill.



s1Value = Workbooks("Book1.xls").Worksheets("Temp").Range("A2").Value
Set FindHeading = Workbooks("Book1.xls").Worksheets("Data").Rows(1).Find("Hotel Name")
If Not FindHeading Is Nothing Then
Workbooks("Book1.xls").Worksheets("Data").Activate
ActiveSheet.Range(Cells(65536, FindHeading.Column) _
.Address).End(xlUp).Offset(1, 0).Value = s1Value
Else
On Error Resume Next
End If
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
This should do what you need. If will cycle through each cell within A2:A???(Last used cell in column A).

Hope this helps.

Dim R as range
for each r in activesheet.range("a2",activesheet.range("A65000").end(xlup))
Call your code
Next
 

samst

Board Regular
Joined
Feb 12, 2003
Messages
69
That's perfect! Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,558
Members
414,077
Latest member
ammylar5

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