Need integer value from string

[revcanon]

I am using this code in a very large vb project, but I isolated the problem area:

Sub Test()

    Dim CondCount1 As Integer
    Dim CondCount2 As Integer
    Dim vCondCount As String
    Dim AssignCount As Integer
    
    CondCount1 = 26
    CondCount2 = 30

    For i = 1 To Worksheets.Count - 1 Step 1

    With Range("C" & 5 + i)
        .NumberFormat = General
        .Value = "Condition " & i
    End With
    
    vCondCount = "CondCount" & i
    AssignCount = Val(vCondCount)
   
    With Range("D" & 5 + i)
        .NumberFormat = 0
        .Value = AssignCount
    End With
    Next i
    
End Sub

I am able to easily define CondCount1 early on, but when I try to do a loop later in the process to look through many different "CondCount"s I can not seem to get the code to find the value of the "CondCount & i".

In this case, I get "0" for each value of AssignCount as it passes through the loop even though the "vCondCount" is = "CondCount1" and using Val() function to find value of "vCondCount"

Any ideas how to make that "AssignCount" = value of "CondCount1" and then it loop to "CondCount2"....etc.?

 

[JoeMo]

You've defined vCondCount as a string so it has a zero value. Why not put your conditions in an array something like this:

Sub Test()
    Dim CondCount() As Integer
    ReDim CondCount(1 To 2)
    CondCount(1) = 26
    CondCount(2) = 30
    For i = 1 To Worksheets.Count - 1 Step 1
        With Range("C" & 5 + i)
            .NumberFormat = General
            .Value = CondCount(i)
        End With
        With Range("D" & 5 + i)
            .NumberFormat = 0
            .Value = CondCount(i)
        End With
    Next i
End Sub

 

[Scott Huish]

The Val of the string CondCount1 is 0 because it starts with text. The Val function is used to evaluate numbers that may be stored as text. It will only return a number if the string starts with a number.

For example, the string "1 dollar" would return 1, but "Dollars: 1" would return 0.

So what exactly do you mean by the value of "CondCount & i"??

 

[revcanon]

uh... is "because I don't actually know what I'm doing" a good answer to your question?

LOL.

That code is perfect for what I needed. Thank you, Joe, for helping me understand the issue and now I know more than I did an hour ago.

Have a great day/night... wherever you are.

 

[JoeMo]

uh... is "because I don't actually know what I'm doing" a good answer to your question?

LOL.

That code is perfect for what I needed. Thank you, Joe, for helping me understand the issue and now I know more than I did an hour ago.

Have a great day/night... wherever you are.

You are welcome - thanks for the reply.