Overwrite existing entry macro

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I have the following code (thanks to the internet) in a spreadsheet that will slightly change in a new use. The code itself creates a new entry on a new line, is it possible to overwrite an existing entry on whichever line i'm currently viewing (there is other code that let's me go through the entries as they were entered). I would only need to be able to change one cell (G5) from the potential options of Approved, Pending Approval, Proposed.

Thank you in advance for your assistance!

VBA Code:
Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "G3,G5,G7,F10,F11,F12,F13,F14,F15,F16,F18,F19,F20,F21,F22,F23,G10,G11,G12,G13,G14,G15,G16,G18,G19,G20,G21,G22,G23,H10,H11,H12,H13,H14,H15,H16,H18,H19,H20,H21,H22,H23"

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Data")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Range("G3:I3").Select
    Selection.ClearContents
    Range("G5:I5").Select
    Selection.ClearContents
    Range("G7:I7").Select
    Selection.ClearContents
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G3:I3").Select
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Not sure exactly what your workflow is.

The code above, as you say, will copy a bunch of data to a new line on the History sheet.
Now you want to modify the Gx (x is your active row) in the history sheet and select a value (Approved, etc)

When do you want to do this? After the data is copied? During the copy proces? Or do you just want a macro to go down the list of the Historysheet and set the approved status for each row (so nothing to do with the data copy)?


By the way, the last bit of your code can be made very much faster by not selecting each of the cells:
VBA Code:
  Range("G3:I3").ClearContents
    Range("G5:I5").ClearContents
    Range("G7:I7").ClearContents
    Range("F10") = "0"
    Range("F11") = "0"
    etc
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Sijpie,

Thank you very much for your response!

The current workflow is as follows:

The change order or new budget information (bulletin) gets inputted using the form and code above by clicking a button 'Add to Data'. But it would be inputted using one of the options described: Proposed, Pending Approval, or Approved. There's other code and buttons on the sheet that let's you scroll through the previous entry(s) so if Proposed or Pending Approval was initially selected, i'd like this new code to overwrite that existing line should it change to Approved (or if the budget line items change) so that it's not a whole new input and a double entry and could use a button 'Update Data'

Hope this makes sense.

SVAR Actual - Budget.xlsm
ABCDEF
1COR-PCO Number:
2
3Change Order:
4
5RFI/Bulletin/ROM:
6
7Status:
8
9Item #DescriptionMaterial Unit PriceShop LaborSite LaborTotal
10001Main driveway curbing, not to exceed amount----
11002Type 1 - Flagstone cladding and cap, not to exceed amount----
Input
Cell Formulas
RangeFormula
F10:F11F10=SUM(C10:E10)
Cells with Data Validation
CellAllowCriteria
D5:F5Any value
D7:F7List=Lists!$Y$1:$Y$3


btw thanks for the suggestion! I was not looking forward to writing that out as it was as this new form will be significantly larger! :p
 
Last edited:

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Changing the field 'Status' (D7) would be the most likely and only update however would like this new button and code to update any possible field like C10:E11 just in case a proposed change order gets approved with a lower/higher number.
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
So if the user selects a line in the history sheet and presses the update button, then that line should be placed back into the input sheet. On pressing the add button in the input sheet, then the original line in the history sheet should be updated (overwriting the time stamp and the user stamp with current time and user and any changes).

Perhaps you can copy the workbook without sensitive info (fake names etc) into dropbox or so? That will make it easier.
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
ok i finally have a somewhat working copy of the workbook. Only the first section is currently working but this should give you a good idea of what is trying to be accomplished.

Add Input button adds to the data tab and hoping to add an Update Input button that will take the data that is pulled up and update accordingly.

 

Watch MrExcel Video

Forum statistics

Threads
1,129,871
Messages
5,638,773
Members
417,052
Latest member
Noobest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top