John_Mc
05-10-2006, 07:50 PM
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
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
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
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