VBA Code to populate to end of data

[bopha99]

Hi all,

I am working on a VBA code that I would like to have run data to the end of rows in a column. The number of rows change with every different spreadsheet that I would like to run the vba code on. Here is my code:

ActiveCell.FormulaR1C1 = "ISM"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Custom Input Symbols_2013-09-18 103538.csv'!C1:C3,3,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H500").End(xlUp)

How do I get this code to run to the end of each worksheet? What do I add/change? Thanks in advance!

[p45cal]

untested (only two lines needed I think):

ActiveCell.FormulaR1C1 = "ISM"
Range(Range("H2"),Range("H2").end(xldown)).FormulaR1C1 = "=VLOOKUP(RC[-1],'Custom Input Symbols_2013-09-18 103538.csv'!C1:C3,3,FALSE)"

[bopha99]

Hi p45cal,

I tried your VBA code that you suggested about how to get the code to run a vlookup to the bottom of my data in column G, where the vlookup in in column H. The problem is, the VBA code runs to the very bottom of the worksheet. How do you make the VBA code run to only the last row in column G so that the vlookup in column H stops at the very last row of data in column G? It would also be great if the word "ISM" could be in H1 which would be above the vlookup output. Thanks for your help.

[p45cal]

try:

Range("H1").value = "ISM" 
Range(Range("G2"),Range("G2").end(xldown)).offset(,1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Custom Input Symbols_2013-09-18 103538.csv'!C1:C3,3,FALSE)"

[bopha99]

Great! The new VBA code works! Thanks alot for your help.