Writing a macro to add a value to a cell

[Koner0]

Hello,
I'm trying to write a macro, which would take the address from cell1, the address is created from formula:

=cell("address";INDEX('2021'!$A$1:$AF$187;MATCH($B8;'2021'!$B$1:$B$187;0);MATCH($H$5;'2021'!$A$1:$AF$1;0)))​

then take the value from selected cell(I launch the macro from that cell), and add it to the value of the cell, from that address.(from the formula)

The cell with the found address is in another sheet.(In the same workbook.)
Thanks for help,
Simon

 

[maabadi]

Welcome to MrExcel Message Board.
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[Koner0]

Here is the example workbook.It's got two sheets, one with a sales document, ant the second is a small plan for the month.
I would like to make it to add the quantity to the right cell in the Plan sheet. Now I do it manually , maybe it would be possible to select a range (row with indexes) then hit the macro button and it would add the quantities to the right cells in the plan?
Thanks for help,
Simon

 

[maabadi]

Please give example. I don't understand, what exactly you want.

 

[Koner0]

Let's say I would like to sell three products, I write the index numbers, and next to it the quantity sold on that day. Then I would like to highlight the indexes, choose the macro button, and the quantities would be added to the day, that they were sold on- in the plan sheet(the date is in the cell J6).
I need to print the document, with the quantity and indexes and the date. After that I would highlight the indexes and automatically add the quantities to the plan.
Normally I have 190 products, and searching for them in the plan takes a lot of time.(Even if I use the Ctrl+F funtion )

 

[maabadi]

How About:

Sub AddValuesToProDucts()
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Dim A As Long, B As Long
Set Sh1 = Sheets("Document")
Set Sh2 = Sheets("Plan")
Lr1 = Sh1.Range("E" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row

For i = 9 To Lr1
A = Application.WorksheetFunction.Match(Sh1.Range("E" & i), Sh2.Range("B1:B" & Lr2), 0)
B = Application.WorksheetFunction.Match(Sh1.Range("J6"), Sh2.Range("A1:AG1"), 0)
Sh2.Cells(A, B).Value = Sh2.Cells(A, B).Value + Sh1.Range("I" & i).Value
Next i
End Sub

 

[Koner0]

Thanks for help, it will save me a lot of time. Is it possible to modify the Sub, to only add the active cell index? I think of selecting the cell with the index number(the quantity is always four cells to the right) and it will search for the index in the plan, and add the quantity(only for the one, chosen product)?

 

[maabadi]

Right Click on the WorkSheet Document & Select View Code Then add this Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Dim A As Long, B As Long
'Application.EnableEvents = True
'Application.ScreenUpdating = True
Set Sh1 = Sheets("Document")
Set Sh2 = Sheets("Plan")
Lr1 = Sh1.Range("E" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
'MsgBox "Selection Changed"
If Target.Cells.Count > 1 Then GoTo LetsContinue
If Not Intersect(Target, Range("E9:E" & Lr1)) Is Nothing Then
A = Application.WorksheetFunction.Match(Target, Sh2.Range("B1:B" & Lr2), 0)
B = Application.WorksheetFunction.Match(Sh1.Range("J6"), Sh2.Range("A1:AG1"), 0)
Sh2.Cells(A, B).Value = Sh2.Cells(A, B).Value + Target.Offset(0, 4).Value
MsgBox "Value Added To Sheet " & Sh2.Name & " Cell " & Sh2.Cells(A, B).Address & " Successfully"
End If
LetsContinue:
'Application.EnableEvents = False
'Application.ScreenUpdating = False
End Sub