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


    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?

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