VBA - Array - Calling Variables from parts of the Array Value

Experts,

Here is a snippet of my code
Dim Cell, RNG As Range
Dim rw As Long
Dim ColumnString, ColLetter, TargLetter As Variant
Dim TargD, TargF, TargH, TargI, TargP, TargQ, TargS As Variant
Dim strTargC, strTargD, strTargF, strTargH, strTargI, strTargP, strTargQ, strTargS As String 
Dim response As VbMsgBoxResult

ColLetter = Array("D", "F", "H", "I", "P", "Q", "S")
    For rw = 0 To UBound(ColLetter)
        trag(ColLetter) = 0
        Set RNG = Cells(2, ColLetter(rw))
        Set RNG = Range(RNG, Cells(Rows.Count, ColLetter(rw)).End(xlUp))
            For Each Cell In RNG
            If Cell < 1 Then
                trag(ColLetter) = trag(ColLetter) + 1
            End If
        Next Cell
      Next

Open in new window


I am trying to set Trag(something) as the variable, go through the loop and add to Trag(something), then go to the next value in the array.

My variables are TragD, TragF, etc. When I tired
Trag(ColLetter)
' it was changed to
trag(ColLetter)
'inside the VBE

Open in new window


Also, is there a shorter way of writing the DIM statement when I have multiple variables that are similar, such as:

Dim TargD, TargF, TargH, TargI, TargP, TargQ, TargS As Variant
'like:
Dim Targ("D","F","H") As Variant

Open in new window


Or do I have to type each one out?

Thank you,
Kevin
LVL 23
redrumkevAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
>Also, is there a shorter way of writing the DIM statement when I have multiple variables that are similar

No. And I never put more than one variable definition on a single line. I like to be very explicit - lends to better documentation.

Other than that, what is your question?

Kevin
0
redrumkevAuthor Commented:
Kevin,

In this:

ColLetter = Array("D", "F", "H", "I", "P", "Q", "S")
    For rw = 0 To UBound(ColLetter)
        Targ(ColLetter) = 0
        Set RNG = Cells(2, ColLetter(rw))
        Set RNG = Range(RNG, Cells(Rows.Count, ColLetter(rw)).End(xlUp))
            For Each Cell In RNG
            If Cell < 1 Then
                Targ(ColLetter) = Targ(ColLetter) + 1
            End If
        Next Cell
      Next

So that each time the array goes through the variable is changed to TargD, TargF, ... TargS, ie, build that variable from the part Targ and the array value?
0
zorvek (Kevin Jones)ConsultantCommented:
One way to do it is to create a two-dimensional array.

Another is to create an array of arrays.

Dim Targ(1 to 7) As Variant
Dim Index As Long
For Index = LBound(Targ) To UBound(Targ)
    Targ(Index) = Array()
Next Index

Kevin
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

zorvek (Kevin Jones)ConsultantCommented:
To translate the column letters to index numbers you can use a collection:

Dim Map As Collection
Dim Key As Variant
Dim Index As Long
Set Map = New Collection
Index = 1
For Each Key In Array("D", "F", "H", "I", "P", "Q", "S")
   Map.Add Index, Key
   Index = Index + 1
Next Key

Then:

Targ(Map(ColumnLetter))(Index) = Value

Kevin
0
redrumkevAuthor Commented:
Kevin,

I tried adding your code, this is what I have:

Sub FIX_INPUT_ERRORS()

Dim Cell As Range
Dim RNG As Range
Dim rw As Long
Dim ColumnString As Variant
Dim ColumnLetter As Variant
Dim TargetLetter As Variant
Dim TargetC As Variant
Dim TargetD As Variant
Dim TargetF As Variant
Dim TargetH As Variant
Dim TargetI As Variant
Dim TargetP As Variant
Dim TargetQ As Variant
Dim TargetS As Variant

Dim Map As Collection
Dim Key As Variant
Dim Index As Long
Set Map = New Collection
Index = 1
For Each Key In Array("D", "F", "H", "I", "P", "Q", "S")
   Map.Add Index, Key
   Index = Index + 1
Next Key

'Then:
target(Map(ColumnLetter))(Index) = Value
      
      target(ColumnLetter) = 0
      Set RNG = Cells(2, ColumnLetter(rw))
      Set RNG = Range(RNG, Cells(Rows.Count, ColumnLetter(rw)).End(xlUp))
          For Each Cell In RNG
          If Cell < 1 Then
              target(ColumnLetter) = target(ColumnLetter) + 1
          End If
      Next Cell
    Next
    
End Sub

Open in new window


If I understand correctly, your code has loaded into Index:
D, F, H, I, P, Q and S

Now I want to set Target(index value) to TargetD = 0 (to count the number of errors I find)
Then I want to loop through D2:D(last row)
Then I want to go to Target (next index value) which would be F
TargetF = 0
Then look through F2:F(last row).

