IfElse and use of variables?

Question

I need to loop trough 20 rows and add an equation to a worksheet that differs by variables. I'm using a If.... Else If statement, and I think I need to declare variable as I am only populating the second row of data.

My Code:

Do While counter < 20
        counter = counter + 1
    If CNum = 269 And SCode = 7168 Then
        NTotal = ("=(C2*35)/1000")
        Range("E2").Value = NTotal
    ElseIf CNum = 269 And SCode = 7181 Then
        NTotal = ("=(C2*2.5)/1000")
        Range("E2").Value = NTotal
    ElseIf CNum = 269 And SCode = 7183 Then
        NTotal = ("=(2*(D2-C2))/1000+50")
        Range("E2").Value = NTotal
    End If
Loop

Do I simply declare a range value for C2, D2, and E2 to make this work?

Joe

why don't you do this with a simple formula in your worksheet? (I'm just curious... if you don't need to do it in VBA and there's a simpler way, I think you can explore it) — Barranka, Oct 27 '14 at 19:42
It's part of a larger re-format that I'm doing on a worksheet that is a repetitive task. Looking to simplify the task. — Joseph Prespare, Oct 27 '14 at 19:56

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

It is working but two things:

Firstly because CNum and scode aren't changing within the loop they always have the same value so only the first matching condition will ever be met for each iteration

Secondly, if you want to increment rows then you need to include 'counter' to increment the rows element of your 'range' statements. An example could be as follows:

Do While counter < 20
        counter = counter + 1
    If CNum = 269 And scode = 7168 Then
        NTotal = ("=(C2*35)/1000")
        Cells(counter + 1, 5).Value = NTotal
    ElseIf CNum = 269 And scode = 7181 Then
        NTotal = ("=(C2*2.5)/1000")
        Cells(counter + 1, 5).Value = NTotal
    ElseIf CNum = 269 And scode = 7183 Then
        NTotal = ("=(2*(D2-C2))/1000+50")
        Cells(counter + 1, 5).Value = NTotal
    End If
Loop

Edit

You may also care to read this

edited May 23 '17 at 11:57

Community

1
1

answered Oct 27 '14 at 20:23

barryleajo

1,956
2
12
13

I'm confused. If I am looking at two values, how is it that they aren't changing within the loop? The Scode changes, the CNum stays the same. – Joseph Prespare Oct 28 '14 at 14:18
Your If ElseIf statements CHECK on the value of the variables, they don't actually change them. So if 'scode' is set at 5000 going into this loop, it is still 5000 when the loop exits. Did you run the code and did it work OK for you? – barryleajo Oct 28 '14 at 15:11
I did run the code but I get the first formula for each row independent of the Scode. – Joseph Prespare Oct 28 '14 at 15:16
You should only get one formula in col E, in each of the 20 rows depending on which condition is met. If no conditions are met then the cell in that particular row won't change. Try stepping through your code line by line and observe what goes on. To do this set a breakpoint on the first line of the loop code, run the code and when it breaks at your breakpoint, step through one line at a time using F8 key. – barryleajo Oct 28 '14 at 15:34
That's what I thought it would do, but when I step through it, it only finds the first condition, 20 times, and then ends. – Joseph Prespare Oct 28 '14 at 15:44
Yep its working correctly - as I said earlier nothing is happening inside the loop to change any conditions – barryleajo Oct 28 '14 at 17:01
Ok, I don't understand why it's not stepping down through each row, but I guess it's back to google to find an answer. Thanks for your help thus far. – Joseph Prespare Oct 28 '14 at 18:59

IfElse and use of variables?

1 Answers1