Can this be done?

M

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
 
M

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
 
M

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
 
K

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top