Can't write the Select Case properly - HELP Please!

[Michelle Deigh]

Below I have the code and pseudo code for the Case portion which is my problem - I am unsure how to word it properly - I have looked up and read hundreds of examples but keep seeing different formats so get more confused.

Basically I am doing the same thing over and over - but with new cols. To start off I have it clean and simple which as you see is done, but then I have to take the next set of numbers and if they have a negative total do one thing and if it is positive do another. After that I just update it with the same code referring to different columns.

I hope you can understand it as I really have no more description then that! Hopefully the code below will assist!


Sub PopCol()

Range("D3:D19").Formula = "=RC[-1]-RC[-2]" 'D=C-B , Delta is equal to Cap minus Dem
For i = 3 To 19
Range("D" & i) = CLng(Range("C" & i) - Range("B" & i))
If (Range("D" & i) < 0) Then
Range("E" & i) = Range("E" & i) - Range("D" & i)
Else
Range("F" & i) = Range("F" & i) + Range("D" & i)
End If
Next

Range("G3:G19").Formula = "=RC[-1]-RC[-2]" 'G=F-E , Delta is equal to Cap minus Dem
'if delta (h)= capacity (G) continue, if g<0 go back over prior months to where is negative and add to capacity
'then recalculate, else g>0 - go back over prior months and subtract(a neg) from demand (same as adding absolute value). then recalculate all.
For i = 3 To 19
Range("G" & i) = CLng(Range("F" & i) - Range("E" & i))
Select Case "May "
'Case "G" = Range ("F" & i) Continue
'Case "G" < "0" check D if >0,("D"&i)=("D"&i)-("G" & i) {then compare D with C, balance over equal is placed in G and D & C end up equal. goto beginning of sub}
'case "G" > "0" check D if <0,("C"&i)=("C"&i)-("G" & i) (then compare D with C, balance over equal is placed in G and D & C end up equal.goto beginning of sub}
End Select
'next is June......

 

[Michelle Deigh]

I have semi solved this but need assistance with how I can make the code return to the beginning every I complete one section of it. (It needs to recalculate the math in the cells now that I have changed the numbers). It will need to repeat this after each month/section of code? -- I have tried GoTo but apparently shouldn't/can't use it. Loop? and how do I do that?

 

[Norie]

The code/psuedo-code isn't clear, especially the Select Case part which is kind of the wrong way round.

What, in words, are you trying to do?

 

[Michelle Deigh]

I am no longer need assistance with the Select Case, but I can't figure out how to go back to the beginning and process all the calculations from start. I have to do this twelve times I have the part I originally thought would be best in a Select Case but did it with If..Then..ElseIf - but each I make these calculations for each month and then need to go back to the beginning eachtime. (I am calculating something that changes things forward and backward in months and so each time needs differences to redone.)

 

[Norie]

Can you post the current code and explain what it's supposed to do?

 

[Michelle Deigh]

This is for an Excel that has 3 columns for each month the first is for Demand, the second is for Capacity, and the third is for Delta/Difference. The delta needs to then be allocated both backwards and forwards to alleviate the balance. if it can't be added into the previous month it needs to be added to the next month then the who thing needs to be recalculated then the next month needs to be donw then the entire delta part needs to be calculated again...and so on...

The code is;

Sub T3_DeltaAll()
    For i = 3 To 19
        Range("D" & i) = CLng(Range("C" & i) - Range("B" & i))
        Next
    For i = 3 To 19
        Range("G" & i) = CLng(Range("F" & i) - Range("E" & i))
        Next
    For i = 3 To 19
        Range("J" & i) = CLng(Range("I" & i) - Range("H" & i))
        Next
    For i = 3 To 19
        Range("M" & i) = CLng(Range("L" & i) - Range("K" & i))
        Next
    For i = 3 To 19
        Range("P" & i) = CLng(Range("O" & i) - Range("N" & i))
        Next
    For i = 3 To 19
        Range("S" & i) = CLng(Range("R" & i) - Range("Q" & i))
        Next
    For i = 3 To 19
        Range("V" & i) = CLng(Range("U" & i) - Range("T" & i))
        Next
    For i = 3 To 19
        Range("Y" & i) = CLng(Range("X" & i) - Range("W" & i))
        Next
    For i = 3 To 19
        Range("AB" & i) = CLng(Range("AA" & i) - Range("Z" & i))
        Next
    For i = 3 To 19
        Range("AE" & i) = CLng(Range("AD" & i) - Range("AC" & i))
        Next
    For i = 3 To 19
        Range("AH" & i) = CLng(Range("AG" & i) - Range("AF" & i))
        Next
    For i = 3 To 19
        Range("AK" & i) = CLng(Range("AJ" & i) - Range("AI" & i))
        Next
       
    'take May Delta and check April to see if can add there to demand, if not place in June.
    For i = 3 To 19
        If Range("G" & i).Value < 0 And Range("D" & i).Value <= 0 Then
            Range("H" & i).Value = Range("H" & i).Value - Range("G" & i).Value 'negative so subtract for neg minus neg so positive
        Else: If Range("C" & i).Value >= Range("D" & i).Value Then Range("B" & i).Value = Range("B" & i).Value + Range("G" & i).Value ' ?
        End If
    Next i
    'take June Delta and check May to see if can add there to demand, if not place in July.
    For i = 3 To 19
        If Range("J" & i).Value < 0 And Range("G" & i).Value <= 0 Then
            Range("K" & i).Value = Range("K" & i).Value - Range("J" & i).Value 'negative so subtract for neg minus neg so positive
        Else: If Range("F" & i).Value > Range("G" & i).Value Then Range("E" & i).Value = Range("E" & i).Value + Range("J" & i).Value ' ?
        End If
    Next i
    
        
     '*take July Delta and check June to see if can add there to demand, if not place in August.
    For i = 3 To 19
        If Range("M" & i).Value < 0 And Range("J" & i).Value <= 0 Then
            Range("N" & i).Value = Range("N" & i).Value - Range("M" & i).Value 'negative so subtract for neg minus neg so positive
        Else: If Range("I" & i).Value > Range("J" & i).Value Then Range("H" & i).Value = Range("H" & i).Value + Range("M" & i).Value ' ?
        End If
    Next i
         '**take August Delta and check July to see if can add there to demand, if not place in September.
    For i = 3 To 19
        If Range("M" & i).Value < 0 And Range("J" & i).Value <= 0 Then
            Range("N" & i).Value = Range("N" & i).Value - Range("M" & i).Value 'negative so subtract for neg minus neg so positive
        Else: If Range("I" & i).Value > Range("J" & i).Value Then Range("H" & i).Value = Range("H" & i).Value + Range("M" & i).Value ' ?
        End If
    Next i
           
End Sub