Excel 2010 Conditional VLOOKUP using 2 x Workbooks

[PWD]

Good afternoon, I thought I would post all of my code for this procedure as it might make my question/task more understandable. I would like to know if there is a "better" way of doing what I have done as this seems a little slow and it only has some 1,100 rows to populate.

Basically I have to populate each row in a provided workbook (WB1) with values from a second workbook (WB2) based on the values in a third workbook (WB3). My solution is to identify the row number in WB3 (matching Column "A" in WB1) and then create a formula testing that row's Column "U" and a VLOOKUP to WB2 if the test is true.

Sub MyOrEO()
Dim Verbfile As String, VerbSheet As String, VerbfileO As String, VerbSheetO As String, MyPath As String, AWB As String
Dim MyRow As Integer, x As Integer

AWB = ActiveWorkbook.Name

MyPath = "G:\DP\LbL\"

Verbfile = "Combined Verbatims V2.xlsx" 'Alternative verbs workbook
VerbSheet = "q23_other_spec"
VerbfileO = "ukc12400397_0 - FINAL DOWNLOAD for Des2.xlsx" 'Original verbs workbook
VerbSheetO = "Verbatim to be cleaned"

If CheckFileIsOpen(VerbfileO) = False Then
Workbooks.Open MyPath & VerbfileO
End If

If CheckFileIsOpen(Verbfile) = False Then
Workbooks.Open MyPath & Verbfile
End If

Workbooks(AWB).Activate

'use cells.rows.count rather than a hard row count value
EndRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

For x = 2 To EndRow

Range("EO" & x).FormulaR1C1 = "= IFERROR(MATCH(RC1,'[" & VerbfileO & "]" & VerbSheetO & "'!C1,""0""),"""")"

If Range("EO" & x).Value <> "" And Range("EO" & x).Value <> "0" Then
  MyRow = Range("EO" & x).Value
  
    Range("EO" & x).FormulaR1C1 = _
        "=IF(OR('[" & VerbfileO & "]" & VerbSheetO & _
        "'!R" & MyRow & "C21 = ""sat_1"" ,'[" & VerbfileO & "]" & VerbSheetO & _
        "'!R" & MyRow & "C21 = ""sat_2"" ,'[" & VerbfileO & "]" & VerbSheetO & _
        "'!R" & MyRow & "C21 = ""sat_3"" ,'[" & VerbfileO & "]" & VerbSheetO & _
        "'!R" & MyRow & "C21 = ""sat_4""),IFERROR(VLOOKUP(RC1,'[" & Verbfile & "]" & VerbSheet & "'!C2:C7,6,0),""""),"""")"

End If 'If Range("EO" & x).Value <> "" Then

Next x

    Range(("EO2:EO" & EndRow)).Copy
    Range("EO2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
End Sub

Many thanks,
D€$

 

[SkipVought]

hi,

Having read your question only, and assuming that the data in the remote workbooks is in tabular structure, accessible by MS Query, I would query both workbooks and avoid some or all VBA code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and even if you really wanted to use a lookup formula, maybe I'd OPEN the workbooks via code, and then use INDEX & MATCH, rather than VLOOKUP, which is a much more flexible method, MANUALLY, ONE TIME, rather than via code, which seems to be unnecessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, this is part of a larger project where I have to insert various verbatim comments from one workbook to another based on customer reference numbers - Column "A". I did think of INDEX & MATCH but wasn't sure whether I'd be wasting my time or not.

I'm probably thick but don't understand "MANUALLY, ONE TIME" as I have (potentially) 1,100 rows of verbatims comments to insert.

Many thanks,
D€$

 

[SkipVought]

So you have a 'master' workbook/sheet, that you need to populate with data from 2 other workbooks.

If you make the 'master' table on this sheet a Structured Table, then the "1,100 rows of verbatims comments to insert" (or 1,100,000 rows, it is irrelevant) your lookup formula is a ONE TIME THING.

What am I missing?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I've just been investigating Structured Tables as I've never used this before. I'm not sure (as yet) how that would help me, however. Creating a table appears to affect the formatting.

I guess I just don't understand your answer.

In WB1 I have data in rows 4 to 1,100, each with a customer reference number in Column "A". So for cell "EO4" I have to locate the row in WB3 that matches the customer reference number and test if cell "U"n is 1 to 4; if so I can VLOOKUP on WB2 to get the verbatim. None of the customer reference numbers are on the same rows within the workbooks. Then do the same for EO5, etc.

TBH I've just run everything again and it took about 2.5 seconds - which seems about 3x faster than earlier.

Many thanks,
D€$

 

[SkipVought]

None of the customer reference numbers are on the same rows within the workbooks.

Is that in dispute or relevant to the discussion?

All I am stateing is that I believe that there is no need for VBA, except for opening these workbooks with one click, as long as the formula is in a Structured Table, that will propagate to every row of data in the table..

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Sorry Skip, I just don't understand.

Many thanks,
D€$