VLOOKUP

N

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.
 
D

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.
 
N

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.
 
N

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top