1

I have been working on a small macro but have run into an error. The function of the macro is as follows: There is a list of stocks in an existing sheet. The macro goes into the folders and opens a spreadsheet where the recommendations are stored. It then goes back to the original sheet, takes each stock code and then goes into the recommendations sheet to see if there is a corresponding stock and what its recommendation is.

The code works fine, however I am now getting a VBA run-time error 438 when I am trying to get the macro to switch which workbook it needs to work on.

The error occurs on the lines application.wb2.activate and then lower down again with application.wb2.activate and application.wb.activate

When I replace wb and wb2 with the full directory, i.e. H:\A\AA\recommendations.xlsx and H:\A\AA\november 2017.xlsm, it works fine.

Would appreciate any help here! Thanks!

Option Explicit
Option Compare Text

Sub gsr()

Dim firstrow As Integer, lastrow As Integer, i As Integer
Dim gsr As Range, msr As Range
Dim stock, findstock As Range, col As Integer
Dim sPath As String, sFile As String
Dim sPath2 As String, sFile2 As String
Dim wb As Workbook, wb2 As Workbook
Dim xlrange As Range, xlcell As Range, xlsheet As Worksheet
Dim xllastrow As Integer
Dim foundlocationG As String, foundlocationM As String

With ActiveWorkbook
    sPath2 = ActiveWorkbook.Path & "\"
    sFile2 = sPath2 & ActiveWorkbook.Name
    Set wb2 = ActiveWorkbook
End With

    sPath = "H:\A\AA\"
    sFile = sPath & "Recommendations.xlsx"
    Set wb = Workbooks.Open(sFile)
    Set xlsheet = Sheets("Sheet1")
    xllastrow = xlsheet.Range("A1").End(xlDown).Row
    Set xlrange = xlsheet.Range("A1:A" & xllastrow)

Application.wb2.Activate
    With wb2.Sheets("Sheet1").Range("A:Z")
    Set stock = .Find(what:="Stock", After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set gsr = .Find(what:="GS", After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set msr = .Find(what:="MS", After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    firstrow = stock.Row + 1
    lastrow = .Cells(.Rows.Count, stock.Column).End(xlUp).Row
    lastrow = lastrow - 1
    col = stock.Column

    For i = firstrow To lastrow
        For Each xlcell In xlrange
            If xlcell.Value = Cells(i, col) Then
                Application.wb.Activate
                    foundlocationG = Cells(xlcell.Row, 2)
                    foundlocationM = Cells(xlcell.Row, 3)
                Application.wb2.Activate
                    Cells(i, gsr.Column) = foundlocationG
                    Cells(i, msr.Column) = foundlocationM
            End If
        Next xlcell
    Next i
    End With

End Sub
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Manu221
  • 41
  • 10

1 Answers1

1

You seem to be confusing the Workbook.Activate and Application.Activate¹ methods.

Activate is a direct method of the Workbook object. If you have correctly assigned (e.g. Set) an object-level variable to the Workbook object, you should be able to call the Activate method directly.

Solution: drop the Application and simply Activate the open workbook from the assigned object variable.

wb2.Activate
...
wb.Activate

For all intents and purposes, activating the workbooks as you have done is not necessary and is not the more efficient code. See How to avoid using Select in Excel VBA for more information.

¹ Application.Activate is more commonly used within Word VBA projects.