Need to Test 2 Criteria before looping macro - how can I do it?

[macropadawan]

Hi folks,

I am trying to test 2 criteria sequentially before looping a macro. The 1st one works fine, but Im having some trouble getting the second test to work. Here is a snippet of the code (which works) prior to adding the second test:

Do While test <> True
k = k + Increment
ActiveSheet.Cells(56 + j, 17).Value = k
test = ActiveSheet.Cells(57 + j, 13).Value
Loop

I would like to add the following line of code prior to the loop:

test = ActiveSheet.Cells(57 + j, 37).Value

When I tried the following code it did not pick it up:

Do While test <> True
k = k + Increment
ActiveSheet.Cells(56 + j, 17).Value = k
test = ActiveSheet.Cells(57 + j, 13).Value
test = ActiveSheet.Cells(57 + j, 37).Value
Loop

What am I doing wrong here?

Thanks!

 

[JoeMo]

Don't know what you mean by "it did not pick it up", but there's no point in assigning test a value and then immediately assigning it another value. Only the latter assignment has any impact. Perhaps you want something like this:

Do While test <> True
        k = k + Increment
        ActiveSheet.Cells(56 + j, 17).Value = k
        If ActiveSheet.Cells(57 + j, 13).Value = True Or ActiveSheet.Cells(57 + j, 37).Value=True then test = true
        Loop

Change the red Or to And if you want to see if both conditions are met.

 

[macropadawan]

Thanks for the quick response. That is what I am trying to do. Unfortunately it didn't work. Here is a full loop, which I thought might be helpful.

Loop
For j = 1 To 29
Increment = Worksheets("Price & Energy Curve").Cells(8 + Start + j, 3).Value
l = Int((Worksheets("Capacity").Cells(57 + j, 12).Value) * (-1) / Increment)
k = WorksheetFunction.Max((l + 1) * Increment, 0)
Worksheets("Capacity").Activate
ActiveSheet.Cells(56 + j, 17).Value = k
test = ActiveSheet.Cells(57 + j, 13).Value
Do While test <> True
k = k + Increment
ActiveSheet.Cells(56 + j, 17).Value = k
If ActiveSheet.Cells(57 + j, 13).Value = True And ActiveSheet.Cells(57 + j, 37).Value = True Then test = True
Loop

Any suggestions?

 

[JoeMo]

"it didn't work" is not useful in diagnosing a problem with your code. What exactly happens when you run the code? If you get an error, what is the error message and what line is highlighted?

 

[macropadawan]

There is no error code. It has the same result as the original code. The macro stops looping once the original criteria is reached, so it basically ignores the second criteria. I am wondering whether I might need to edit the line of code above "Do While" so that that the test includes the second criteria, but I am not sure how to do it.

 

[JoeMo]

You have not told us exactly what you want your code to accomplish so I'm going to guess that you need to change this line:

If ActiveSheet.Cells(57 + j, 13).Value = True And ActiveSheet.Cells(57 + j, 37).Value = True Then test = True

To this:

If ActiveSheet.Cells(57 + j, 13).Value = True And ActiveSheet.Cells(57 + j, 37).Value = True Then
test = True
Else
test = False
End If

 

[macropadawan]

Hmmm. That did not seem to have any affect. The loop is still stopping once the 1st criteria is met. What I am trying to do is have the code loop until both criteria are met. Does the line above "Do While" need to be altered in any way since we are adding a second criteria to the test? Please let me know if there is any other information that I can provide you that would be helpful to solving the problem.

Thanks

 

[JoeMo]

Maybe you could explain in words exactly what you want your code to do, and post the entire code that's not working for you now.