Excel - VBA code to scan columns until finding negative value

Question

Hello,

I have two values in columns K (quantity on hand) and L (quantity on order), which added make for available quantity.

Then I have 24 columns, M thru AJ, which represent monthly demand buckets.

So I need to determine at which monthly column, my available quantity becomes negative.

For example, cell K2 = 10 + cell L2 = 10. Available quantity = 20.

Demand is M2=10, N2=5, P2=10, and so on. 

So the 20 available (K2 + L2), become negative at column P (added demand thru month P = 25 - 20 initially available = -5).

I need the header at P1 (month's name).

How may I do this in VBA code and, can I then reference the function in a each row cell?

I apologize for asking for the whole VBA code, but I'm not profficient enough to make it from my several google searches.

I actually couldn't find anything related to how to move through columns, but only thru rows.

I can do it by adding an additional row with the formulas directly in the Excel spreadsheet, but I have plenty of rows to go through.

Thank you for your time and help,

José Martínez

Answer 1

@José

So the 20 available (K2 + L2), become negative at column P (added demand thru month P = 25 - 20 initially available = -5)

Respectfully disagree. For me this becomes negative in column O:

Assuming you're OK …:

Range M1:AJ1 is named "HeaderMonths" with a sheet Scope

In a VBA Module

Option Explicit

Public Function WhenBelowZero(ByRef rngQty As Range, ByRef rngVal As Range) As String

'

    Dim iQty        As Integer

    Dim iTemp       As Integer

    Dim vArray      As Variant

    Dim i           As Integer

    Dim strMonth    As String

    Application.Volatile (False)

    iQty = rngQty.Value

    vArray = Application.Transpose(rngVal.Value)

    ' ASSUMPTIONS: Qty and all values in range rngVal are numeric

    ' otherwise the function will return a #VALUE! error

    For i = LBound(vArray) To UBound(vArray)

        iTemp = iTemp + vArray(i, 1)

        If iTemp > iQty Then Exit For

    Next i

    If i > UBound(vArray) Then strMonth = "N/A" _

    Else strMonth = ActiveSheet.Range("HeaderMonths").Columns(i).Value

    WhenBelowZero = strMonth

End Function

In I2: =WhenBelowZero(L2;M2:AJ2)

Sample available here

Answer 2

@José,

Glad I could help & thanks for posting back + well done re. Long vs Integer

Answer 3

Actually, I think I did figure it. I had to go back and declare the quantity variables as Long, instead of Integer, due to big quantities I'll be using.

Thank you again!

Answer 4

Hi @Lz.

Thank you so much for the prompt response.

You're totally correct: in my example, column O was the first negative. I skipped column O, for some reason, when I was typing the example.

The code worked well with all my rows, except for one, which is very odd, since I triple-checked and the cell range has the same format as the others. Can't figure out why it resulted in the #VALUE! error, but all others didn't.

Again, thank you very much for your time and help.

José Mtz.

Answer 5

To provide the most helpful answer, some more information is needed.

Would you be able to save a copy of the file without any sensitive data to OneDrive, Google Drive or Dropbox and post the link to the file as a reply.

Thanks.