Looping VBA Code

[Spyhunter]

I have a piece of code for 31 days but I want to loop this maybe using a counter 1 to 31 rather than typing the same piece of code 31 types

If [Day1] = "H" Then
    TotalHolCalc = CDbl(TotalHolCalc + 1)
End If
If [Day1] = "H½" Then
    TotalHolCalc = CDbl(TotalHolCalc + 0.5)
End If
If [Day1] = "S" Then
    TotalSickCalc = CDbl(TotalSickCalc + 1)
End If
If [Day1] = "S½" Then
    TotalSickCalc = CDbl(TotalSickCalc + 0.5)
End If
If [Day1] = "EL" Then
    TotalELCalc = CDbl(TotalELCalc + 1)
End If
If [Day1] = "EL½" Then
    TotalELCalc = CDbl(TotalELCalc + 0.5)
End If

Can anyone help?

Day1 would be Day1 to Day31 fields.

 

[hbrems]

I'll give you a start and let us know if you need more information.

Dim d as string ' Day

for i = 1 to 31
     d = "Day" & CStr(i)
next i

 

[Spyhunter]

Super. Do I still have to loop with this, as I believe this would be the correct way of thinking but it doesn't work - Am I missing the obvious somewhere?

Dim d As String
For i = 1 To 31
     d = "[" & "Day" & CStr(i) & "]"
If d = "H" Then
    TotalHolCalc = CDbl(TotalHolCalc + 1)
End If
If d = "H½" Then
    TotalHolCalc = CDbl(TotalHolCalc + 0.5)
End If
If d = "S" Then
    TotalSickCalc = CDbl(TotalSickCalc + 1)
End If
If d = "S½" Then
    TotalSickCalc = CDbl(TotalSickCalc + 0.5)
End If
If d = "EL" Then
    TotalELCalc = CDbl(TotalELCalc + 1)
End If
If d = "EL½" Then
    TotalELCalc = CDbl(TotalELCalc + 0.5)
End If
Next i

 

[hbrems]

My loop results in "Day1", "Day2", "Day3", ..., "Day31". Currently you are comparing these string values with other string values "H½", "S", "S½", ... So the result will always be false. For example: "Day1" does not equal "S".

Where are the actual "H½", "S", "S½", ... values stored?

 

[Spyhunter]

Day1 is a field in a record and the user selections on a previous form would select any of the H, S, etc etc so Day1, Day2, etc would equate to either null or a user selection (as listed).

 

[Spyhunter]

any suggestions? The theory should allow it to be working.

to add, the code works if i place Day1 as the 'd', Same for Day 2, etc etc

 

[hbrems]

So the different fields are represented as what on the form? Textboxes? Haha, I'm having trouble visualizing what you're doing...

Here's at least something to simplify your load of if statements. Try a Select Case statement instead, less typing work.

Dim d As String
Dim i As Integer

For i = 1 To 31

     d = "[" & "Day" & CStr(i) & "]"

     Select Case d
          Case "H"
               TotalHolCalc = CDbl(TotalHolCalc + 1)
          Case "H½"
               TotalHolCalc = CDbl(TotalHolCalc + 0.5)
          Case "S"
               TotalSickCalc = CDbl(TotalSickCalc + 1)
          Case "S½"
               TotalSickCalc = CDbl(TotalSickCalc + 0.5)
          Case "EL"
               TotalELCalc = CDbl(TotalELCalc + 1)
          Case "EL½"
               TotalELCalc = CDbl(TotalELCalc + 0.5)
     End Select

Next i

 

[pbaldy]

Perhaps

Select Case Me(d)

 

[Spyhunter]

thanks guys. that works using the me(d) parameter.

Woohoo!! Saved me months (alright days) of work

Much appreciated.

 

[pbaldy]

I was happy to contribute a small piece of the puzzle. You guys had already done all the heavy lifting.

 

[hbrems]

Nice to see that you were able to do what you wanted! xD

 

[Spyhunter]

many thanks all for your help. i now have a better understanding of loop and case