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

cybernetic.nomad · Accepted Answer · 2020-02-25 22:23:30Z

1

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

answered Feb 25, 2020 at 22:23

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variable remains empty in VBA Do while iteration

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related