look up next empty row and write data to it

December 2, 2018 at 07:53:00
Specs: Windows 7
I have this working code that searches for MyVal variable and if not found it writes the data to the next empty column. However, I have found that comboboxes do not like horizontal lists so I have transposed all of my data to a vertical list. I need to re-write my code to search for the next empty row and then reference that row to write the data to. Can one of you experts look at my existing code and help me convert it to search and write to next empty row instead of next empty column?
Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & " already exists, do you want to 
overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list
Set sourceRng = Worksheets("Borrower 
Database").Range("5:5").Find(What:=myVal, LookAt:=xlWhole) 'locate column 
where to copy from
Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")

If Not sourceRng Is Nothing Then
Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
If Answer = vbNo Then
    Exit Sub
Else
With ws2
Application.EnableEvents = False
    .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
    .Range(.Cells(6, sourceRng.Column), .Cells(8, sourceRng.Column)).Value = 
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
    .Range(.Cells(9, sourceRng.Column), .Cells(10, sourceRng.Column)).Value 
= ws1.Range("G11:G12").Value 'Ratios
    .Cells(11, sourceRng.Column).Value = ws1.Range("G15").Value 'Reserves
    .Cells(12, sourceRng.Column).Value = ws1.Range("D15").Value 'Credit 
Score
Application.EnableEvents = True
End With
End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    .Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
    .Range(.Cells(6, lCol), .Cells(8, lCol)).Value = 
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
    .Range(.Cells(9, lCol), .Cells(10, lCol)).Value = 
ws1.Range("G11:G12").Value 'Borrower Name
    .Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
Application.EnableEvents = True
End With
End If
End Sub

Any help would be greatly appreciated :-)

message edited by mecerrato


See More: look up next empty row and write data to it

Reply ↓  Report •

#1
December 3, 2018 at 03:57:23
Pease review the information found here or do a Google search for indents in VBA code or similar.

http://www.vbforums.com/showthread....

message edited by DerbyDad03


Reply ↓  Report •

#2
December 3, 2018 at 19:22:08
I was able to figure it out by reading a bunch of sites and some trial and error, I will post for anyone else seeing something similiar:
lRow2 = .Cells(6, 3).End(xlDown).Row + 1
        .Cells(lRow2, 3).Value = ws1.Range("F5").Value


Reply ↓  Report •

#3
December 4, 2018 at 08:08:25
Whether or not your code works for others - and for you - depends on 2 things:

1 - Your definition of "the next empty row".
2 - The data layout.

Using your code, with a starting location of C6, your code will return a number of different values depending on the data layout.

For example, with this data, the value returned will not be the "next empty row". lrow2 will be set equal to 8, which is not empty:

       C
5
6
7    Data
8    Data
9    Data
10
11
12

With this data, the value will be 10, which is the "next empty row" after C6, but not the next empty row after all the data in Column C:

       C
5
6    Data
7    Data
8    Data
9    Data
10
11   Data
12   

This data set will also return 10, which in this case is the next empty row after all the data:

       C
5
6    Data
7    Data
8    Data
9    Data
10
11  
12  

The main issue is that your code starts at C6 and looks down. That can result in different values being returned based on the layout of the data. Empty cells within the data set can impact the result. If you want to consistently return the next empty row after all data in a column, you need to send VBA to the bottom of the sheet and then look up until data is found. Adding 1 to that result will give you the "next empty cell" after all of the data in the specified column.

lRow2 = .Cells(Rows.Count, 3).End(xlUp).Row + 1

In addition, if there is column that contains more data than the specified column (e.g. C) then the "next empty row" in Column C may not be the next empty row in the sheet. In reality, all you are finding is the next empty cell in the specified column.

If you have columns of varying length then you may need to loop through each column until you find the longest one. Once that is found, you can use that column number in the instruction. That will ensure that you've found the next empty row in the sheet, not just the next empty cell in a given column.

e.g.

Sub FindLastRow()
 With Sheets(2)
'Loop through Columns, set longCol_Num to longest Column number
  For col_Num = 1 To UsedRange.Columns.Count
   temp_longCol = .Cells(Rows.Count, col_Num).End(xlUp).Row + 1
     If temp_longCol > longCol Then
       longCol = temp_longCol
       longCol_Num = col_Num
     End If
  Next

'Use longest column number to find next empty row in sheet
     lRow2 = .Cells(Rows.Count, longCol_Num).End(xlUp).Row + 1
 End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question