vba do until loop doesn't go in although condition is valid

Question

I won't bring the entire code, I'll try to show what is relevant. The following code (which is inside a loop, but it doesn't matter) calls a function (compareStrings) which returns an integer.

Sheet1.Range("S" & i).Value = compareStrings(sheet1.Range("J" & i).Value, sheet1.Range("K" & i).Value)

So basically I have a loop that fills column S with integers. I then sort S column in ascending order. later I have another loop, that is supposed to do something with all the values that are less than 5.

The loop looks like this:

With Sheet1.Range("S" & i)
    Do Until .Value < 5
        If .Value = 0 Then
            'some statement
        Else
            'some statement
        End If
        i = i + 1
    Loop

End With

For some reason it doesn't go in the loop although I have many rows with values that are < 5. I actually tried to change it to <> and it doesn't go in either. It is as if it doesn't see it as an integer, although I have put integers in these cells.

Any ideas?

Thanks

What does the value show up as? I think we will need to see more code. — SJR
– SJR, Commented Oct 8, 2018 at 15:42
Unfortunately there isn't much for us to help you with without seeing the raw data. I would suggest placing a Debug.Print .Value statement and watching the immediate window. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Oct 8, 2018 at 15:42
@SJR it shows numbers. If I manually calculate something with these numbers it works (for example =s1 +s2). I don't know what other code might be relevant. I show everything I do with this column.. — Hana
– Hana, Commented Oct 8, 2018 at 15:43
Are you sure you want < in Do Until .Value < 5 - i.e. if the value is less than 5, stop and don't do anything? Your logic doesn't seem to make sense, especially if the values are in ascending order. — BigBen
– BigBen, Commented Oct 8, 2018 at 15:44
Use F8 to step through the code line by line, checking the values of the variables as you go, and you'll figure out exactly where the issue lies. See Chip Pearson's Debugging VBA. — ashleedawg
– ashleedawg, Commented Oct 8, 2018 at 15:44

BigBen · Accepted Answer · 2018-10-08 15:49:48Z

1

"I have another loop that is supposed to do something with all the values that are less than 5."

Your logic doesn't make sense though.

Do Until .Value < 5 will not do anything with values less than 5. It's the same as saying "take action if my value is greater than or equal to 5.

Do While might be a better option.

answered Oct 8, 2018 at 15:49

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

vba do until loop doesn't go in although condition is valid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related