VLOOKUP
 

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.

VLOOKUP
 

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

Nena wrote:

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

VLOOKUP
 

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

VLOOKUP
 

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

On Sep 11, 12:12 pm, Dave Peterson wrote:
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).

Nena wrote:

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

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