VB UserForm finding next empty cell to insert data. 1

[tudor30]

I am using the multipage property form the control toolbox for userforms. The top of the page uses textboxes to read info from the spreadsheet. The four values: OptionCode, PartNumber, LetterState, and PartDescription.

On the bottom of that page I'm using the multipage property. Its purpose is to update the spreadsheet for the four values stated above. (There is a multipage property being placed onto a page of another multipage property).

The process begins by searching columns for a specific part number. When I find that part number I identify all the values in that row and transfer them to top of page 1 in UserForm1.

Where I need HELP:
1) How to look for the next empty cell in column B if the part number is not found and then place the entered part number into that cell using a message box to confirm the new part number?

2) How to identify the cell Address and use it as the mark to insert additional information for that row?

The first IF Sets c = .Find for PartNumberBox
The Else that is to locate the next empty cell in column B and insert the value entered in PartNumberBox is at the bottom the code displayed.

Thanks for any suggestions,
John


Here's the current code:

Private Sub SubmitPartNumber_Click()

Dim c As Range
'Placing data from spreadsheet into top of Product Engineering page

With Sheets("Master Tracking&quot.Range("B1:B1000&quot
Set c = .Find(PartNumberBox.Value, LookIn:=xlValues)

If c = PartNumberBox.Value Then

OptionCode.Value = c.Offset(0, -1).Value
PartNumber.Value = c.Offset(0, 0).Value
LetterState.Value = c.Offset(0, 1).Value
PartDescription.Value = c.Offset(0, 2).Value
PiecesPerEngine.Value = c.Offset(0, 3).Value
RefPartNumber.Value = c.Offset(0, 4).Value

If c.Offset(0, 5).Value = "Yes" Then 'Service Part Yes
OptionButton1.Value = True
ElseIf c.Offset(0, 5).Value = "No" Then 'Service Part No
OptionButton2.Value = True
Else 'No Entry for Service Part
OptionButton1.Value = False
OptionButton2.Value = False

End If

ServiceSource.Value = c.Offset(0, 6).Value
TermCode.Value = c.Offset(0, 7).Value
PEChangeLead.Value = c.Offset(0, 8).Value
LevelofChange.Value = c.Offset(0, 9).Value

Sheets("Master Tracking&quot.Range("K1&quot = c.Offset(0, 9).Value

'Placing data from spreadsheet into input textboxes

'Placing data for Main Tab of Product Engineering
OptionCodeBox.Value = c.Offset(0, -1).Value
LetterStateBox.Value = c.Offset(0, 1).Value
PartDescriptionBox.Value = c.Offset(0, 2).Value
PiecesPerEngineBox.Value = c.Offset(0, 3).Value
RefPartNumberBox.Value = c.Offset(0, 4).Value

If c.Offset(0, 5).Value = "Yes" Then 'Service part Yes
OptionButton3.Value = True
ElseIf c.Offset(0, 5).Value = "No" Then 'Service Part No
OptionButton4.Value = True
Else 'No Entry for Service Part
OptionButton3.Value = False
OptionButton4.Value = False

End If

ServiceSourceComboBox.Value = c.Offset(0, 6).Value
TermCodeBox.Value = c.Offset(0, 7).Value
PEChangeLeadBox.Value = c.Offset(0, 8).Value

With LevelofChangeComboBox
.ColumnCount = 1
.RowSource = Sheets("Master Tracking&quot.[K1:K5].Address
End With


Else
Set c = Cells(65536, 2).End(xlUp).Row + 1
c.Value = PartNumberBox.Value

' SubmitPartNumber_Click

End If

End With

End Sub

 

[PHV]

Set c = Cells(65536, 2).End(xlUp).Row + 1
Set c=Cells(Cells(65536, 2).End(xlUp).Row + 1,2)
You can now play with c.Address or c.Row

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tudor30]

I am receiving Run Time Error 91
Object variable or With block variable not set

It then highlights: If c <> PartNumberBox.Value Then

Any suggestions?

Private Sub SubmitPartNumber_Click()

Dim c As Range
Dim LastCell As Range

'Placing data from spreadsheet into top of Product Engineering page

With Sheets(&quot;Master Tracking&quot.Range(&quot;B1:B1000&quot

Set c = .Find(PartNumberBox.Value, LookIn:=xlValues)

If c <> PartNumberBox.Value Then
Set LastCell = Cells(Cells(65536, 2).End(xlUp).Row + 1, 2)

If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If

LastCell.Select
LastCell.Value = PartNumberBox.Value

 

[PHV]

If c <> PartNumberBox.Value Then
If c.Value <> PartNumberBox.Value Then



Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Oops.
If c <> PartNumberBox.Value Then
If c Is Nothing Then


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tudor30]

SUCCESS!!!


Thank you PHV.

John