VBA - Extract data from userform into only 1 of 4 potential cells (first blank/empty cell)

[77highland]

Hi Guys,

Been trying to figure out the below query for a while with little success:

I wish for certain info to be extracted from a userform and then entered into only 1 of 4 potential cells (1st that is blank/empty).

Using the below table as an example........
If userform ComboBoxUnitNo = "001" Then
TextBoxDefect.Value is exported into either C3, E3, G3 or I3 (but only into the 1st cell (from left) that is empty.
i.e:- If C3 contained text then TextBoxDefect.Value would be exported into E3.

A	B	C	D	E	F	G	H	I
Unit No	Date	Defect	Date	Defect	Date	Defect	Date	Defect
001
002
003
004

<tbody>
</tbody>

 

[JLGWhiz]

The conditions are a little ambiguous but if you are looking for code that will find the next empty cell after the last cell with data in a row, then assume the row has been assigned to rw As Long variable, destination sheet sh As Worksheet.

Dim sh As Worksheet, rw As Long, tgtCell As Range
Set sh = Sheet(1)
Set tgtCell = sh.Cells(rw, Columns.Count).End(xlToLeft).Offset(0, 1)
tgtCell = TextBoxDefect.Value

Based on the posted illustration, the above code would assume that the date column is not empty for that particular entry. But there was no explanation regarding the date columns or how they are filled. Depending on that action, it might require looping through the cells to find the next empty one. Hard to tell with incomplete procedure information.