VLOOKUP
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
|