Solved: Use Textbox Values in Loop

[John_Mc]

Hi All,

I have a form (called frmSearch) with around 50 text boxes on it. My code below is designed to

1) look at the text box to get the account code
2) move to another sheet and find each instance of the account code
3) add up each match.

I can get it to work fine for a specific textbox (called txtac1), but I can't get it to loop to the next textbox (txtac2, txtac3....txtac50)

I've looked elsewhere in the forum and seen bits of code that potentially might apply, but as my code below demonstrates, my VB is too basic to understand it and customise it.

Can anyone help me out?


Cheers,

John Mc

[vba]
private sub SumData()

Dim MyCostCentre As Long, MyAccountCode As Long, MyHours As Long, MyCost As Long
Dim CurrentBox As TextBox, z As Long, x As Integer

MyCostCentre = txtcc1.Value

For z = 1 To 50
CurrentBox = "txtac" & z
MyAccountCode = CurrentBox.Value
Sheets("Raw Data").Activate

For x = 1 To 2000
If Cells(x, 3).Value = MyCostCentre Then
If Cells(x, 4).Value = MyAccountCode Then
MyHours = MyHours + Cells(x, 6).Value
MyCost = MyCost + Cells(x, 7).Value
Else
End If
Else
End If
Next x

Sheets("Extracted Data").Activate
Range("a1").Select
For y = 1 To 50
If ActiveCell.Value = MyAccountCode Then
ActiveCell.Offset(0, 1).Value = MyHours
ActiveCell.Offset(0, 2).Value = MyCost
Else: ActiveCell.Offset(1, 0).Activate
End If
Next y
Next z

End sub
[/vba]

[geekgirlau]

Try changing

[vba]CurrentBox = "txtac" & z
MyAccountCode = CurrentBox.Value [/vba]

to

[vba]MyAccountCode = Me.Controls("txtac" & z ).Value [/vba]

[John_Mc]

I believe thats my last three posts you've helped me out on. If this continues, I'll just have to send you my work directly!

Thanks again for your help!

Cheers,

John Mc

[geekgirlau]

Happy to help if I can