PDA

View Full Version : [SOLVED] VBA copy and paste Vlookup



Me00550
04-16-2019, 06:43 AM
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

Bob Phillips
04-16-2019, 07:01 AM
What are you trying to lookup, and from where?

Me00550
04-16-2019, 07:18 AM
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

p45cal
04-17-2019, 06:03 AM
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

Me00550
04-17-2019, 09:53 AM
thanx p45Cal worx just like i want..:yes

Me00550
04-23-2019, 08:56 AM
P45cal

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

p45cal
04-23-2019, 09:10 AM
it worked for awhile now im getting a run time error 1004 Application-defined or object defined errorOn which line?

Me00550
04-23-2019, 10:29 AM
i think i figured it out.

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

thanx for your help