Dimming many variables at once and using loop variable inside of them?

Question

I have a number of reports that I am pulling information from and then placing into new fields. I'm wondering if there's a good way to strucutre it so that, depending on the report type, variables are dimmed to reference the cells that I need, but with the loop counter built-in. Here is a quick mock-up of what I'm looking for:

sub demo()
dim i as long
dim a,b,c,d,e as range

for i = 1 to 100
   if cells(i,1).value2 like "Financials-Q4" Then
     set a = cells(i,21).value2
     set b = cells(i,44).value2
     set c = cells(i,65).value2
  elseif cells(i,1).value2 like "Amor-Q4" Then
     set a = cells(i,100).value2
     set b = cells(i,97).value2
     set c = cells(i,157).value2
     set d = cells(i,89).value2
  end if
next i

 'Start using variables
 for i = 1 to 100
   If a = b Then
      c = "Does not compute"
   Else
      c = "Does compute"
   End if
next i

Currently my code is basically just referencing each individual cell value which is extremely time consuming to clean up / change around.

1. don't use Set for simple value assignment. 2. only e is an integer, a, b, c & d are variants. 3. don't use integer because you think it sounds cool; there is almost no reason to use integers as a var-type. — , Feb 16 '18 at 16:42
Also, if `a`,`b`, and `c` are the same value in a scenario, why not just use a single variable for that? — BruceWayne, Feb 16 '18 at 16:43
Your new loop (`'start using variables`) has no reason to loop 100 times. All it does is set `c` and loop over doing the same thing, since nothing changes each loop. — BruceWayne, Feb 16 '18 at 17:18

Vityata · Accepted Answer · 2018-02-16T16:53:43.373

Something that should work:

Sub Demo()
    Dim i As Long
    Dim a As Range
    Dim b As Range
    Dim c As Range, d As Range, e As Range

    For i = 1 To 100
        If Cells(i, 1).Value2 = "Financials-Q4" Then
            Set a = Cells(i, 24).Value2
            Set b = Cells(i, 24).Value2
            Set c = Cells(i, 24).Value2
        ElseIf Cells(i, 1).Value2 = "Amor-Q4" Then
            Set a = Cells(i, 100).Value2
            Set b = Cells(i, 97).Value2
            Set c = Cells(i, 157).Value2
            Set d = Cells(i, 89).Value2
        End If
    Next
End Sub

Assuming that you do not need Like, but simply =, because you have not used and * or ? signs to use the Like.

This is some example of the usage of Like with the results in the immediate window (source):

?"Vito6" Like "V?to6"
True
?"Vito6" Like "Vito#"
True
?"Vito6" Like "V*6"
True
?"Vito6" Like "Vit[a-z]6"
True
?"Vito6" Like "Vit[A-Z]6"
False
?"Vito6" Like "Vit[!A-Z]6"
True
?"12 34" Like "## ##"
True
?"12 34" Like "1[0-9] [0-9]4"
True

Concerning the usage of Integer instead of Long - Why Use Integer Instead of Long?

Thanks Vityata, though I made a mistake -- the variables sohuld be ranges, as I plan to loop over them — Gregory, Feb 16 '18 at 16:52

Dimming many variables at once and using loop variable inside of them?

1 Answers1