I get an error that value is undefined, how should it be defined?

Sorry - I am just very confused on getting the logic above to work.
0
zorvek (Kevin Jones)ConsultantCommented:
Do you need the errors by column? Or do you just want the total number of errors?

Kevin
0
zorvek (Kevin Jones)ConsultantCommented:
Produces an array of 7 elements, one for each column, that contains a count of errors for those columns:

Sub FIX_INPUT_ERRORS()

    Dim Cell As Range
    Dim SourceRange As Range
    Dim Map As Collection
    Dim ColumnLetters As Variant
    Dim ColumnLetter As Variant
    Dim Index As Long
    Dim Errors(1 To 7) As Variant
   
    ColumnLetters = Array("D", "F", "H", "I", "P", "Q", "S")
   
    Set Map = New Collection
    Index = 1
    For Each ColumnLetter In ColumnLetters
       Map.Add Index, ColumnLetter
       Index = Index + 1
    Next ColumnLetter
   
    For Each ColumnLetter In ColumnLetters
        With ThisWorkbook.Sheets("Sheet1")
            Set SourceRange = Range(Cells(2, ColumnLetter), Cells(.Rows.Count, ColumnLetter).End(xlUp))
            For Each Cell In SourceRange
                If Cell.Value < 1 Then
                    Errors(Map(ColumnLetter)) = Errors(Map(ColumnLetter)) + 1
                End If
            Next Cell
        End With
    Next ColumnLetter
   
End Sub

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redrumkevAuthor Commented:
Kevin,

The stepped through perfectly.

If column D had 3 errors, where/how is this count stored so I can use this in a string, such as:
"THERE ARE " & (column D count variable here) & "CELLS WITHOUT PREMIUM VALUES"

Open in new window


To display THERE ARE 3 CELLS WITHOUT PREMIUM VALUES.


0
zorvek (Kevin Jones)ConsultantCommented:
Kevins are so high maintenance!

Sub FIX_INPUT_ERRORS()

    Dim Cell As Range
    Dim SourceRange As Range
    Dim Map As Collection
    Dim ColumnLetters As Variant
    Dim ColumnLetter As Variant
    Dim Index As Long
    Dim Errors(1 To 7) As Variant
   
    ColumnLetters = Array("D", "F", "H", "I", "P", "Q", "S")
   
    Set Map = New Collection
    Index = 1
    For Each ColumnLetter In ColumnLetters
       Map.Add Index, ColumnLetter
       Index = Index + 1
    Next ColumnLetter
   
    For Each ColumnLetter In ColumnLetters
        With ThisWorkbook.Sheets("Sheet1")
            Set SourceRange = Range(Cells(2, ColumnLetter), Cells(.Rows.Count, ColumnLetter).End(xlUp))
            For Each Cell In SourceRange
                If Cell.Value < 1 Then
                    Errors(Map(ColumnLetter)) = Errors(Map(ColumnLetter)) + 1
                End If
            Next Cell
        End With
    Next ColumnLetter
   
    For Each ColumnLetter In ColumnLetters
        If Errors(Map(ColumnLetter)) > 0 Then
            Debug.Print "There are " & Errors(Map(ColumnLetter)) & " cells without premium values in column " & ColumnLetter
        End If
    Next ColumnLetter
   
End Sub

Kevin
0
redrumkevAuthor Commented:
In the above string, I was using the TargA, for example... below is the sort of code I was trying to recycle, where I would have a variable for each column, if they had:

Case 0 - do nothing
Case 1 - load a string with singular verbiage
Case else - load a string with plural verbiage

' To Display Message Box or not
Select Case TargA
    Case 0
        ' skip to TargB
    Case 1
        strTargA = "THERE IS " & TargA & " CELL NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
    Case Else
        strTargA = "THERE ARE " & TargA & " CELLS NOT MARKED AS 'M' - FOR MEDICARE" & vbCrLf
End Select
Select Case TargB
    Case 0 ' skip to string assembly for message box
    Case 1
    strTargB = "THERE IS " & TargB & "CELL WITHOUT A PREMIUM VALUE" & vbCrLf
    Case Else
    strTargB = "THERE ARE " & TargB & " CELLS WITHOUT PREMIUM VALUE" & vbCrLf
End Select
' Then I want to assemble the above strings, if not null such as:

If TargA + TargB = 0 Then
   MsgBox "Operations successful"
Else
   MsgBox strTargA + strTargB + "PLEASE CORRECT THE ABOVE IN GM AND RE-QUERY THE DATA"
End If

Open in new window

0
redrumkevAuthor Commented:
YOU MAKE ME LAUGH - yes we are high maintenance!!!!
0
redrumkevAuthor Commented:
These kind of questions call for way more than 500 points!
0
redrumkevAuthor Commented:
I am posting a new question to go with this.... link in a few seconds.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.