Variable remains empty in VBA Do while iteration

Question

I am having trouble with my macro not capturing the object/variable "book". It should pull the string from the cell, then test if it's equal to the string.

My code only captures the first string, and then becomes empty after the first iteration.

     Dim i%, j%
        i = 37
        j = 1
        Dim TRBfound As Boolean
        Do While TRBfound = False
            book = Cells(i, 1).Value2
            If Left(book, 3) = "TRB" Then
                TRBfound = True

            Else

                Sheets("Sheet2").Select
                Cells(i, 1).Select
                Selection.Copy
                Sheets("Sheet18").Select
                Cells(j, 1).Select

                ActiveSheet.Paste
                i = i + 1
                j = j + 1
            End If
        Loop

        End If

Data: beginning in A37, which is also merged with other cells next to it.

AOU          - AOU                              
DSU          - DSU                              
TRBK         - Treasury Book                                
LLE          - US Single Stock                              
SDC          - SDC                              
SZK          - AMRS DELTA 1 RIS                             
TRBK         - Treasury Book                                
TRBK         - Treasury Book                                
TRBK         - Treasury Book                                
TRBK         - Treasury Book                                
TRBK         - Treasury Book                                
TRBK         - Treasury Book

Variable remaining empty in VBA Do while interation

score 1 · Answer 1 · answered Feb 25 '20 at 22:23

Try to avoid using Select in your code.

This seems to work fine for me:

Dim i As Long
Dim j As Long
Dim TRBfound As Boolean

i = 37
j = 1
Do While TRBfound = False
    book = Sheets("Sheet1").Cells(i, 1).Value2
    If Left(book, 3) = "TRB" Then
        TRBfound = True
    Else
        Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i, 1).Value
        i = i + 1
        j = j + 1
    End If
Loop

Variable remains empty in VBA Do while iteration

1 Answers1