Code need rectification

[libin baby]

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?

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

 

[NARAYANK991]

Hi ,

I am not sure I know what you want ; when ever you want that the formulae should be replaced by their values , just add the following line of code :

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

This will replace the formulae by their calculated values.

Narayan

 

[jindon]

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?

Just my guess...

Sub test()
    Dim r As Range, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Sheets("sheet1")
        For Each r In .Range("a1", .Range("a" & Rows.Count).End(xlUp))
            Set dic(r.Value) = r(, 2)
        Next
    End With
    With Sheets("sheet2")
        For Each r In .Range("a2:a" & .Range("p" & Rows.Count).End(xlUp).Row)
            If dic.exists(r.Value) Then
                dic(r.Value).Copy r(, 17)
            Else
                r.ClearContents
            End If
        Next
    End With
End Sub

 

[jindon]

Noticed a bug..

                r.ClearContents

should be

                r(, 17).ClearContents

 

[libin baby]

Hi all
The below 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