Results 1 to 8 of 8

Thread: putting a vlookup into vba code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    635
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default putting a vlookup into vba code

    Hi, good afternoon, hope you can help I have the code below which is part vlookup and part vba but it doesn't work, I want it to go into cell C2 then drag it down to the last column, but all it does is paste the same code into each cell and does nothing hope you can help.
    [CODE]Private Sub CommandButton1_Click()

    Dim lr As Long

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("C2:C" & lr)
    .Formula ="=VLOOKUP(A2,Old!B:C,2,FALSE)"


    .Value = .Value

    End With

    End Sub




    [/CODE]

  2. #2
    Board Regular WaterGypsy's Avatar
    Join Date
    Jan 2010
    Location
    London, England
    Posts
    676
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: putting a vlookup into vba code

    I would just put the code for the formula into C2, then use Autofill to copy it down. Something like:

    Code:
    .
    .
    Range("C2").Value ="=VLOOKUP(A2,Old!B:C,2,FALSE)"
    range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & Ir)
    

  3. #3
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    635
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: putting a vlookup into vba code

    HI thank you for the response I have tried the code, thank you but I get an error on the line below, it gets highlighted in yellow
    Code:
    Selection.AutoFill Destination:=Range("C2:C" & Ir)
    this is the whole code
    Code:
    Private Sub CommandButton1_Click()
      Dim lr As Long
      Dim copySheet As Worksheet
      Dim pasteSheet As Worksheet
      Set copySheet = Worksheets("Jun")
      Set pasteSheet = Worksheets("Jun")
      
      Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
      On Error Resume Next
      Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
      On Error GoTo 0
    Range("AP2", Range("AP2").End(xlDown)).Copy Range("A2")
    Range("A2", Range("A2").End(xlDown)).NumberFormat = "0"
    Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
    Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
    Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
    Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
    Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
    Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
    Range("C2").Value = "=VLOOKUP(A2,Old!B:C,2,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & Ir)
    End Sub

  4. #4
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    635
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: putting a vlookup into vba code

    hi again the error says ' Run-time error 1004, method range of object worksheet failed

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,876
    Post Thanks / Like
    Mentioned
    384 Post(s)
    Tagged
    41 Thread(s)

    Default Re: putting a vlookup into vba code

    Try
    Code:
    Range("C2:C" & LastRow).Formula = "=VLOOKUP(A2,Old!B:C,2,FALSE)"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    635
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: putting a vlookup into vba code

    this is great many thanks for your help

  7. #7
    Board Regular WaterGypsy's Avatar
    Join Date
    Jan 2010
    Location
    London, England
    Posts
    676
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: putting a vlookup into vba code

    My typo .... the Autofill needs to be to your last line variable rather than Ir

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,876
    Post Thanks / Like
    Mentioned
    384 Post(s)
    Tagged
    41 Thread(s)

    Default Re: putting a vlookup into vba code

    Quote Originally Posted by Patriot2879 View Post
    this is great many thanks for your help
    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •