Solved: Vlookup in other workbooks fail

[AnswerSeeker]

Hello

I am trying to open from the main workbook several other workbooks and than read a certain cell value via vlookup.
The numbers of workbooks and are changing and also the the place and where they are saved and names might be changed in the future wherefore I used several variables



Application.Workbooks(MainWorkbook).Worksheets(1).Activate
For a = 1 To CountMonth Step 1
wholePath = myPath & MyMonthName(a) & " " & myYearFile & myFileName
Workbooks.Open Filename:=wholePath
Application.Workbooks(MainWorkbook).Worksheets(1).Activate
Range("E26").Offset(0, j).Value = Application.WorksheetFunction.VLookup(SearchCriteria, Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Sheets("Pivot").Range("A:E"), 5, False)
Workbooks(MyMonthName(a) & " " & myYearFile & myFileName).Close SaveChanges:=False
j = j + 2
Next a

The other workbook is working which means the variable wholePath is correct, thus variables myPath, MyMonthName, myYearFile an myFileName are correct.

does anyone knows my mistake?

[Bob Phillips]

What is in SearchCriteria?

[AnswerSeeker]

SearchCriteria = "Consultancy Fees Total"

[Paul_Hossler]

does anyone knows my mistake?


Are you getting an error msg, or just no data?

One thing I always do when having more that one WB open, is to be very rigorous about always specifying the objects

For ex, you just have Range("E26"), so that would refer to the active sheet, and I'd get confused or make a mistake; easier to let Excel keep it straight for me


Option Explicit
Sub test()
Dim wbMain As Workbook, wb2 As Workbook
Dim wsMain As Worksheet
Set wbMain = Workbooks(MainWorkbook)
Set wsMain = wbMain.Worksheets("Sheet1")
For a = 1 To CountMonth Step 1
wholePath = myPath & MyMonthName(a) & " " & myYearFile & myFileName

Workbooks.Open Filename:=wholePath
Set wb2 = ActiveWorkbook

wsMain.Range("E26").Offset(0, j).Value = Application.WorksheetFunction.VLookup(SearchCriteria, wb2.Sheets("Pivot").Range("A:E"), 5, False)

wb2.Close SaveChanges:=False

j = j + 2

Next a
End Sub



Paul

[Bob Phillips]

What is in SearchCriteria?

And you are sure that text exits in your lookup range, no extra spaces or anything?

[AnswerSeeker]

I checked again, and only in the first workbook were deviations from the searchcriteria wording.

Thanks for your help!