VLOOKUP

[Nena]

Quick question

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]

Find the range to fix first and assign the .formular1c1 to all the cells in the
range.

dim LastRow as long
with worksheets("sheet99999")
'based on the entries in column A
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("x2:x" & lastrow).formular1c1 _
="=IF(ISNA(VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE)=TRUE),""""," _
& "VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE))"
end with

Change the range to fix to what you need (I used X2 to X (bottom used row of
column A).

Quick question

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.

 

[Nena]

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.

 

[Nena]

When I run the macro, it gives me a message that reads, "Object
required", any ideas what that can mean?

Find the range to fix first and assign the .formular1c1 to all the cells in the
range.

dim LastRow as long
with worksheets("sheet99999")
'based on the entries in column A
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("x2:x" & lastrow).formular1c1 _
="=IF(ISNA(VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE)=TRUE),""""," _
& "VLOOKUP(RC[-1],'undupcrn'!R2C1:R65536C5,3,FALSE))"
end with

Change the range to fix to what you need (I used X2 to X (bottom used row of
column A).

Quick question

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]

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

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.