Getting a loop to stop printing in VBA

Question

I'm quite new to VBA and very rusty from java and C++ years ago, so any help is appreciated. At an overview I am trying to make this program find a number and interpret the month into a sheet number on an external workbook, then return me a value based off of the first number. I have it doing this but I cannot get the loop to end at the first empty cell. It could be a problem with the range in lookFor or with the loop statement; I'm not sure. Any thoughts? Here is the code

Sub Go()

  Range("B6").Select
  Do Until IsEmpty(ActiveCell)

    Dim lookFor As Range
    Dim srchRange As Range
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim book2Name As String
    book2Name = "Cash_Office_Long_Short_Log_FYE18.xlsx"
    Dim book2NamePath As String
    book2NamePath = ThisWorkbook.Path & "\" & book2Name
    Dim refMonth As Integer
    refMonth = Month(Cells(6, 5)) + 1

    Debug.Print "refMonth="; refMonth

    Set book1 = ThisWorkbook

    If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
    Set book2 = Workbooks(book2Name)

    Set lookFor = book1.Sheets(1).Range("B6:B800")
    Set srchRange = book2.Sheets(refMonth).Range("A1:B800")

    lookFor.Offset(0, -1).Value = Application.VLookup(lookFor, srchRange, 2, False)

    ActiveCell.Offset(1, 0).Select

  Loop

End Sub

Function IsOpen(strWkbNm As String) As Boolean

  On Error Resume Next
  Dim wBook As Workbook
  Set wBook = Workbooks(strWkbNm)
  If wBook Is Nothing Then
    IsOpen = False
    Set wBook = Nothing
    On Error GoTo 0
  Else
    IsOpen = True
    Set wBook = Nothing
    On Error GoTo 0
  End If

End Function

Your issue is caused by using `ActiveCell`, which is potentially in the active sheet in `Cash_Office_Long_Short_Log_FYE18.xlsx`. But you also have problems with trying to do a `VLookup` with the search item being a range of 795 cells - that gives a type mismatch error. Are you actually just trying to search for the cell in your original sheet's B6, B7, B8 etc? — YowE3K, Jan 09 '17 at 19:06
The active could potentially be in that sheet but in the test instances I have run it is always in book1 or Thisworkbook. I am just searching for the value of the cells in column B from book1 in book2. — Rsheale, Jan 09 '17 at 19:26
So your code doesn't ever open the 2nd workbook? It is always open when you start? — YowE3K, Jan 09 '17 at 19:26
Yes as the 2nd workbook is a starting point for reasearch that the first book does. I was unsure of how to write the command to make it open the 2nd workbook, to be honest. — Rsheale, Jan 09 '17 at 19:29
If the 2nd workbook isn't open when your code is run, your code opens it, and the "active" sheet in that workbook becomes the `ActiveSheet` and the `ActiveCell` is then on that sheet - and no longer on the sheet where you were when you started running the macro. So `ActiveCell.Offset(1, 0).Select` is selecting a cell in the 2nd workbook. — YowE3K, Jan 09 '17 at 19:37
Yes, so the end user for these research sheets is supposed to in all instances have the 2nd workbook already open, but if they did not I wanted them to have to open it as it is necessary to properly review the information. I see and now significantly better understand what I was doing as opposed to what I wanted to do Thank you. — Rsheale, Jan 09 '17 at 20:02

score 0 · Answer 1 · edited May 23 '17 at 12:24

Your looping issue is caused by your use of ActiveCell. As soon as you open a new workbook, the active cell becomes a cell in that workbook, and that has caused your loop to become very confused.

Please refer to How to avoid using Select in Excel VBA macros for suggestions on how to avoid these issues.

In the meantime I have refactored your code to (hopefully) do what you were trying to do:

'Change name of sub - "Go" isn't valid
Sub RunMe()
    Dim controlRow As Long
    Dim lookFor As Range
    Dim srchRange As Range
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim book2Name As String
    Dim book2NamePath As String
    Dim refMonth As Integer

    Set book1 = ThisWorkbook
    With book1.Worksheets(1) ' I have assumed that the active sheet was worksheets(1)
        'Set row pointer to start at row 6
        controlRow = 6
        Do Until IsEmpty(.Cells(controlRow, "B"))

            book2Name = "Cash_Office_Long_Short_Log_FYE18.xlsx"
            book2NamePath = ThisWorkbook.Path & "\" & book2Name
            refMonth = Month(.Cells(6, 5)) + 1  'Should this be controlRow rather than 6?

            Debug.Print "refMonth="; refMonth

            If Not IsOpen(book2Name) Then Workbooks.Open book2NamePath
            Set book2 = Workbooks(book2Name)

            Set lookFor = .Cells(controlRow, "B") ' Assumed that you are searching for value on this row
            Set srchRange = book2.Worksheets(refMonth).Range("A1:B800")

            lookFor.Offset(0, -1).Value = Application.VLookup(lookFor.Value, srchRange, 2, False)

            'Point to next row
            controlRow = controlRow + 1
        Loop
    End With

End Sub

Function IsOpen(strWkbNm As String) As Boolean
    On Error Resume Next
    Dim wBook As Workbook
    Set wBook = Workbooks(strWkbNm)
    IsOpen = Not (wBook Is Nothing)
    Set wBook = Nothing
    On Error GoTo 0
End Function

Getting a loop to stop printing in VBA

1 Answers1