PDA

View Full Version : Excel VBA control loop with Find



simora
04-22-2013, 07:25 PM
I am using VBA find to post a textbox value to a sheet based on the .row & .column of the found value and the column based on a ComboBox selection.

If the sheet location ( Cells(rsRow, rsCol).Value = "" ) is BLANK it gets posted,
but if a value is already there, then, it inserts a row and then does the posting.


If Cells(rsRow, rsCol).Value = "" Then
Cells(rsRow, rsCol) = TextBox2.Value

' Put comments here
If OptionButton4 = True Then
Cells(rsRow, rsCol).AddComment
Cells(rsRow, rsCol).Comment.Visible = False
Cells(rsRow, rsCol).Comment.Text Text:=" " & Chr(10) & "Posted " & " "& Online_Bank.Text & ""
Cells(rsRow, rsCol).Comment.Shape.TextFrame.AutoSize = True
End If



' TEST HERE IF IVAL IS >1 * TIMES BEFORE INSERTING THE NEW ROW

' LOOP * TIMES TO SEE IF the new (rsRow, rsCol).Value = ""


Else
Cells(rsRow, rsCol).Select
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(1, 0).Value = TextBox2.Value



What I'm trying to do now is:

See if there's multiple rows with that match the criteria, and if so how many. (iVal)

When there's multiple matching rows, how can I test that x amount of times to find a blank useable column before I insert a new row.

iVal tells me how many found occourances there is.

iVal = Application.WorksheetFunction.CountIf(myrange, Label23.Caption)

How can I tell the code to loop iVal times to find a suitable Row&Column cell before it inserts a new row when iVal is more than 1 ?

Using Win XP & Excel 2003