Procedure too Large - Ideas to simplify

[rsfalkowski]

Hello all,

This is my first post, so forgive me if its not the best.

I have some vba code that is acting as an inventory management programs for tools. It consist of about 350 if-elseif-end if statements (see below).

If range("x9").Value - range("aa9").Value > 1 Then
MsgBox "Wrench1 currently checked out!", , "Inventory Alert"
ActiveCell.Offset(0, -1).Select
ActiveCell.ClearContents

ElseIf range("aa9").Value - range("x9").Value > 0 Then
MsgBox "Wrench1 currently checked in!", , "Inventory Alert"
ActiveCell.Offset(0, -1).Select
ActiveCell.ClearContents

The ("x9") and ("aa9") are referencing a CountIf formula on the active worksheet like so:

=COUNTIF($k$10:$p$10000,"Wrench1")

So essentially the VBA code is looking at this cell and if it is greater than 1, it means someone has tried to check out a tool that has been checked out, and it works the same for checking in a tool.

However, with 350 tools and 350 if statements, its become too large. Is there a way to consolidate counting tools and checking to see if they are checked-in/out within VBA?

Thanks in advance!

 

[DanteAmor]

Hi & Welcome to MrExcel

You could put the code from the beginning and show several if elseif, to find a pattern and simplify the code.