Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |