Find Number Then Do Until Loop Question

[RyanH]

I have a reference number as a Label in a Userform. I want to search all
columns for the reference number in Row 1 on Sheets1. If the reference
number is found, data that was entered in the Userform will save over top of
the old data that was in that reference numbers column. If the reference
number is not found I want the code to find the next empty cell (counting
every 4 columns) and apply the new data below the new empty cell. Here is
what I have so far?

Sub FindReferenceNumber ()

Dim myRef As Range, blank As Range

Set myRef = Worksheets("Plastic Faces").Rows(1).Find
(What:=lblRefNumber.Caption, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If myRef Is Nothing Then 'if reference number is not found
Do
For i = 0 To 63 'searches all columns in row 1
If IsEmpty(Cells(1, 4 * i + 2)) = True Then
Cells(1, 4 * i + 2) = myRef
Else
Exit Do
End If
Next i
Loop
End If

With Sheets("Plastic Faces").Range(myRef.Address)
.Offset(1, 0).Value = cboMaterial 'name of combobox
.Offset(2, 0).Value = cboMoldStyle 'name of combobox
.Offset(3, 0).Value = cboRadius 'name of combobox
.Offset(4, 0).Value = cboMoldSeam 'name of combobox
End With

End Sub

 

[ilia]

This is what I came up with, which seems to work - not clear whether
this is the behavior you want, but it will populate the first blank
4th column (counting from 2 ala 2 6 10 14 etc) and populate with
RefNumber and combobox values:

Sub FindReferenceNumber()
Dim myRef As Excel.Range
Dim i As Long


Set myRef = Worksheets("Plastic Faces").Rows(1).Find _
(What:=lblRefNumber.Caption, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If myRef Is Nothing Then 'if reference number is not found
Do
For i = 0 To 63 'searches all columns in row 1
If IsEmpty(Cells(1, 4 * i + 2)) = True Then
Set myRef = Cells(1, 4 * i + 2)
Exit Do
End If
Next i
Loop
End If


With myRef
.Value = lblRefNumber.Caption
.Offset(1, 0).Value = cboMaterial 'name of combobox
.Offset(2, 0).Value = cboMoldStyle 'name of combobox
.Offset(3, 0).Value = cboRadius 'name of combobox
.Offset(4, 0).Value = cboMoldSeam 'name of combobox
End With
End Sub

 

[RyanH]

Works great man! Thanks for the quick response! I'm glad I was at least
close to the correct code!