VBA To Check If Cells In A Range Have Data While Looping Through Rows

[AcornNut]

Hello all,

I have the following situation... I have a VBA code that checks an inventory sheet for deficiencies and expirations and, when it finds them, fills out each new row on the Supply Order Form with the name of the item, how many are used or expired, and puts that number in the appropriate column. Columns H-K indicate whether the item is used, expired (or expiring), damaged, or new.
The code will loop through each row and determine whether the item is already listed (in which case it adds to the existing number in the appropriate column) and, if not, creates a new line item in the next blank row and adds the data accordingly.

What I'm needing, and not sure how to even begin, is once the row has been populated with the data to check to see if more than 1 column has data in it for that specific row. Meaning, if one line item is missing 4 and 5 are about to expire, and only 7 are require to be stocked, I need the code to bring up a pop-up to alert the user that more than required is being ordered and asks if any modifications need to be made. (It's possible that some of the items that show expired were actually used). This will help not to order more than needed.

Any suggestions on how to check the 4 cells in that given row for data in 2 or more of those cells, bring up the pop-up, and if data needs to be corrected, make the appropriate changes.

If that's not enough info, or if you need clarification, please don't hesitate to ask.

Thanks in advance for your help!!!

COLUMN B COLUMN C (COLUMNS) H I J K

[TABLE="width: 946"]
<tbody>[TR]
[TD="colspan: 2"]Description[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD]Qty[/TD]
[TD]Qty to[/TD]
[TD="colspan: 2"]Qty[/TD]
[TD]New[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Code[/TD]
[TD="colspan: 2"]Item[/TD]
[TD="colspan: 2"][/TD]
[TD]Used[/TD]
[TD]Expire[/TD]
[TD="colspan: 2"]Damage[/TD]
[TD]Item[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Order 1[/TD]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD]8[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Order 2[/TD]
[TD="colspan: 2"]2[/TD]
[TD="colspan: 2"][/TD]
[TD]20[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Order 3[/TD]
[TD="colspan: 2"]3[/TD]
[TD="colspan: 2"][/TD]
[TD]24[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

My first question is where did this new item come from. Was it purchased? Found in stock? Turned-in by user? If I am going to add an item to inventory, I want to know how it got there. My experience tells me that Found in stock items that do not appear on current inventory listings are the result of either clerical error in records keeping or negligence in stockroom maintenance, or both and before I would add the item to inventory under its present identification, I would research it back to its origin to determine why it is not already on reacord. Then it can be reconciled appropriately. If it is purchased or turned in by the user, then it is in effect a new item and only the expiration date needs to be considered in establishing order quantities.

 

[AcornNut]

Any NEW items will be purchased. This will mostly be if an item is ordered but not available (back order) and is being substituted with the new item by our warehouse. This is very rarely the case and, in all likelyhood, will be manually entered, so that particular cell isn't imperative. I'm sure that whatever the code is, that column can be excluded. Even so, I still have 3 cells in each given row that I need the code to check if there's data in more than just one cell and pull up the pop-up, then once the pop-up action is completed, move onto the next row.

 

[JLGWhiz]

Since the item is being added to the next available row at the bottom of the used range, you can easily find that row, assuming column A will always be filled when an item is added, by using this syntax:

Dim myRange as Range
With ActiveSheet
    myRange = .Cells(Rows.Count, 1).End(xlUp) 'This gets you to the cell in column A for the last row entry.
    If myRange.Offset(, 2) = 0 Then
         'Do Something
    End if
    If myRange.Offset(, 3) = 0 Then
         'Do xomething
    End If
    'etc.
End With

Once you locate the anchor cell for the row, you simply use the offse3t to address the cells in various columns.

 

[AcornNut]

Thanks. Just from looking over the code, it looks like it examines each cell in that given row individually one at a time. Is that correct? If so, I'm not sure that's what I'm looking for. All I'm needing is for the code to give a pop-up if more than 1 cell in that row (row x, columns H through J) has data. For example, in row 12, if only cell H has data, move on with the rest of the code. But if in row 12, cells H and J (or H & I, or I & J, or H & J) have data, the bring up the pop-up.

What's been happening is the inventory (done on a weekly basis) shows a number of items that are missing (have been used during that week). It also shows how may of that item are set to expire at a given date. Some of those items that have been used are also counted among the items set to expire. So when the code to fill in the order form looks for items missing and items expiring, it might count the same item twice causing a double order.

 

[JLGWhiz]

No, the code I posted finds the last row and then checks the cells in columns C and D to see if they equal zero. I am confused as to which row or rows you want to check to produce the message box. Here is code that will walk down your entire used range of data and check if there is more than one entry in cells H:J of that row.

Sub t()
Dim c As Range
    For Each c In Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        If Application.CountA(c.Offset, 7).Resize(, 3)) > 1 Then
            Beep
            MsgBox "Two quantities listed"
       End If
   Next
End Sub

 

[AcornNut]

Thanks, I tried this code, but I keep getting a "Compile error: Invalid or unqualified reference" pop-up. I will try to fix it and let you know.

As far as which rows to check... my code adds a new row of data each time a deficiency or expiration is found. So the rows will change. It loops row by row on the inventory form, and then copies and pastes the needed data onto the next blank row on the order form. The order form then looks for any duplicates items listed and if found, simply adds the new quantities to the existing quantities. Once it does that, I need it to check the columns in that given row it just added info in, so the rows will be different each time and not always the last row. I know, it's complicated and confusing. Sorry. But thanks for your patience and help. The "Application.CountA" seems to be what I'm looking for, but it's making it work that's the trick.

 

[MARK858]

Thanks, I tried this code, but I keep getting a "Compile error: Invalid or unqualified

Remove the period in front of .cells

For Each c In Range("A2", [COLOR="#FF0000"][B].[/B][/COLOR]Cells(Rows.Count, 1).End(xlUp))

 

[JLGWhiz]

Remove the period in front of .cells

For Each c In Range("A2", [COLOR=#ff0000][B].[/B][/COLOR]Cells(Rows.Count, 1).End(xlUp))

Don't know why I keep doing that. If I tested it before I posted, I would probably catch it. Tx MARK858.