I am so close....if null add new problem

[Carlee]

Hi there,

With assistance, I have put together the following code.

The user has a UserForm which they enter values. On click, the system
searches Column A of the 'HQ Input Log' to see if the month entered in
cboMonth on the UserForm, already exists in Column A. If it does, the system
displays a message asking if the user wants to replace what is already in the
'HQ Input Log'. If the user clicks 'Yes', the system will replace the
existing row with teh new values entered on the UserForm.

If the user clicks No, then the process is canceled, and no action occurs.

Problem:
If the system does not find a matching value in Column A of 'HQ Input Log',
I want the system to be insert the new entry, in the next available row.

Can anyone help me out?

Private Sub cmdSubmit_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String
Dim rw As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.cboMonth.Value

rw = SH.Range("A:A").Find(sStr, LookIn:=xlValues).Row
If MsgBox("The HQ Input Log already contains a reading entry for the
month of " & sStr & ". Do you want to replace the existing entry with your
new one?", vbYesNo) = vbYes Then

SH.Cells(rw, 1) = Me.cboMonth.Value
SH.Cells(rw, 2) = Me.txtPreparedBy.Value
SH.Cells(rw, 3) = Me.txtNoLdsAtSmelter.Value
SH.Cells(rw, 4) = Me.txtWeight.Value
SH.Cells(rw, 5) = Me.txtMoisture.Value
SH.Cells(rw, 6) = Me.txtDryWeight.Value
SH.Cells(rw, 7) = Me.txtCuWeight.Value
SH.Cells(rw, 8) = Me.txtCuPercent.Value

MsgBox "Your submission has replaced the previous reading for the
month of " & sStr
Else
MsgBox "The entry process has been canceled. No entry has been
added to the HQ Input Log"
End If

'if the month to be added does not already exist in the HQ Input Log

ActiveWorkbook.Sheets("HQ Input Log").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 0) = Me.cboMonth
ActiveCell.Offset(0, 1) = Me.txtPreparedBy
ActiveCell.Offset(0, 2) = Me.txtNoLdsAtSmelter.Value
ActiveCell.Offset(0, 3) = Me.txtWeight.Value
ActiveCell.Offset(0, 4) = Me.txtMoisture.Value
ActiveCell.Offset(0, 5) = Me.txtDryWeight.Value
ActiveCell.Offset(0, 6) = Me.txtCuWeight.Value
ActiveCell.Offset(0, 7) = Me.txtCuPercent.Value
Unload Me
MsgBox "Your submission has been successfully added to the HQ Log."

End Sub

 

[JLGWhiz]

I did not test this, Carlee, but it should work. Try it on a copy before
installing.

rw = SH.Range("A:A").Find(sStr, LookIn:=xlValues).Row
If Not sStr is Nothing Then
If MsgBox("The HQ Input Log already contains a reading entry for the
month of " & sStr & ". Do you want to replace the existing entry with your
new one?", vbYesNo) = vbYes Then

SH.Cells(rw, 1) = Me.cboMonth.Value
SH.Cells(rw, 2) = Me.txtPreparedBy.Value
SH.Cells(rw, 3) = Me.txtNoLdsAtSmelter.Value
SH.Cells(rw, 4) = Me.txtWeight.Value
SH.Cells(rw, 5) = Me.txtMoisture.Value
SH.Cells(rw, 6) = Me.txtDryWeight.Value
SH.Cells(rw, 7) = Me.txtCuWeight.Value
SH.Cells(rw, 8) = Me.txtCuPercent.Value

MsgBox "Your submission has replaced the previous reading for the
month of " & sStr
Else
MsgBox "The entry process has been canceled. No entry has been
added to the HQ Input Log"
End If
Else
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Activate
ActiveCell.Offset(0, 0) = Me.cboMonth
ActiveCell.Offset(0, 1) = Me.txtPreparedBy
ActiveCell.Offset(0, 2) = Me.txtNoLdsAtSmelter.Value
ActiveCell.Offset(0, 3) = Me.txtWeight.Value
ActiveCell.Offset(0, 4) = Me.txtMoisture.Value
ActiveCell.Offset(0, 5) = Me.txtDryWeight.Value
ActiveCell.Offset(0, 6) = Me.txtCuWeight.Value
ActiveCell.Offset(0, 7) = Me.txtCuPercent.Value
Unload Me
MsgBox "Your submission has been successfully added to the HQ Log."

End Sub

 

[JLGWhiz]

Forgot the End If...

rw = SH.Range("A:A").Find(sStr, LookIn:=xlValues).Row
If Not sStr is Nothing Then
If MsgBox("The HQ Input Log already contains a reading entry for the
month of " & sStr & ". Do you want to replace the existing entry with your
new one?", vbYesNo) = vbYes Then

SH.Cells(rw, 1) = Me.cboMonth.Value
SH.Cells(rw, 2) = Me.txtPreparedBy.Value
SH.Cells(rw, 3) = Me.txtNoLdsAtSmelter.Value
SH.Cells(rw, 4) = Me.txtWeight.Value
SH.Cells(rw, 5) = Me.txtMoisture.Value
SH.Cells(rw, 6) = Me.txtDryWeight.Value
SH.Cells(rw, 7) = Me.txtCuWeight.Value
SH.Cells(rw, 8) = Me.txtCuPercent.Value

MsgBox "Your submission has replaced the previous reading for the
month of " & sStr
Else
MsgBox "The entry process has been canceled. No entry has been
added to the HQ Input Log"
End If
Else
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Activate
ActiveCell.Offset(0, 0) = Me.cboMonth
ActiveCell.Offset(0, 1) = Me.txtPreparedBy
ActiveCell.Offset(0, 2) = Me.txtNoLdsAtSmelter.Value
ActiveCell.Offset(0, 3) = Me.txtWeight.Value
ActiveCell.Offset(0, 4) = Me.txtMoisture.Value
ActiveCell.Offset(0, 5) = Me.txtDryWeight.Value
ActiveCell.Offset(0, 6) = Me.txtCuWeight.Value
ActiveCell.Offset(0, 7) = Me.txtCuPercent.Value
End If
Unload Me
MsgBox "Your submission has been successfully added to the HQ Log."

End Sub