"Please".VBA: going from If then to For next.

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
984
Office Version
  1. 2010
Platform
  1. Windows
Hello, everyone.
I am trying to check an entire dynamic array with some conditions.
I have a six months data on B2: G
the conditions are:
IF B5 < B4 and B3 < B5 then ( B3 - 4) else (B3 + 1)
and the same for the others columns, C,D,E,F and G

I did only the if statements code, what I am trying to figure out is
How to loop in order to go to the LastRow
and every time the code will go one cell down at the time

the sensitive part for me here is that the condition are NOT EQUAL on
every COLUMN, you will see what I mean with the code I am loading

Thanks for reading this post, I really understand how valuable is your time.
VBA Code:
Sub sat()
Dim x As Long, y As Long, c As Long
         c = Range("b3").Value: y = Range("b4").Value: x = Range("b5").Value
         Range("i2").ClearContents
                     If x < y And c < x Then
                     c = c - 4
                     Else
                     c = c + 1
         Range("i2").Value = c
         End If
End Sub
Sub mon()
         Dim k As Long, m As Long, d As Long
         d = Range("c3").Value: m = Range("c4").Value: k = Range("c5").Value
         Range("j2").ClearContents
                  If k < m And d < k Then
                  d = d - 6
                  Else
                  d = d + 3
                  Range("j2").Value = d
         End If
End Sub

Sub wed()
         Dim h As Long, z As Long, si As Long
         si = Range("d3").Value: z = Range("d4").Value: h = Range("d5").Value
         Range("k2").ClearContents
                  If h < z And si < h Then
                  si = si - 8
                  Else
                  si = si + 5
                  Range("k2").Value = si
End If
End Sub
Here you can see, on
Column B the condition is c= c - 4 and C + 1, but on Column
C change d = d - 6 and d = d + 3
column D condition is si = si - 8 and si = si +5
column E condition is ........["here I know you can handle different so as a reference, I am providing a sort of names for this part of the code, but you will named anyway you want, please"]
E variable = variable - 10 and variable = variable + 7
F variable = variable - 12 and variable = variable + 9
G variable = variable - 14 and variable = variable + 11

001MONTECA.PNG

Thanks again
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
VBA Code:
Sub OMEGA()
      Dim x As Long, y As Long, c As Long
               c = Range("b3").Value: y = Range("b4").Value: x = Range("b5").Value
               
                     If x < y And c < x Then
                              c = c - 4
                              Else
                              c = c + 1
                              Range("p2").Value = c
                     End If
      
      Dim k As Long, m As Long, d As Long
               d = Range("c3").Value: m = Range("c4").Value: k = Range("c5").Value
               
                     If k < m And d < k Then
                           d = d - 6
                           Else
                           d = d + 3
                           Range("q2").Value = d
                     End If
      
      Dim h As Long, z As Long, si As Long
               si = Range("d3").Value: z = Range("d4").Value: h = Range("d5").Value
               
                     If h < z And si < h Then
                           si = si - 8
                           Else
                           si = si + 5
                           Range("r2").Value = si
                     End If
      
      Dim AA As Long, AB As Long, AC As Long
            AC = Range("E3").Value: AB = Range("E4").Value: AA = Range("E5").Value
            
                     If AA < AB And AC < AA Then
                           AC = AC - 10
                           Else
                           AC = AC + 7
                           Range("S2").Value = AC
                     End If
      
      Dim AE As Long, AF As Long, AD As Long
            AD = Range("F3").Value: AF = Range("F4").Value: AE = Range("F5").Value
            
                  If AE < AF And AD < AE Then
                        AD = AD - 12
                        Else
                        AD = AD + 9
                        Range("T2").Value = AD
                  End If
      
      Dim AI As Long, AJ As Long, AK As Long
               AK = Range("G3").Value: AJ = Range("G4").Value: AI = Range("G5").Value
               
                  If AI < AJ And AK < AI Then
                        AK = AK - 14
                        Else
                        AK = AK + 11
                        Range("U2").Value = AK
                  End If
End Sub
Any opinion
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top