PDA

View Full Version : vba loop to find and change value in textbox on userform



sjohnp2112
03-08-2017, 01:19 PM
Well I thought this would be easy, sort of, but....

I have a worksheet, and column A with a range of 29 cells (A2:A30) containing expense category names for a budget workbook, and range A30 is called
"Totals" which represents the totals row that sums daily transactions. I have a userform that populates textboxes with the values in column A, but I want to exclude the value "TOTALS" or at least set its Textbox.Text = "N/A".

This would work great if the transaction worksheet was set have no more and no less than 29 rows, but I made it so users can insert/delete rows as they wish. One user might tailor his budget workbook to track only 10 expense categories where someone else may want to track 20+; therefore the "TOTALS" row is not fixed. I tried variations of loops, arrays and such and got nowhere. I don't think it should be that difficult but I'm still new at this so thanks for patience....any help would be much appreciated.

thanks,
sjohnp2112

JKwan
03-08-2017, 02:28 PM
you may want to do something like this:


For i = 2 To 30
If Sheet1.Cells(i, "A") <> "TOTALS" Then
' do your stuff
Else
Exit Sub ' found your total, exit out
End If
Next I

SamT
03-08-2017, 04:19 PM
The Bottom Cell is never considered?


LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 to LastRow - 1
'Do your thing here



Next