excelvbavlookup

Adding Vlookup + Vlookup with active cell

I have SHEET 1 and SHEET 2. They represent two sheets that for the purpose of this explanation I made in the same sheet.
enter image description here

I need when I press the Red button, VBA code takes the 100 from cell C11, adds to cell I11 (mind you, I11 is in a different sheet), and then erases all content from cell C11 and E11.

Since it is only one button that will be used for several rows, I have read that you can use "active cell" to avoid creating a button for each row.

This is the formula I thought of to add those two. I do not know how to put it into VBA to run with "activesheet".

=IF(NOT(ISBLANK(G11)),VLOOKUP(A11,G11:I20,3,0))+ VLOOKUP(A11,A11:E20,3,0)

The result should be:
enter image description here

How do I incorporate activesheet together with the vlookup formula?

Erasing cells I have used before so I can look it up.

Solution

  • Please, try using the next code. You can call it from the button you already have, or copy it As the respective code:

    Sub updateQuantity()
  Dim rng As Range, ws2 As Worksheet, rngItem As Range
  
  Set rng = ActiveCell
  If rng.cells.count > 1 Then MsgBox "You need to select only ONE CELL...": Exit Sub
  If rng.column <> 1 Then MsgBox "The active cell must be in A:A column...": Exit Sub
  
  Set ws2 = rng.Parent.Next ' Set the second worksheet
                            ' in this code it is the next sheet after the one keeping active cell
                            
  Set rngItem = ws2.Range("A:A").Find(What:=rng.value, LookAt:=xlWhole) 'find the Item in the second sheet
  If Not rngItem Is Nothing Then 'if the item has been found:
     'add the existing value in I:I (found range row) with the one from C:C column (activecell row)
     ws2.cells(rngItem.Row, "I").value = ws2.cells(rngItem.Row, "I").value + rng.Offset(, 2).value
     rng.Offset(, 2).Resize(, 3).ClearContents 'clear content of C:E columns of the active cell row
  Else
     MsgBox "No any match for item """ & rng.value & """ has been found in """ & _
           ws2.name & """ sheet..."
  End If
End Sub

    If the button you have is of ActiveX type, write updateQuantity in its Click event. If it is a Form button, assig the above macro to it.

    If something not clear enough, do not hesitate to ask for clarifications...