Add item to next cell on button click (with limited cells to use)

[mrfahaji]

Hi,

I am using a macro for this operation but my VBA skills aren’t great. I’ve used the record function to get the first iteration to do the most basic function, and it works, but now I’m trying to add more conditions I can’t do it!

Scenario: A list of books to choose from (a long list) a drop down. Once happy with the choice, you can click a button to add the book to your inventory. However, you only have four slots available. My current code works to add the chosen book to your first slot but that’s it.

Essentially I want the code to be:

If slot 1 is empty, then add the book to slot 1.
Else if slot 2 is empty
Else if slot 3
Else if slot 4
Else do not add book and display message that inventory is full

I'd also like to prevent you adding the same book twice, but realise that could be more difficult.

The final ‘message’ is optional since I currently have a separate formula-based message which advises the inventory is full and not to click the button. I found some instructions on the ISEMPTY command but when I tried to incorporate it, it didn’t work.

This is the VBA I am using to achieve the first step. Note that the two sheets are ‘Books’ and ‘Inventory’, and the blank cells are two rows each, spanning AG5:AN12. If being merged cells is a problem, I could always add the books to another single cell elsewhere and then link it. Also worth noting that I want the inventory to be permanently stored (unless deliberately removed), hence why I have a paste values code in there too.

Sheets("Inventory").Select
Range("AG5:AN6").Select
ActiveCell.FormulaR1C1 = "=Books!R[16]C[-30]"
Range("AG5:AN6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AG7:AN8").Select
Sheets("Books").Select
Range("C18:N20").Select


Even though I have the logic for this operation, I am just not familiar with the VBA language to get the right commands in the right format etc. Any help appreciated - apart from the additional caveats I don't think it will be that taxing for those with the VBA skills!

 

[DanteAmor]

Hi @mrfahaji, thanks for posting on the forum.

... If being merged cells is a problem ...
... apart from the additional caveats ...

First recommendation, DO NOT use merged cells.

I will help you with the code to fill 4 destination cells: AG5, AG6, AG7 and AG8.
I suppose that the drop down list is in the same cell C21.

Then try this:

Sub Add_Item()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim rng As Range, c As Range, f As Range
  Dim i As Long
  Dim addit As Boolean
  Dim itm As String
 
  Set sh1 = Sheets("Inventory")
  Set sh2 = Sheets("Books")
 
  Set rng = sh1.Range("AG5:AG8")
  itm = sh2.Range("C21").Value
 
  If itm = "" Then
    MsgBox "Select a book"
    Exit Sub
  End If
 
  Set f = rng.Find(itm, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    MsgBox "You cannot add the same book 2 times."
    Exit Sub
  End If
 
  For i = 1 To rng.Rows.Count
    If rng.Cells(i).Value = "" Then
      rng.Cells(i).Value = itm
      MsgBox "Book stored in the slot: " & i
      addit = True
      Exit For
    End If
  Next
 
  If addit = False Then
    MsgBox "inventory is full"
    Exit Sub
  End If
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[mrfahaji]

Thank you! I will try and implement this over the next couple of days.

Re the merged cells, if I can avoid it I will but I may need to have to keep the cells merged due to the layout of the entire sheet. If so then I might have to do this elsewhere in the sheet and then just link to that list.

 

[mrfahaji]

Hi Dante, thank you so much for this. I may have some follow up questions, because I'm going to try and tweak a few things (and also I'd like to try and understand/learn a little of how it works so I'm not JUST copying and pasting!) but this is an amazing start. I never considered having those pop up boxes either (rather just have a message in a cell), but now thinking of keeping them!

 

[mrfahaji]

Hi again Dante, just to say it's working brilliantly, thanks again. I have tried to understand it and most of it makes sense.

I've made a few small edits - I had to keep the merged cells, so instead set the range to CG5:CG8 and then linked the merged AG col cells directly to the CG column, but it still functions just the same