VBA Macro - find next Empty Row within Dynamic Range

cary.coser · Apr 29th 2020

Im having a really hard time with creating a dynamic way of entering data from a userform.

I have a column ("A") down the left which is reference to the product name (example AA123), there is 78 rows with the name AA123 and they increment by 1 every 78 rows, untill around AA400.

When the user enters product name into a text box in a userform (for example AA123), and then i want to find the next empty row If column A is equal to the value in the text box (AA123) and add the values from with in Textbox1 offset by 2

Ive tried a few different methods but nothing seems to be working. It either enters the data into every single cell with in that range. Or it will enter the data into the last empty cell within the whole worksheet.

This is the code i have written but it adds data to every single row that has the Value "AA123" in Column A. I want it to only be the next empty row

Any Help would be greatly appreciated.

Code

Dim ws As Worksheet
Dim AddNew As Range
Set ws = ActiveSheet
Dim j As Long, i As Long
'Assign your variables
Set ws = ThisWorkbook.Sheets("Sheet1")
'Finds the last empty row in Column D
Set AddNew = ws.Range("D65356").End(xlUp).Offset(1, 0)
‘This is the section that is meant to find Value of the grating name.
'Loop through the cells in col A
For j = 4 To 20000
'Check if cell value matches text box's value, if matches then write the other text box value to the row
If ws.Cells(j, 1).Value = ProductName.Text And IsEmpty(ws.Cells(j, 4)) Then
'Finds last empty row and Adds data from Text box into sheet.
AddNew.Offset(0, 0).Value = TextBox1.Text
End If
Next j
End Sub

Display More

dangelor · May 2nd 2020

Seems like it should be a simple fix but your description and code is a bit vague on exact where you want the text written. Could you attach a small workbook with before and after worksheets?

jolivanes · May 3rd 2020

Like dangelor mentioned, it is hard to grasp what you want.

Where are these empty cells?

Which is "the other text box"?

If the empty cells are between cell value changes:

Change references where required.

Code

Sub Maybe()
Sheets("Sheet1").Cells(Columns(1).Find("TextBox1", , , 1).End(xlDown).Row, 1).Offset(1).Value = TextBox2
End Sub

If there are no empty cells in Column A, you'll end up below all you data in that column

The best thing to do is add an attachment with sufficient data and a before and after. No personal data in it though.

**royUK** · May 3rd 2020

cary

Welcome to Ozgrid. Please take a few minutes to read the forum rules and you will understand why I have added code tags to your post.

**royUK** · May 3rd 2020

Loops should be avoided when possible. Find a more efficient way, in this case I would use .Find.

This code is untested and based ontrying to interpret your code

Code

Sub x()
Dim ws As Worksheet
Dim AddNew As Range, rCl As Range
Set ws = ActiveSheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'Finds the last empty row in Column D
Set AddNew = .Cells(.Rows.Count, 4).End(xlUp).Offset(1, 0)
'This is the section that is meant to find Value of the grating name.
Set rCl = .UsedRange.Columns(1).Find(Me.TextBox1.Value)
'Check if cell value matches text box's value, if matches then write the other text box value to the row
If Not rCl Is Nothing Then
If rCl.Value = ProductName.Text And IsEmpty(rCl.Offset(, 3)) Then
'Finds last empty row and Adds data from Text box into sheet.
AddNew.Value = Me.TextBox1.Text
End If
End If
End With
End Sub

Display More

VBA Macro - find next Empty Row within Dynamic Range

Share

Tags