Update existing data or Insert new problem

[Corey]

I have data stored in a single row in another sheet(sheet2).
I have a formatted sheet to present the data(sheet1)

When a new record is created i use the code below to save it from sheet1 to sheet2:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub StoreSTQData()
If Range("G5").Value = "" Then
MsgBox "Please generate a Unique Number before proceeding !!", vbInformation, "XXXX."
Range("G5").Select
Exit Sub
Else
Dim rngFound As Range
On Error Resume Next
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Unprotect
With Worksheets("Sheet2").Range("A:A")
Set rngFound = .Find(What:=Sheets("Sheet1").Range("G5").Value, After:=.Range("A:A"),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
Matchbyte:=False)
' Place data from Sheet2 sheet into Sheet1 Sheet
If rngFound.Value = Sheets("Sheet1").Range("G5").Value Then
' If Unique Number is not listed Insert new row and place data into that row
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Value = Sheets("Sheet1").Range("G5").Value
Range("B1").Value = Sheets("Sheet1").Range("B1").Value
Range("C1").Value = Sheets("Sheet1").Range("B3").Value
Range("D1").Value = Sheets("Sheet1").Range("B5").Value
Range("E1").Value = Sheets("Sheet1").Range("B7").Value
Range("F1").Value = Sheets("Sheet1").Range("B9").Value
Range("G1").Value = Sheets("Sheet1").Range("B11").Value
Range("H1").Value = Sheets("Sheet1").Range("B13").Value
Range("I1").Value = Sheets("Sheet1").Range("B14").Value

Range("J1").Value = Sheets("Sheet1").Range("B17").Value
Range("K1").Value = Sheets("Sheet1").Range("B19").Value

Range("L1").Value = Sheets("Sheet1").Range("A22").Value
Range("M1").Value = Sheets("Sheet1").Range("A28").Value
Range("N1").Value = Sheets("Sheet1").Range("A30").Value
Range("O1").Value = Sheets("Sheet1").Range("A32").Value

Range("P1").Value = Sheets("Sheet1").Range("A35").Value
Range("AA1").Value = Sheets("Sheet1").Range("A41").Value
Range("Q1").Value = Sheets("Sheet1").Range("H46").Value
Range("R1").Value = Sheets("Sheet1").Range("H47").Value
Range("S1").Value = Sheets("Sheet1").Range("H48").Value
Range("T1").Value = Sheets("Sheet1").Range("H49").Value
Range("U1").Value = Sheets("Sheet1").Range("H50").Value
Range("V1").Value = Sheets("Sheet1").Range("H53").Value

Range("W1").Value = Sheets("Sheet1").Range("B55").Value

