| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am so close....if null add new problem
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I am so close....if null add new problem
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I am so close....if null add new problem
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 "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Charting Null Values | Charts and Charting in Excel | |||
| NULL pointer problem Error=[0x80040204] | Excel Discussion (Misc queries) | |||
| CountIf & null values problem | Excel Worksheet Functions | |||
| IF IS NULL PROBLEM | Excel Worksheet Functions | |||
| IIF IS NULL Problem | Excel Worksheet Functions | |||
Linear Mode
