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?
 
zorvek (Kevin Jones)Connect With a Mentor 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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.