VBA VLOOKUP and Copy and Paste Cell Value

[gclose7]

Hi, I currently run a report every morning that manipulates exported raw data to show the information needed. The majority of this report and the code was built by someone else a long time ago, but I have added to this to make it quicker and better...

However, I am having problems with a VLOOKUP that can cause me to waste quite a bit of my time copying and pasting from the previous Worksheet every morning! I would really appreciate some help with this:

Column A holds the identity of each row using a number (eg 1-123456789). Column K holds the 'Comments' relating to the particular data of this row. What the VLOOKUP does is it uses the row ID number 1-123456789 in my ActiveSheet, and searches to see if it is located in the PreviousSheet. If it finds it, it then copies the cell in column K of that row only from PreviousSheet and pastes it into the cell in column K on the row in ActiveSheet that has the same row ID number. (Really not sure if I've explained it clearly here, see images and code below).

I have two problems:

1. The ID 1-123456789 is hardly ever located on row 5 for example, it can change everyday. When this happens the column K value returns #N/A, meaning I have to use the ID no. to manually find locate, copy and paste from the previous worksheet.

2. Even if the VLOOKUP is successful, it doesn't paste the value of the cell, only the formula. So I have to manually copy and paste every single cell in column K to be able to add to the 'Comments'.

Code below for VLOOKUP:

'Select Cell K3 ready for comments Macro
    
    ActiveSheet.Cells(3, 11).Select

    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-10],'" & ActiveSheet.Previous.Name & "'!RC[-10]:R[97]C,11,FALSE)"
    Range("K4").Select

I got this code from forums quite a while back.

Again, any help is much appreciated.

 

[ShawnPCooke]

If I'm reading this correctly, what you're doing is selecting a cell where you want the comments to go in your active sheet. You run the macro, and you want it to look at the previous sheet, pull the value from column K in that sheet that has a matching ID, and output it to your selection. Then you want the selection to move down one to get you ready for the next one. Is that correct?

Unless you expect the data in the previous sheet to change, there's no reason to do this by means of a VLOOKUP formula. Instead, you can just find the value and move it over.

Public Sub PullComments()

'Declarations
Dim varID as Variant
Dim wsCurrent as Worksheet
Dim wsPrevious as Worksheet
Dim rngSelection as Range

'Initialization
Set wsCurrent = ActiveSheet
Set wsPrevious = wsCurrent.Previous
Set rngSelection = ActiveCell

'Error checking--do nothing if not in the correct column
If Not rngSelection.Column = 11 Then

    MsgBox "Please select a cell in column K.", vbCritical + vbOKOnly, "Error"
    Exit Sub

End If

'Search to see if you can find the ID on the previous page
Set varID = wsPrevious.Find(What:=wsCurrent.Cells(rngSelection.Row,1).Value)

'If it didn't find it, the results will be Nothing.
If varID Is Nothing Then

    rngSelection.Value = " - Not Found - "

Else

    'Return the value in the appropriate row and column from the previous sheet
    rngSelection.Value = wsPrevious.Cells(varID.Row, 11).Value

End If

'Regardless, move to the next cell
wsCurrent.Cells(rngSelection.Row +1, rngSelection.Column).Select

End Sub

 

[gclose7]

Thanks for the reply.

Yes that's correct, I've implemented your code and ran the report this morning, but there is a 'Compile error' appearing with the message:

'Method or data member not found' and it highlights the .Find part of the code below.

'Search to see if you can find the ID on the previous page
Set varID = wsPrevious.Find(What:=wsCurrent.Cells(rngSelection.Row, 1).Value)

After clicking on the help button it takes me to this page below:
Method or data member not found (Error 461) , but with me being a novice with VBA I don't really know how to fix the problem...

Please can you help again?

 

[ShawnPCooke]

Sorry, that was my error. Replace the offending line with this:

'Search to see if you can find the ID on the previous page
Set varID = wsPrevious.Columns(1).Find(What:=wsCurrent.Cells(rngSelection.Row, 1).Value)

VBA lesson for the day: "Method or data member not found" means that I'm trying to use a function or a property on an object which that object can't do. Previously, I had applied the "Find" method to a worksheet. That doesn't work. You can only apply "Find" to a Range object. So I added Columns(1), which is a property of a worksheet that returns a range--in this case, the first Column, which is where your ID value lives.

 

[gclose7]

I had to enclose your code in a loop for the number of rows I wanted it to perform the retrieval for, but it worked brilliantly... Thank you so much, couldn't have done it without you!

 

[ShawnPCooke]

Glad to help!