First empty row in given range (i.e. C3:G16)

[ncortez]

Hi all,

I have looked through the web and have been unable to find an answer to my question.

I have a userform with a few text boxes for the user to enter data, as well as two combo boxes, with choices as follows:
cbo_1:Opportunity, Risk
cbo_2: High, Medium, Low.

I have a table in excel with 6 different "blocks".
High Opportunity: C6:G16
Medium Opportunity: H6:L16
Low Opportunity: M6:Q16

High Risk:C18:G18
Medium Risk: H18:L18
Low Risk: M18:Q18

I would like to have the data entered in the text boxes be put into the corresponding "block", according to what was chosen in the user form.

I know how to add the data in, however I am struggling to find a way to find the first empty row in a specific range. So, for example, if I already have two inputs under High Opportunity, I am trying to find a way to get the first empty row in the range C6:G16, which in this instance would be 8. Does anyone have any ideas on how this would be done??
I've struggled for a couple of days and have been unable to find anything that works online, so any help would be greatly appreciated! Thanks

[Kenneth Hobs]

Welcome to the forum!

Some of these might help.

[VBA]'=LastNBRow(A3:G10)
Function LastNBRow(rng As Range) As Long
Dim Lastrow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
Lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastNBRow = Lastrow
End Function

'=LastNBCol(A3:G10)
Function LastNBCol(rng As Range) As Long
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
LastNBCol = LastColumn
End Function

