Possible to run through multiple variables?

[chirayu]

Hi All,


This is a bit of a weird question because I think it isn't possible, at least not in the way I'm thinking of doing it. I was wondering if I can use a for loop or something similar instead of an if condition to add the contents of a variable e.g.

Original Code:

Sub Sample()

Dim DC1 As String
Dim DC2 As String
Dim DC3 As String
Dim DC4 As String
Dim i As Integer

i = Application.WorksheetFunction.CountA(Range("A:A"))

If i = 1 Then
  DC1 = Cells(1, 1)
  'othercode
ElseIf i = 2 Then
  DC1 = Cells(1, 1)
  DC2 = Cells(2, 1)
  'other code
ElseIf i = 3 Then
  DC1 = Cells(1, 1)
  DC2 = Cells(2, 1)
  DC3 = Cells(3, 1)
  'othercode
Else
  DC1 = Cells(1, 1)
  DC2 = Cells(2, 1)
  DC3 = Cells(3, 1)
  DC4 = Cells(4, 1)
  'othercode
End If

End Sub

Imaginary Stuff I'm talking about:

Sub Sample()

Dim DC1 As String
Dim DC2 As String
Dim DC3 As String
Dim DC4 As String
Dim DC As Integer
Dim i As Integer

DC = Application.WorksheetFunction.CountA(Range("A:A"))

For i = 1 To DC

  "DC" & i = Cells(i, 1)

Next

End Sub

 

[Deepak]

You may hold it to array like as below...

Option Explicit

Sub Sample()
Dim dcc() As Variant
Dim dc As Variant
Dim i As Integer

dc = Application.WorksheetFunction.CountA(Range("A:A"))

ReDim dcc(dc)

For i = 1 To dc
    dcc(i) = Cells(i, 1).Value
Next

End Sub

 

[chirayu]

Interesting solution Deepak. Didn't think of holding it as Array.

 

[Deepak]

Or more easier way..

Option Explicit

Sub Sample()
Dim dcc As Variant, dc As Integer

dc = Application.WorksheetFunction.CountA(Range("A:A"))

dcc = Application.Transpose(Range(Cells(1, 1), Cells(dc, 1)))

End Sub

but Transpose has limitation...