Range("X1").Value = Sheets("Sheet1").Range("M5").Value
Range("Y1").Value = Sheets("Sheet1").Range("N5").Value
Range("Z1").Value = Sheets("Sheet1").Range("L1").Value
Sheets("Sheet2").Visible = False
' Clear the data from Sheet2
With Sheets("Sheet1")
..Select
..Unprotect
..Range("G5").Value = ""
..Range("B1").Value = ""
..Range("L1").Value = ""
..Range("B3").Value = ""
..Range("B5").Value = ""
..Range("M5").Value = ""
..Range("N5").Value = ""
..Range("B7").Value = ""
..Range("B9").Value = ""
..Range("B11").Value = ""
..Range("B13").Value = ""
..Range("B14").Value = ""
..Range("B17").Value = ""
..Range("B19").Value = ""
..Range("A22").Value = ""
..Range("A28").Value = ""
..Range("A30").Value = ""
..Range("A32").Value = ""
..Range("A35").Value = ""
..Range("H46").Value = ""
..Range("H47").Value = ""
..Range("H48").Value = ""
..Range("H50").Value = ""
..Range("H55").Value = ""
..protect
End With
Call Workbook_Info
Call Macro1
Else
If rngFound.Value <> "" Then
' Unique number in column A already exists so need to UPDATE existing data rather than insert a new
row
Exit Sub
Sheets("Enter-Exit Page").Select
End If
End If
End With
End If
Sheets("Sheet2").Visible = False
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If i need to review/modify a record i find it using listboxes on a userform :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ListBox3_Click()
Application.ScreenUpdating = False
UserForm3.Hide
With ActiveWorkbook.Worksheets("Quotation")
..Select
Dim rngFound As Range
On Error Resume Next
With Worksheets("Sheet2").Range("L:L")
Set rngFound = .Find(What:=ListBox3.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
' Place data from Data sheet2 into Sheet2
Range("G5").Value = rngFound.Offset(, -11).Value
Range("B1").Value = rngFound.Offset(, -10).Value
Range("L1").Value = rngFound.ofset(, 14).Value
Range("B3").Value = rngFound.Offset(, -9).Value
Range("B5").Value = rngFound.Offset(, -8).Value
Range("M5").Value = rngFound.Offset(, 12).Value
Range("N5").Value = rngFound.Offset(, 13).Value
Range("B7").Value = rngFound.Offset(, -7).Value
Range("B9").Value = rngFound.Offset(, -6).Value
Range("B11").Value = rngFound.Offset(, -5).Value
Range("B13").Value = rngFound.Offset(, -4).Value
Range("B14").Value = rngFound.Offset(, -3).Value
Range("B17").Value = rngFound.Offset(, -2).Value
Range("B19").Value = rngFound.Offset(, -1).Value
Range("A22").Value = rngFound.Value
Range("A28").Value = rngFound.Offset(, 1).Value
Range("A30").Value = rngFound.Offset(, 2).Value
Range("A32").Value = rngFound.Offset(, 3).Value
Range("A35").Value = rngFound.Offset(, 4).Value
Range("A41").Value = rngFound.Offset(, 15).Value
Range("H46").Value = rngFound.Offset(, 5).Value
Range("H47").Value = rngFound.Offset(, 6).Value
Range("H48").Value = rngFound.Offset(, 7).Value
Range("H50").Value = rngFound.Offset(, 8).Value
Range("B55").Value = rngFound.Offset(, 11).Value
End With
End With
Unload Me
Application.ScreenUpdating = True
Range("A1").Select
Sheets("Sheet2").Visible = False
Sheets("Sheet1").Select
Call PrintPreview
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Then i modify it to suit then re-save it, but I need to differenciate between a modified record and
a new record.

This i want to do with the 1st code in this post (Sub STQData()).

But i cannot get the code right to either save the record as a NEW record and insert a new row in
Sheet2, or if the Unique Value in sheet1.range("G5") is already found in sheet2.column(A), then
UPdate any modification instead of inserting a new row.

I have messed around with this for heaps of time but cannot get it to either update or save
dependant upon IF the G5 value is found in sheet2 Column A.

Can anyone assist with this to end my frustrations?

CTM

 

[Steven P]

One suggestion:

you really need to present your problem shortly and clearly, the code
is just too long to be reverse-engineered.

 

[Corey]

In a nut shell i need code to Check to see IF a numerical value in shet2.Column A has a value that
is in sheet1.range("G5")

If it does then i need to data in various cells in sheet1 to be placed into the row in sheet2 that
contains the unique number.
If the number in sheet1.range("G5") is not found in shet2.Column A then a New Row is inserted into
sheet2 and the values in sheet1 are then placed into Sheet2's new row instead.

The code below (STQData) does input the values to sheet2 but it will always insert a new row, even
IF there is found a unique number in Column A instead of updating the values in it.


One suggestion:

you really need to present your problem shortly and clearly, the code
is just too long to be reverse-engineered.

 

[Steven P]

seems quite simple, seach in range, if found return the column number.
Otherwise add new row


For search function, try vlookup() or

Using following code

public sub GetPartsNo () as integer
Dim a_col As Integer
Dim a_success As Boolean

Dim t_name As String

t_name = "05"
a_col = 1
a_success = False

' Look for Product, get column address
For Each a In Range(RefProductionEquips)
If Trim(a.Value) = t_name Then
a_success = True
Exit For
End If
a_col = a_col + 1
Next

If a_success = True Then
'GetPartsNo = a_col
Else
'GetPartsNo = 0
End If
end sub