Find cell and sum given value to an existing value in corresponding column

Jfryguy

New Member
Joined
Jul 5, 2015
Messages
2
I need two things done when the "Submit" button is hit to process the order.

  • Find D2 in C:C, if found, add H6 to value to corresponding cell in E:E
  • Find E3 in J:J, if found, ADD E4 to corresponding cell in L:L
  • (Same command of bullet two ^, but also for F3-F4, G3-G4, H3-H4) (This is so that I get the total quantity added for all items purchased to the existing quantity)

image.png
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Assuming the button is a Forms Control button. If not, Change the title line to Private Sub CommandButton1_Click(), or whatever command button number it is and install the code in the sheet code module. Otherwise, install the code in the standard code module as it is below.
Code:
Sub camptown()
Dim sh As Worksheet, fn1 As Range, fn2 As Range, fn3 As Range, fn4 As Range, fn5 As Range
Set sh = ActiveSheet
    With sh
        Set fn1 = .Range("C:C").Find(.Range("D2").Value, , xlValues, xlWhole)
        If Not fn1 Is Nothing Then
            fn1.Offset(0, 2) = fn1.Offset(0, 2).Value + .Range("H6").Value
        End If
        For i = 5 To 8
            If .Cells(4, i) <> "" Then
                Set fn2 = .Range("J10", .Cells(Rows.Count, 10).End(xlUp)).Find(.Cells(3, i), , xlValues, xlWhole)
                If Not fn2 Is Nothing Then
                    fn2.Offset(0, 2) = fn2.Offset(0, 2).Value + .Cells(4, i).Value
                End If
            End If
            Set fn2 = Nothing
        Next
    End With
End Sub
 

Jfryguy

New Member
Joined
Jul 5, 2015
Messages
2
Assuming the button is a Forms Control button. If not, Change the title line to Private Sub CommandButton1_Click(), or whatever command button number it is and install the code in the sheet code module. Otherwise, install the code in the standard code module as it is below.
Code:
Sub camptown()
Dim sh As Worksheet, fn1 As Range, fn2 As Range, fn3 As Range, fn4 As Range, fn5 As Range
Set sh = ActiveSheet
    With sh
        Set fn1 = .Range("C:C").Find(.Range("D2").Value, , xlValues, xlWhole)
        If Not fn1 Is Nothing Then
            fn1.Offset(0, 2) = fn1.Offset(0, 2).Value + .Range("H6").Value
        End If
        For i = 5 To 8
            If .Cells(4, i) <> "" Then
                Set fn2 = .Range("J10", .Cells(Rows.Count, 10).End(xlUp)).Find(.Cells(3, i), , xlValues, xlWhole)
                If Not fn2 Is Nothing Then
                    fn2.Offset(0, 2) = fn2.Offset(0, 2).Value + .Cells(4, i).Value
                End If
            End If
            Set fn2 = Nothing
        Next
    End With
End Sub




Thanks so Much! Works Great! One additional thing.

If I wanted to add an additional sheet called "Inventory" to separate the inventory as shown in the following image, how would this code need to be changed?

Capture.png
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
That is a whole new ball game. Please put it in a new thread for others to view and perhaps respond to.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,122,551
Messages
5,596,797
Members
414,103
Latest member
imamalidadashzada

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