VBA Newbie: Help with Do Loop code
 

I am extremely new to VBA and Macros, so any help would be greatly
appreciated.

Scenario:
Lets say I have 2 coulumns in an excel spreadsheet, lets call them A
and B.

Coulumn A contains names of a company's various departments. The
length of this column can vary each time the spreadsheet is produced.

I have a macro to run a VLOOKUP based on the contents of column A and
enters the required result in column B.

At present when I run this macro only the first cell (B1) has data
inputted.
How can I get the macro to repeat itself until it gets to the bottom
of column A (the first empty cell).

I assume that I need to use some form of Do Loop, but cannot get the
code right.
Sub Contact()
'
' Contact Macro
' Macro recorded 01/12/2004 by '
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,PERSONAL.XLS!Division_Lookup_Table,2 ,FALSE)"

End Sub

Hi Carl
just a question upfront: Why not copy this formula manually or just double
click on the lower right corner of the cell B1 and the formula copies down
for all filled rows

"Carl" wrote:

I am extremely new to VBA and Macros, so any help would be greatly
appreciated.

Scenario:
Lets say I have 2 coulumns in an excel spreadsheet, lets call them A
and B.

Coulumn A contains names of a company's various departments. The
length of this column can vary each time the spreadsheet is produced.

I have a macro to run a VLOOKUP based on the contents of column A and
enters the required result in column B.

At present when I run this macro only the first cell (B1) has data
inputted.
How can I get the macro to repeat itself until it gets to the bottom
of column A (the first empty cell).

I assume that I need to use some form of Do Loop, but cannot get the
code right.
Sub Contact()
'
' Contact Macro
' Macro recorded 01/12/2004 by '
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,PERSONAL.XLS!Division_Lookup_Table,2 ,FALSE)"

End Sub

Try this;

Option Explicit
Private Sub MyLoop()
Dim Row As Integer
Do Until Cells(Row, 1) = "" 'Loop until blank cells is found.

Cells(Row, 2).Selection 'Select the 2nd column in that row.
'Use the Cells(Row,Col) to change the formula.
ActiveCell.Formula =
"=VLOOKUP(Cells(Row,1),PERSONAL.XLS!Division_Looku p_Table,2,FALSE)"

Row = Row + 1 'increment the Row number by one.
Loop
End Sub

"Frank Kabel" wrote:

Hi Carl
just a question upfront: Why not copy this formula manually or just double
click on the lower right corner of the cell B1 and the formula copies down
for all filled rows

"Carl" wrote:

I am extremely new to VBA and Macros, so any help would be greatly
appreciated.

Scenario:
Lets say I have 2 coulumns in an excel spreadsheet, lets call them A
and B.

Coulumn A contains names of a company's various departments. The
length of this column can vary each time the spreadsheet is produced.

I have a macro to run a VLOOKUP based on the contents of column A and
enters the required result in column B.

At present when I run this macro only the first cell (B1) has data
inputted.
How can I get the macro to repeat itself until it gets to the bottom
of column A (the first empty cell).

I assume that I need to use some form of Do Loop, but cannot get the
code right.
Sub Contact()
'
' Contact Macro
' Macro recorded 01/12/2004 by '
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,PERSONAL.XLS!Division_Lookup_Table,2 ,FALSE)"

End Sub

Additionally, before you run that code inside a DO loop, please note
that this section of the syntax
ActiveCell.FormulaR1C1 = etc
instructs Excel to generate the VLOOKUP formula in Row 1 Column 1, or
cell A1.

Alternatively, you might copy cell A1, select a desired range, and
paste into that range. The code to copy cell A1 and paste into cells
A6 thru A9 looks like this:
Range("a1").Select
Selection.Copy
Range("a6:a9").Select
ActiveSheet.Paste