VBA Code to count inventory

[mskli]

Hi:

I have a spreadsheet that brings in live Inventory data. Column A (sheet1) is the inventory item. Column B on Sheet1 is the age of inventory. The age of inventory is listed as NEW or OLD. I would like to count the number of items that are "OLD" using VBA code (no formulas in Excel). Once counted, I would like that number to be put in Sheet2 Cell B5. I would also like the time of this count to be posted in Sheet2 Cell C5. I would be adding this to existing VBA code that runs on a loop.

Can someone write some code for this?

thanks!

 

[JoeMo]

Here's a standalone subroutine you can adapt to your existing code.

Sub SumOLD()
Dim V As Variant, i As Long, S As Long
V = Sheets("Sheet1").Range("B1:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
    If V(i, 1) = "OLD" Then S = S + 1
Next i
Application.ScreenUpdating = False
With Sheets("Sheet2")
    .Range("B5").Value = S
    .Range("C5").Value = Now
    .Range("B:C").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[Marc L]

I would like to count the number of items that are "OLD" using VBA code (no formulas in Excel). Once counted, I would like that number to be put in Sheet2 Cell B5
I would also like the time of this count to be posted in Sheet2 Cell C5

Hi, according to Excel basics a single codeline is necessary :​

[Sheet2!B5:C5] = Array(Application.CountIf([Sheet1!A1].CurrentRegion.Columns(2), "OLD"), Now) …​

 

[mskli]

Here's a standalone subroutine you can adapt to your existing code.

Sub SumOLD()
Dim V As Variant, i As Long, S As Long
V = Sheets("Sheet1").Range("B1:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
    If V(i, 1) = "OLD" Then S = S + 1
Next i
Application.ScreenUpdating = False
With Sheets("Sheet2")
    .Range("B5").Value = S
    .Range("C5").Value = Now
    .Range("B:C").EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

This works well. Thanks!

I have one addition I need to make. The word is column B is often OLD with the number of days. So It would say OLD10days, OLD11days etc. How would I do this to search for just the first three letters = "OLD" in Column B

 

[JoeMo]

This works well. Thanks!

I have one addition I need to make. The word is column B is often OLD with the number of days. So It would say OLD10days, OLD11days etc. How would I do this to search for just the first three letters = "OLD" in Column B

You are welcome - thanks for the reply.
To address your new request try changing this line:

If V(i, 1) = "OLD" Then S = S + 1

to this:

If Left(V(i, 1), 3) = "OLD" Then S = S + 1

 

[mskli]

You are welcome - thanks for the reply.
To address your new request try changing this line:

If V(i, 1) = "OLD" Then S = S + 1

to this:

If Left(V(i, 1), 3) = "OLD" Then S = S + 1

thanks!