Can this be done?

[michael.beckinsale]

Hi All,

The code snippet below loops thru a series of different option buttons
on a form. What l now need to do is assign the result of each
iteration to a variable. So in this case the 1st iteration would
assign the result to the variable C1. How can l amend the code so that
iteration 2 would assign the code to variable C2?

All help & suggections greatly appreciated.

i = 1
For i = 1 To 12
c1 = Controls("re" & i).Value
If Controls("abs" & i).Value = True Then
c1 = c1.Address(False, False)
End If
If Controls("row" & i).Value = True Then
c1 = c1.Address(True, False)
End If
If Controls("col" & i).Value = True Then
c1 = c1.Address(False, True)
End If
Next i

Regards

Michael

 

[Keith74]

Hi Micheal

How about using an array to store the data instead?

hth

Keith

 

[Matthew Pfluger]

What you should use here is an array variable:

Sub LoadArray()
Dim c(1 To 12) As Variant ' array variable
Dim i As Long

i = 1
For i = 1 To 12
c(i) = Controls("re" & i).Value

If Controls("abs" & i).Value = True Then
c(i) = c(i).Address(False, False)
End If

If Controls("row" & i).Value = True Then
c(i) = c(i).Address(True, False)
End If

If Controls("col" & i).Value = True Then
c(i) = c(i).Address(False, True)
End If
Next i

End Sub

For more information on array variables, check out Chip Pearson's website.
Here's a link to the index:
http://www.cpearson.com/excel/topic.aspx

HTH,
Matthew Pfluger

 

[michael.beckinsale]

Hi Keith,

Thanks for the prompt reply.

I was thinking along the same lines, but my array coding is not that
great, perhaps you would be kind enough to help?

I think the biggest problem will come later in the code when l have to
insert the array elements into a string. Would it look something like
this something like this?

"abcd" & MyArray(0) & "efgh" & MyArray(1) etc

Regards

Michael

 

[Keith74]

Hi Michael

"abcd" & MyArray(0) & "efgh" & MyArray(1) etc

yep, that or very close
btw, the site Matt listed above is very good

cheers

keith

 

[michael.beckinsale]

Hi All,

Sorry for the delay in getting back to you.

I have applied you examples / suggestions and all is now working as
required.

Thank you very much indeed.

Regards

Michael