Can this be done?
 

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

Can this be done?
 

Hi Micheal

How about using an array to store the data instead?

hth

Keith

Can this be done?
 

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" wrote:

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

Can this be done?
 

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

Can this be done?
 

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

Can this be done?
 

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