Code need rectification

libin baby · May 25th 2016

Hi,
Below code works for me as desired.. It gives me output in Cell Q2:Q.
But i need the out put in PASTE SPECIAL format? any good to approach this?

Code

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet


'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
   OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
   .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub

Display More

skywriter · May 25th 2016

Re: Code need rectification

With paste special you need to copy and then choose the options when pasting.

Options include things like paste values or paste formulas etc.

So what do you want to copy, where do you want to paste it and what options do you want?

Fuzz-Head · May 25th 2016

Re: Code need rectification

PasteSpecail isn't something you can use with VLookup in a cell formula to my knowledge, However, you can add to your code and copy the range of Q and then re paste is as pastespecial if trying to strip the formula.

libin baby · May 25th 2016

Re: Code need rectification

Hi sky writer,
The below code

Code

.Range("Q2:Q" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

After the vlookup it Gives me the value ( with formula in formula bar) in cloumn Q..
I just need the formula in each cell of Q not to be visible... In short i need my macro to paste special the values in each cell of Q after vlookup.

cytop · May 25th 2016

Re: Code need rectification

Just overwrite the range with the values after inserting the formula... this will delete the formulas but keep the result.

Code

.Range("Q2:Q" & OutputLastRow).Formula = _ 
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)" 


.Range("Q2:Q" & OutputLastRow).Value = .Range("Q2:Q" & OutputLastRow).Value
End With

Code need rectification

Participate now!

Share