Code need rectification

  • 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?



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

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

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!