Function FindLastCellInRangeWithData(aRange As Range) As Range
Dim LastColumn As Integer
Dim Lastrow As Long
Dim LastCell As Range
Dim FindLastCellInRange As Range
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
Lastrow = Cells.Find(what:="*", After:=aRange(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(what:="*", After:=aRange(1, 1), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCellInRange = Cells(Lastrow, LastColumn)
End If
End Function[/VBA]

[ncortez]

The first function looks like what I need. Could you help me with the placement of it? I'm not familiar with functions at all... all I know is that they return a value, but I'm not sure how this value gets passed back to the sub procedure.

Here is the code I have. I have only implemented the calling of the function in the first case (high opportunity). I'm not sure if this is how I specify the range. Right now, it's giving me an error, so I must be doing something wrong.

[VBA]Private Sub AddTcTemplate()

Dim rng As Range
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("R&O")


'Add new
If Me.cbo_type = "Opportunity" Then
If Me.cbo_probability = "High" Then
rng = Range("C6:G15")
Call GetRow(rng)
lrow = ' need to set this to function return value
ws.Cells(lRow, 3).Value = Me.tbo_ID.Value
ws.Cells(lRow, 4).Value = Me.tbo_item.Value
ws.Cells(lRow, 5).Value = Me.tbo_amt.Value
ws.Cells(lRow, 6).Value = Me.tbo_investment.Value
ws.Cells(lRow, 7).Value = Me.tbo_ECD.Value

ElseIf Me.cbo_probability = "Medium" Then

ws.Cells(lRow, 8).Value = Me.tbo_ID.Value
ws.Cells(lRow, 9).Value = Me.tbo_item.Value
ws.Cells(lRow, 10).Value = Me.tbo_amt.Value
ws.Cells(lRow, 11).Value = Me.tbo_investment.Value
ws.Cells(lRow, 12).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Low" Then

ws.Cells(lRow, 13).Value = Me.tbo_ID.Value
ws.Cells(lRow, 14).Value = Me.tbo_item.Value
ws.Cells(lRow, 15).Value = Me.tbo_amt.Value
ws.Cells(lRow, 16).Value = Me.tbo_investment.Value
ws.Cells(lRow, 17).Value = Me.tbo_ECD.Value
End If

ElseIf Me.cbo_type = "Risk" Then
If Me.cbo_probability = "High" Then

ws.Cells(rng, 3).Value = Me.tbo_ID.Value
ws.Cells(rng, 4).Value = Me.tbo_item.Value
ws.Cells(rng, 5).Value = Me.tbo_amt.Value
ws.Cells(rng, 6).Value = Me.tbo_investment.Value
ws.Cells(rng, 7).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Medium" Then

ws.Cells(rng, 8).Value = Me.tbo_ID.Value
ws.Cells(rng, 9).Value = Me.tbo_item.Value
ws.Cells(rng, 10).Value = Me.tbo_amt.Value
ws.Cells(rng, 11).Value = Me.tbo_investment.Value
ws.Cells(rng, 12).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Low" Then

ws.Cells(rng, 13).Value = Me.tbo_ID.Value
ws.Cells(rng, 14).Value = Me.tbo_item.Value
ws.Cells(rng, 15).Value = Me.tbo_amt.Value
ws.Cells(rng, 16).Value = Me.tbo_investment.Value
ws.Cells(rng, 17).Value = Me.tbo_ECD.Value
End If
End If


End Sub[/VBA]

[ncortez]

Okay, I think I figured out how to get the value back to the sub procedure. This is what I have:

[VBA]Private Sub AddTcTemplate()

Dim rng As Range
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("R&O")


'Add new
If Me.cbo_type = "Opportunity" Then
If Me.cbo_probability = "High" Then
Set rng = Range("C6:G15")
lRow = GetRow(rng)
ws.Cells(lRow, 3).Value = Me.tbo_ID.Value
ws.Cells(lRow, 4).Value = Me.tbo_item.Value
ws.Cells(lRow, 5).Value = Me.tbo_amt.Value
ws.Cells(lRow, 6).Value = Me.tbo_investment.Value
ws.Cells(lRow, 7).Value = Me.tbo_ECD.Value

ElseIf Me.cbo_probability = "Medium" Then
Set rng = Range("H6:L15")
lRow = GetRow(rng)
ws.Cells(lRow, 8).Value = Me.tbo_ID.Value
ws.Cells(lRow, 9).Value = Me.tbo_item.Value
ws.Cells(lRow, 10).Value = Me.tbo_amt.Value
ws.Cells(lRow, 11).Value = Me.tbo_investment.Value
ws.Cells(lRow, 12).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Low" Then
Set rng = Range("M6:Q15")
lRow = GetRow(rng)
ws.Cells(lRow, 13).Value = Me.tbo_ID.Value
ws.Cells(lRow, 14).Value = Me.tbo_item.Value
ws.Cells(lRow, 15).Value = Me.tbo_amt.Value
ws.Cells(lRow, 16).Value = Me.tbo_investment.Value
ws.Cells(lRow, 17).Value = Me.tbo_ECD.Value
End If

ElseIf Me.cbo_type = "Risk" Then
If Me.cbo_probability = "High" Then
Set rng = Range("C18:G27")
lRow = GetRow(rng)
ws.Cells(rng, 3).Value = Me.tbo_ID.Value
ws.Cells(rng, 4).Value = Me.tbo_item.Value
ws.Cells(rng, 5).Value = Me.tbo_amt.Value
ws.Cells(rng, 6).Value = Me.tbo_investment.Value
ws.Cells(rng, 7).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Medium" Then
Set rng = Range("H18:L27")
lRow = GetRow(rng)
ws.Cells(rng, 8).Value = Me.tbo_ID.Value
ws.Cells(rng, 9).Value = Me.tbo_item.Value
ws.Cells(rng, 10).Value = Me.tbo_amt.Value
ws.Cells(rng, 11).Value = Me.tbo_investment.Value
ws.Cells(rng, 12).Value = Me.tbo_ECD.Value
ElseIf Me.cbo_probability = "Low" Then
Set rng = Range("M18:Q27")
lRow = GetRow(rng)
ws.Cells(rng, 13).Value = Me.tbo_ID.Value
ws.Cells(rng, 14).Value = Me.tbo_item.Value
ws.Cells(rng, 15).Value = Me.tbo_amt.Value
ws.Cells(rng, 16).Value = Me.tbo_investment.Value
ws.Cells(rng, 17).Value = Me.tbo_ECD.Value
End If
End If

'Recalculate grand total


End Sub
Function GetRow(rng As Range) As Long
Dim Lastrow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.

Lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End If
GetRow = Lastrow
End Function[/VBA]

However, now I'm struggling w/ the bolded line... it is giving me a "Object variable or with block variable not set" run time error. Any ideas as to why this is happening?

[Kenneth Hobs]

When working with lots of IF's, consider using Select Case. Me is probably not needed. If you must reference an object many times, use With to make it more efficient.

See if this helps. I included the other function so that you could check if the worksheet exists if needed. Of course a return of 0 means that all rows were blank.

Put public functions into a Module.

OF course you will need to add 1 if the return is greater than 0 to get the next blank row.

[vba]Sub Test_GetRow()
MsgBox GetRow(Worksheets("Sheet2").Range("C6:G15"))
End Sub

Function GetRow(rng As Range) As Long
Dim Lastrow As Long
If WorksheetFunction.CountA(rng) > 0 Then
'Search for any entry, by searching backwards by Rows.
Lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
End If
GetRow = Lastrow
End Function

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function[/vba]

[ncortez]

Kenneth,

Thanks for the Select Case tip! I changed it and the code looks a lot cleaner now.

I tried adding the message box, but I'm still getting the same error when the code goes into the function:

Function GetRow(rng As Range) As Long
Dim Lastrow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.

Lastrow = rng.Find(what:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End If
GetRow = Lastrow
End Function

The bolded line (lastrow = ....) gives me a Run Time Error 91 - "Object variable or with block variable not set." Any ideas how to fix this??

[Kenneth Hobs]

You did not use the code that I last posted for the function. I changed cells to rng.

[ncortez]

Ah i apologize, I did not see that. That did it! Thank you!