Program VLookup
 

Hi. Is there a way to have excel scan a column, and if a cell in column A
is not blank, then insert a Vlookup formula in the same row, column C. If
the cell in column A is blank, then do nothing and move to the next cell.
Thanks!

Program VLookup
 

somethinglike

Sub Macro1()
Dim cl As Range
For Each cl In Range("A:A")
If Not IsEmpty(cl) Then
ActiveCell.Offset(0, 2).Formula =
Application.Worksheetfunction.VLookup(....)
End If
Next cl
End Sub


http://www.excel-ant.co.uk

Steph wrote:
Hi. Is there a way to have excel scan a column, and if a cell in column A
is not blank, then insert a Vlookup formula in the same row, column C. If
the cell in column A is blank, then do nothing and move to the next cell.
Thanks!

Program VLookup
 

One more--if the cells in column A are filled with constants--not formulas.

Option Explicit
Sub testme01()

Dim myRng As Range

With Worksheets("sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("A:A").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
myRng.Offset(0, 2).FormulaR1C1 _
= "=vlookup(rc[-2],sheet2!c1:c5,2,false)"
End If
End With

End Sub

After you run it, take a look at one of the formulas in the cells. You'll see
how that R1C1 stuff gets translated back to A1 reference style.



Steph wrote:

Hi. Is there a way to have excel scan a column, and if a cell in column A
is not blank, then insert a Vlookup formula in the same row, column C. If
the cell in column A is blank, then do nothing and move to the next cell.
Thanks!

--

Dave Peterson