No. I meant that you don't have to loop through each of the cells in that
column.

It's kind of like selecting B7:B99 and typing the formula for B7 and hitting
control-enter. Excel will fill the rest of the cells with that formula.

Option Explicit
Sub Macro1c()

Dim LastRow As Long

With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("b7:b" & LastRow).FormulaR1C1 _
= "=IF(ISNA(VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE)),""""," _
&
"VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE))"
End With

End Sub

Nena wrote:

Like this...

Sub Macro1c()

Sheets("Sheet1").Select

x = WorksheetFunction.CountA(Range("A2:A65536"))

Range("B7").Select

Dim LastRow As Long
With Worksheets("Sheet1")
'based on the entries in column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B7:B93" & LastRow).FormulaR1C1 _
= "=IF(ISNA(VLOOKUP(RC[-1],'undupcrn'!
R2C1:R65536C5,3,FALSE)=TRUE),""""," _
& "VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE))"
End With

For Record = 1 To x

Next Record

End Sub

Thanks Dave!

How do I get this macro to run through an entire column and not down
the entire row?

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],'undupcrn'!
R2C1:R65536C5,3,FALSE)=TRUE),"""", VLOOKUP(RC[-1],'undupcrn'!
R2C1:R65536C5,3,FALSE))"
ActiveCell.Offset(0, 1).Select

Thanks in advance.

--

Dave Peterson

--

Dave Peterson