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
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