copy paste values macro

[Wanna Learn]

Hello

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

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE)),"""",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

 

[Jacob Skaria]

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

 

[Shane Devenshire]

If you are willing to select the range:

Selection
="=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE)),"""",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.

 

[Dave Peterson]

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,FALSE))," _
& """"",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
.Value = .Value
End With
End With
End Sub

 

[Don Guillett]

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

 

[Don Guillett]

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