redefining an integer vba

Question

I have difficulty fixing the following problem I am facing:

Lets say I have this code

    i_GroupNumberA = Application.WorksheetFunction.CountIf(Sheets("SheetX").Range("G2:G500"), "Red")
    i_GroupNumberB = Application.WorksheetFunction.CountIf(Sheets("SheetX").Range("G2:G500"), "Green")

    For i = 2 To LastRow
        For j = 2 To LastCol

            groupNumber = Sheets("Sheet1").Cells(i, j).Value

            i_GroupNumberA = 35 'this number a integer that I have got from a cells.value
            i_GroupNumberB = 39

            If groupNumber = Sheets("Sheet1").Cells(i, j).Value Then

                  i_Variable = "i_" + groupNumber + "AlphabeticLetter"

                  MsgBox i_Variable

            End If

        Next j
    Next i

As a result I get i_Variable as a string in a messagebox as an outcome:

i_groupNumberA

I want to have the following result:

35

What I am asking is how can I make get a new variable of a string functioning as a integer.

I am not sure if I am asking this right?

I did as @engineersmnky said, but no effect. I check sites and made some adjustments together with your code. But Still I can't get the needed number in return. So far I got this:

Dim Group As New Collection 


i_GroupNumberA = Application.WorksheetFunction.CountIf(Sheets("SheetX").Range("G2:G500"), "Red") 'lets assume it is an number 35
    i_GroupNumberB = Application.WorksheetFunction.CountIf(Sheets("SheetX").Range("G2:G500"), "Green") 'lets assume it is an number 39

Group.Add i_GroupNumberA
Group.Add i_GroupNumberB

For i = 2 To LastRow
    For j = 2 To LastCol
         groupNumber = Sheets("Sheet1").Cells(i, j).Value
         i_Variable = "i_" + groupNumber + "AlphabeticLetter"
         Group(i_Variable)
    Next j
Next i

Group(i_variable) I cant figure it out? for some reason it is not working.

Its unclear what you are trying to achieve and your post does not make sense... how can you get i_groupNumberA from i_Variable = "i_" + groupNumber + "AlphabeticLetter"? You can't... — Cool Blue, Jul 29 '14 at 04:41

engineersmnky · Accepted Answer · 2014-07-29T13:31:19.840

Since you cannot get a variable value from a string in vba I would suggest creating a Collection Object or a Dictionary if you need something more diverse

dim groups AS New Collection
           'Value, Key
groups.Add 35, "i_GroupNumberA"
groups.Add 39, "i_GroupNumberB"

Then in your loop instead of the message box you can use

groups(i_Variable)

This will return 35 for "i_GroupNumberA"

Update Collections use an Item and Key structure for Collection(Key) to return the value you want you must specify the key

You may have to build a collection dynamically with your needed group numbers as I am unsure what these are e.g.

Function buildCollection(val As Variant,alpha AS String) AS Collection
   Dim groups As New Collection
   For i = 2 To LastRow
     For j = 2 To LastCol
       groupNumber = Sheets("Sheet1").Cells(i, j).Value
       i_Variable = "i_" & CStr(groupNumber) & alpha 
       groups.Add val, i_Variable
     Next j
   Next i
   set buildCollection = groups
End

But without more info about your structure I can't really help beyond basic examples. What is group number? Is it an actual integer? What is A vs B? Please advise as this may be simpler than I originally thought. If you put some sample data or something I am sure I could get this working for you seems like a fairly simple loop. Although you are looping through all the cells and stating that they are all group numbers which seems confusing to me.

@Tarik It is not working because of how you set up the collection incorrectly. A Collection is a set of Items and Keys. Items are retrieved by keys so you will need to specify a key or you cannot retrieve the item. — engineersmnky, Jul 29 '14 at 12:53

redefining an integer vba

1 Answers1