How to fix loop initializing only some of the time in Excel VBA?

Question

I have an excel VBA macro that transfers some data from one workbook to another. The workbook that the data is sent to is set up with 12 worksheets, each one representing a month. When the user initiates the macro, it determines which sheet to paste the data into from some of the data being transferred. I bring this up because the issue I am having is dependent on the sheet the data will be pasted to. Part of the macro is a do while loop that finds the next available row to paste the data to. This loop only activates on certain sheets. The loop is skipped in other sheets. (For example, the loop will activate if the data will be pasted into the December sheet but not the November sheet or the January Sheet.)

I have stepped through the macro two times. The first time I set the the data to be pasted to the december sheet and the loop worked correctly.

The second time I set the data to be pasted in the January sheet and the loop was skipped over.

With wbOut.Sheets(strSheet)
        .Activate
        nLastRowOut = .Range("A500").End(xlUp).Row + 1
        i = 220
        nLastRowOut = i
        Do While (i > 41)
              str = .Range("A" & i).Value & .Range("B" & i).Value & .Range("C" & i).Value & .Range("D" & i).Value & .Range("E" & i).Value & .Range("F" & i).Value & .Range("G" & i).Value & .Range("H" & i).Value & .Range("I" & i).Value & .Range("J" & i).Value & .Range("K" & i).Value & .Range("L" & i).Value & .Range("M" & i).Value
              If Replace(str, 0, "") <> "" Then
                    nLastRowOut = i + 1
                    GoTo copySections
              End If
              i = i - 1
        Loop

The next available row should be found and and then used to paste the data.

What is actually happening 75% of the time is that the loop is skipped over and the data is pasted in row 221 instead of the next available row.

What’s the value of “str” when you reach “If Replace(str, 0, "") <> "" Then” statement in the not working loop? — DisplayName
– DisplayName, Commented Dec 26, 2018 at 6:38
This is the value that shows when I reach that line in the code. Watch : : Str : "1858000148640200664000" : String * -1 : Module1.transformData — Mathieu Klein
– Mathieu Klein, Commented Dec 26, 2018 at 6:48
If str has the value “1858000148640200664000” then the result of “Replace(str, 0, "")” is “1858148642664”, hence the check “If Replace(str, 0, "") <> ""” returns True and there you follow up with “nLastRowOut = i + 1” and “GoTo copySections”. If that is not the wanted behavior then you have to change the ”If” condition — DisplayName
– DisplayName, Commented Dec 26, 2018 at 12:50
I found the issue. There was a formula in a cell in row 220 for the sheets that were not working. That's why the loop was not working and pasting the data in 221. Your comment helped me figure it out. Thanks a lot! — Mathieu Klein
– Mathieu Klein, Commented Dec 27, 2018 at 1:12

alowflyingpig · Accepted Answer · 2018-12-26 22:17:45Z

1

The code for the nLastRowOut doesn't look right..

Change this:

nLastRowOut = .Range("A500").End(xlUp).Row + 1

to this:

nLastRowOut = .Range("A" & Rows.Count).End(xlUp).Row + 1

answered Dec 26, 2018 at 22:17

alowflyingpig

7387 silver badges20 bronze badges

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

1 Comment

Mathieu Klein Over a year ago

You are right! I made the fix. Thanks a lot for pointing that out. I found out the issue I was having already but I will fix this as well. Thanks a lot!

Collectives™ on Stack Overflow

How to fix loop initializing only some of the time in Excel VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related