Solved: Solved:Merge string with int to make variable name

[lucky245]

Sorry, this is probably dead simple for you whizz kids but I've got a mental block on this for the moment.

I have 40 drop downs which are each call drp1, drp2 drp3 etc. (not my naming I hasten to add.) each holds a value depending on the answer Yes =1, No = 0 N/A = 2. These are then added together to get the total yes's (N/A are ignored by means of an if statement) At the moment the code reads


total = total + drp1
total = total + drp2
etc
etc
etc
total = total + drp40

All I'm trying to do is write a simple loop rather than leave it in the long list. Brain just wont function today.

Thanks in advance

[Imdabaum]

[vba]
Dim iLoop as Integer
Dim ctl As Control
Dim total as Integer
total = 0
For iLoop = y To z
cName = "drp" & iLoop
Set ctlCurrentControl = Me.Controls(cName)
'Debug.Print ctlCurrentControl.Name
total = total + ctlCurrentControl.value
'May need some extra bits in case your dropdown box is null or an empty string.
Next iLoop2
[/vba]

[lucky245]

Brilliant, Thankyou

[Imdabaum]

I'd say I am glad to help, but that knowledge was imparted to me from this forum as well... I asked the same question a few months ago.

You're welcome though...