VBA "Variable required. Can't assign to this expression" error - Goal-seek analysis

user_16340709 0
Viewed 93

I'm a beginner with VBA and by no means a programmer. I am trying to write a macro which iterates through cells in a row and carries out goal-seek analysis on multiple cells. I could not use any built-in functions from Excel because goal-seek analysis is only possible for one cell and not many.

My macro should iterate through a specific range of cells in a row (from columns A to E) while carrying out goal-seek analysis. I could not get this to work without a loop or through using something like For Each Row In Range so I tried creating an array of letters for the second loop to work.

However, when I run the program I get a "Variable required. Can't assign to this expression" error. Could someone point out what I'm doing wrong or if I could do something more efficiently?

Thank you

Sub CommandButton1_Click()
Dim c As String
c = "ABCDE"

'Create array of letters to iterate through cells in a row
Dim i, j As Long
Dim c_array() As String
ReDim c_array(Len(c) - 1)

For j = 1 To Len(c)
    c_array(j - 1) = Mid$(c, j, 1)
Next

'Goal seek analysis
For i = 0 To (Len(c_array) - 1)
        Cells(3, c_array(i)).GoalSeek Goal:=Cells(2, "G"), ChangingCell:=Cells(2, c_array(i))
    Next i
End Sub
vba excel
1 Answers

Dim i, j As Long does only declare j As Long but i As Variant. In VBA you need to specify a type for every variable:

Dim i As Long, j As Long

The issue you get is Len(c_array) where it is defined as array Dim c_array() As String. Len does not work with arrays. If you want to know the upper or lower bound of the array you need to use LBound and Ubound:

For i = LBound(c_array) To UBound(c_array)

But actually there is no need to put the data into an array. You can use the string directly:

Const c As String = "ABCD"
Dim i As Long
For i = 1 To Len(c)
    Dim Col As String
    Col = Mid$(c, i, 1)

    Cells(3, Col).GoalSeek Goal:=Cells(2, "G"), ChangingCell:=Cells(2, Col)
Next i

Or even better use column numbers instead of their letters if they are consecutive.
ABCD is column 1 to 4:

Dim iCol As Long
For iCol = 1 To 4
    Cells(3, iCol).GoalSeek Goal:=Cells(2, "G"), ChangingCell:=Cells(2, iCol)
Next iCol
user_32196130
Related