Assign Results from If...Then and Loop to an Array (VBA)

[a]

Given the basic structure below, anyone know how to add the results (10 of them) from the If...Then statements inside the For Next loop to an empty array ?

---------------------------------------------------------------------
For i = 1 to 10

If ()= True Then
cvtVariable = "Text"

ElseIf ()= True Then
cvtVariable = "Number"

ElseIf ()= True Then
cvtVariable = "Error"

Else
cvtVariable = "Blank"

End If

Next i

-----------------------------------------------------------------------

Dim cvt1 As String
Dim cvt2 As String
Dim cvt3 As String
Dim cvt4 As String
Dim cvt5 As String
Dim cvt6 As String
Dim cvt7 As String
Dim cvt8 As String
Dim cvt9 As String
Dim cvt10 As String

Dim CellValueTestVariableArray As String

Dim cvtVariable As String

CellValueTestVariableArray = Array(cvt1, cvt2, cvt3, cvt4, cvt5, cvt6, cvt7, cvt8, cvt9, cvt10)

-------------------------------------------------------------------
The result would look something like this, for example:

cvt1 = "Text"
cvt2 = "Blank"
cvt3 = "Error"
cvt4 = "Number"
cvt5 = "Number"
cvt6 = "Blank"
cvt7 = "Number"
cvt8 = "Text"
cvt9 = "Error"
cvt10 = "Text"

 

[Myrna Larson]

I can't figure out what you are after. The syntax

If () = True Then

is not valid. It gives a compiler error: Expected: Expression

What is it you are trying to compare, and with what?

As far as adding results to an empty array, you have two different ways to
create the array.

One is Dim X(1 to 10) As Variant
The other is Dim X AS Variant: Redim X(1 to 10)

With the 2nd syntax, you can write X = Array(1,2,3,4,5,6,7,8,9,10)
With the 1st it has to be X(1) = 1: X(2) = 2, etc.

Given the basic structure below, anyone know how to add the results (10 of

them) from the If...Then statements inside the For Next loop to an empty array
?

 

[mudraker]

Paul


Why not use an array instead of 10 variables



Sub ddd()
Dim Cvt(1 To 10) As String
Dim i As Integer
Dim cvtVariable As String

For i = 1 To 10 Step 1
If IsError(Cvt(i)) Then
cvtVariable = "Error"
ElseIf IsNumeric(Cvt(i)) Then
cvtVariable = "Number"
ElseIf Trim(Cvt(i)) = "" Then
cvtVariable = "Blank"
End If
Next
End Su

 

[Dana DeLouis]

I'm not sure either. Would any ideas here help?

Sub Demo()
Dim v(1 To 10) As String
Dim i As Long

For i = 1 To 10
Select Case i
Case 1, 8, 10
v(i) = "Text"
Case 2, 6
v(i) = "Blank"
Case 3, 9
v(i) = "Error"
Case 4, 5, 7
v(i) = "Number"
End Select
Next i
End Sub

HTH
Dana DeLouis

Given the basic structure below, anyone know how to add the results (10 of

them) from the If...Then statements inside the For Next loop to an empty
array ?

 

[a]

Hi:

Basically, I was missing the (1 to 10) after my Array declaration, and a couple other variables to get the results from the If...Then statements into the CellValueTestVariableArray.

You can see that the cvtVariable is used in the If...Then statements to get various values, eg. "Text" , etc into the CellValueTestVariableArray.

Probably is a simpler way of getting the results from the If...Then and Loop into the Array, but I don't see it yet.

This is what ended up working.
-----------------------------------------------------------------------------------------
Dim CellValueTestVariableArray(1 To 10) As String
Dim CellVariableArray As Variant
Dim Cell_Variable As Variant
Dim cvtVariable As String
Dim iCV as Integer

For iCV = 1 to 10
------------------------------

Cell_Variable = CellVariableArray(iCV - 1)

If (Cell_Variable) = "" Then
cvtVariable = "Blank"

ElseIf (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(Cell_Variable, Workbooks("Get SEC Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False))) = True Then
cvtVariable = "Blank"

ElseIf (Application.WorksheetFunction.IsNumber(Cell_Variable)) = True Then
cvtVariable = "Number"

ElseIf (Application.WorksheetFunction.IsError(Application.WorksheetFunction.VLookup(Cell_Variable, Workbooks("Get SEC Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False))) = True Then
cvtVariable = "Error"

ElseIf (Application.WorksheetFunction.IsText(Application.WorksheetFunction.VLookup(Cell_Variable, Workbooks("Get SEC Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False))) = True Then
cvtVariable = "Text"

Else
cvtVariable = "Blank"

End If

CellValueTestVariableArray(iCV) = cvtVariable 'The value to assign to the array element
cvtVariable = "" 'Clear contents before next loop

Next iCV
=======================================================

 

[Dana DeLouis]

Hello. I can't follow this too well. Would any ideas here help? This is
untested. The idea was to pull the messy VLookup out.

With Application.WorksheetFunction
For iCV = 1 To 10
Cell_Variable = CellVariableArray(iCV - 1)

Temp = .VLookup(Cell_Variable, _
Workbooks("Get SEC Data.xls"). _
Worksheets("VLookUp_Income"). _
Range("VLookup_Income"), 2, False)

If Cell_Variable = vbNullString Then
cvtVariable = "Blank"

ElseIf .IsNA(Temp) Then
cvtVariable = "Blank"

ElseIf .IsNumber(Cell_Variable) Then
cvtVariable = "Number"

ElseIf .IsError(Temp) Then
cvtVariable = "Error"

ElseIf .IsText(Temp) Then
cvtVariable = "Text"
Else
cvtVariable = "Blank"
End If

CellValueTestVariableArray(iCV) = cvtVariable
cvtVariable = vbNullString
Next iCV

End With

Dana DeLouis

Hi:

Basically, I was missing the (1 to 10) after my Array declaration, and a

couple other variables to get the results from the If...Then statements into
the CellValueTestVariableArray.

You can see that the cvtVariable is used in the If...Then statements to

get various values, eg. "Text" , etc into the CellValueTestVariableArray.

Probably is a simpler way of getting the results from the If...Then and

Loop into the Array, but I don't see it yet.

This is what ended up working.
-------------------------------------------------------------------------- ---------------
Dim CellValueTestVariableArray(1 To 10) As String
Dim CellVariableArray As Variant
Dim Cell_Variable As Variant
Dim cvtVariable As String
Dim iCV as Integer

For iCV = 1 to 10
------------------------------

Cell_Variable = CellVariableArray(iCV - 1)

If (Cell_Variable) = "" Then
cvtVariable = "Blank"

ElseIf

(Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(Ce
ll_Variable, Workbooks("Get SEC
Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False)))
= True Then

cvtVariable = "Blank"

ElseIf (Application.WorksheetFunction.IsNumber(Cell_Variable)) = True Then
cvtVariable = "Number"

ElseIf

(Application.WorksheetFunction.IsError(Application.WorksheetFunction.VLookup
(Cell_Variable, Workbooks("Get SEC
Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False)))
= True Then

cvtVariable = "Error"

ElseIf

(Application.WorksheetFunction.IsText(Application.WorksheetFunction.VLookup(
Cell_Variable, Workbooks("Get SEC
Data.xls").Worksheets("VLookUp_Income").Range("VLookup_Income"), 2, False)))
= True Then

cvtVariable = "Text"

Else
cvtVariable = "Blank"

End If

CellValueTestVariableArray(iCV) = cvtVariable 'The value to assign to the array element
cvtVariable = "" 'Clear contents before next loop

Next iCV
=======================================================

I can't figure out what you are after. The syntax

If () = True Then

is not valid. It gives a compiler error: Expected: Expression

What is it you are trying to compare, and with what?

As far as adding results to an empty array, you have two different ways to
create the array.

One is Dim X(1 to 10) As Variant
The other is Dim X AS Variant: Redim X(1 to 10)

With the 2nd syntax, you can write X = Array(1,2,3,4,5,6,7,8,9,10)
With the 1st it has to be X(1) = 1: X(2) = 2, etc.