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