Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks

Try the below which will auto adjust depending on the number or rows in
Column J

Sub MyMacro()
Dim lngRow As Long
lngRow = Cells(Rows.Count, "J").End(xlUp).Row
Range("K4:K" & lngRow).Formula = _
"=IF(ISERROR(VLOOKUP($J4,Sheet2!$A$1:$B$56,2,FALSE ))," & _
""""",VLOOKUP($J4,Sheet2!$A$1:$B$56,2,FALSE))"
Range("K4:K" & lngRow).Value = Range("K4:K" & lngRow).Value
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Wanna Learn" wrote:

Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks

If you are willing to select the range:

Selection
="=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FAL SE)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE)) "
Selection = Selection.Value

will replace the entire macro. If you are not we will need to know how you
know how far down to fill the data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Wanna Learn" wrote:

Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks

Can you pick out a column that can be used to determine the last used row (how
far to fill the formulas)??

I used column A:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim myRng As Range

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("K4", .Cells(LastRow, "K"))
With myRng
.FormulaR1C1 _
= "=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E))," _
& """"",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE)) "
.Value = .Value
End With
End With
End Sub



Wanna Learn wrote:

Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks

--

Dave Peterson

Your macro may be GREATLY simplified. What determines the last row to copy
to in col K.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wanna Learn" wrote in message
...
Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run
everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks

Sub foriformulas()'to last row in col J
For i = 4 To cells(rows.count,"J").end(xlup).row
Cells(i, "k").Value = Application.VLookup(Cells(i, "j"), _
Sheets("Sheet2").Range("a1:b56"), 2, 0)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Your macro may be GREATLY simplified. What determines the last row to copy
to in col K.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wanna Learn" wrote in message
...
Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run
everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks