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
"Carlee" wrote:
> 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
>
> --
> Carlee
Similar Threads
