Consulting

Results 1 to 8 of 8

Thread: VBA copy and paste Vlookup

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    21
    Location

    VBA copy and paste Vlookup

    my first part of my code works to transfer data from FA USE to LOG but i am trying to get the QATRangeCopy to look up SN# from worksheet QAT USE and then copy an paste new data to the log based on the SN#

    i have tried several different Vlookups but cant get it to work keep getting errors or nothing happens

    attached is my workbook
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    What are you trying to lookup, and from where?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Posts
    21
    Location
    assuming the Final USe already entered data(this code works) i basically want when someone enters data into QAT USE they enter the SN# , and number of tags found. i want my code to look up the SN# entered and then reference the LOG to copy and past the Date, Time and Tags to the LOG from QAT USE SHEET corresponding to the SN# they entered

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the attached is a button in the QAT USE sheet near cell C21 labelled Update Log Sheet.
    The code it calls is in that sheet's code-module. If there is more than one instance of the serial number on the Log sheet it finds the last (bottom-most) one.
    Sub blah()
    Set x = [Table1[Serial Number]]
    Set y = x.Find(what:=Range("C10").Value, after:=x.Cells(1), LookIn:=xlFormulas, lookat:=xlWhole, searchdirection:=xlPrevious, searchformat:=True)
    If y Is Nothing Then
      MsgBox "Serial no. " & vbLf & Range("C10").Value & vbLf & " not found on the Log sheet"
    Else
      If Application.WorksheetFunction.CountBlank(y.Offset(, 4).Resize(, 3)) < 3 Then
        MsgBox "There is already data on the Log sheet for that serial number" & vbLf & "Going there now…"
        Application.Goto y.Offset(, 4).Resize(, 3)
      Else
        y.Offset(, 4).Resize(, 3).Value = Array(Range("C6").Value, Range("C8").Value, Range("C14").Value)
        MsgBox "Log sheet updated"
      End If
    End If
    End Sub
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Posts
    21
    Location
    thanx p45Cal worx just like i want..

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Posts
    21
    Location
    P45cal

    it worked for awhile now im getting a run time error 1004 Application-defined or object defined error

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Me00550 View Post
    it worked for awhile now im getting a run time error 1004 Application-defined or object defined error
    On which line?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular
    Joined
    Nov 2018
    Posts
    21
    Location
    i think i figured it out.

    i forgot add in the code to un-protect the sheet

    thanx for your help

Posting Permissions

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