Find Large VBA

[mahmed1]

Hello

i have some month names across A:E
January (merged a1:b1) February (c1:d1) etc

now i need a code that will look in b:d:f etc row 2 to 100 and see which is the largest value found and then give the month of the largest value found and if there is a tie then give me that month also

this should be displayed in a msgbox with the value

If i had 60 as the largest value found for feb and may then
msgbox (Largest amount (value) to be given in feb and may)

 

[mahmed1]

Hi Rick,

Ignore my previous thread comments although that explains a bit on how I input the data manually.

I have had a nap and I now I am thinking straight and thought it would A LOT LOT better to give the max amount I will receive altogether and for which months instead of which month has the most value.

I.e…I could have in Expected for Jan 500 but in Feb Expected I could have 200 and 400 so in reality I am going to be receiving more money in February,

So I could really have a Sum Formula in A101 (Summing A3 to A100) copied across and then use the
Max value formula for that row. A101 AZ101 and store that in vArr and then use the given codes as normal for that range.

That would be more meaningful to my boss.

I have also in another sheet created the same format for the dates A1 across
Below that Expected/Actual and A2/B2 across
In row 3 I just have a formula that adds up all the for that month

=North!A101+East!A101+West!101 Copied across

So now given on the change above, I guess I will be ok to use the code you gave for all sheets and also as I am creating a Summary sheet with the total values, I guess il be able to use the code you gave on that sheet also

I would like 2 other things

I have the:

Most expected amount
Most actual amount

I would like 2 more info to say from today’s Date/Month;

When was the last Actual payment received (What month) and when is the next expected payment month due.

I suspect it may be another Formula check for the both macros you gave and then incorporating that in the loop but you will know best

I appreciate all your help and you have been nothing but Amazing. Your help is really appreciated.

Thank You

 

[mahmed1]

Hi Rick,

Ignore my previous thread comments although that explains a bit on how I input the data manually.

I have had a nap and I now I am thinking straight and thought it would A LOT LOT better to give the max amount I will receive altogether and for which months instead of which month has the most value.

I.e…I could have in Expected for Jan 500 but in Feb Expected I could have 200 and 400 so in reality I am going to be receiving more money in February,

So I could really have a Sum Formula in A101 (Summing A3 to A100) copied across and then use the
Max value formula for that row. A101 AZ101 and store that in vArr and then use the given codes as normal for that range.

That would be more meaningful to my boss.

I have also in another sheet created the same format for the dates A1 across
Below that Expected/Actual and A2/B2 across
In row 3 I just have a formula that adds up all the for that month

=North!A101+East!A101+West!101 Copied across

So now given on the change above, I guess I will be ok to use the code you gave for all sheets and also as I am creating a Summary sheet with the total values, I guess il be able to use the code you gave on that sheet also

I would like 2 other things

I have the:

Most expected amount
Most actual amount

I would like 2 more info to say from today’s Date/Month;

When was the last Actual payment received (What month) and when is the next expected payment month due.

I suspect it may be another Formula check for the both macros you gave and then incorporating that in the loop but you will know best

I appreciate all your help and you have been nothing but Amazing. Your help is really appreciated.

Thank You

Hi Rick, i hope i have explained myself more clearly now

I do apologise for not explaining properly

Thank You

 

[Rick Rothstein]

Hi Rick, i hope i have explained myself more clearly now

If I understand you correctly, you created a new sheet, laid out the same as the other three sheets, where you sum up each same cell across the three original sheets. What is the name of that new sheet? Also, when there is no data in a given cell address on the three original sheets, what is displayed in the equivalent cell on the total sheet... a zero or is the cell blank ("")?

 

[mahmed1]

Hi Rick

Before i explain i want to thank you for your patience and all your help. Appreciated Sir

Yes i have done exactly what you have said above

The cells display 0 If the cells are blank so i il have to put a formula to display blank maybe something like this copied down and across unless you can suggest a better solution Sir

=IF(AND(North!A3="",East!A3="",West!A3=""),"",North!A3+East!A3+West!A3)


I have named the New Sheet CombinedData

P.s Do you think based on the changes i am making, that would be more useful for management information> trying to get a view from an expert

and

I would like 2 other things

I have the:

Most expected amount
Most actual amount

I would like 2 more info to say from today’s Date/Month;

When was the last Actual payment received (What month) and when is the next expected payment month due.

I suspect it may be another Formula check for the both macros you gave and then incorporating that in the loop but you will know best

 

[Rick Rothstein]

Give these two functions a try...

Function LastActualDate() As Variant
  Dim X As Long
  With Sheets("CombinedData")
    For X = 52 To 1 Step -2
      If Application.Sum(.Range(.Cells(3, X), .Cells(100, X))) > 0 Then
        LastActualDate = .Cells(1, X)
        Exit Function
      End If
    Next
  End With
End Function

Function LastExpectedDate() As Variant
  Dim X As Long
  With Sheets("CombinedData")
    For X = 51 To 1 Step -2
      If Application.Sum(.Range(.Cells(3, X), .Cells(100, X))) > 0 Then
        LastExpectedDate = .Cells(1, X)
        Exit Function
      End If
    Next
  End With
End Function

 

[mahmed1]

Thank you

il give this a try when i get home

Am i right in saying these to macros will give me the next expected payment date and the last payment received date?

and do i call this function via code or through the worksheet as function formula?

i suppose i can use these for combineddata and all my other 3 regions so i can get a combined breakdown aswel as individual breakdown. Would this mean i change the worksheet to a for each loop to go through combined and all the 3 other sheets?

 

[Rick Rothstein]

Am i right in saying these two macros will give me the next expected payment date and the last payment received date?

These functions will return the date (whatever is in Row 1) for the last appropriate column having a value greater than 0 in it somewhere.

and do i call this function via code or through the worksheet as function formula?

You can do either, but if you call it from within code, do not do so in a loop... they only need to be called once as they do their own looping internally.

i suppose i can use these for combineddata and all my other 3 regions so i can get a combined breakdown aswel as individual breakdown.

No, I did not think you said that you wanted it for the individual sheets... I thought you said you wanted it for the CombinedData data sheet only. Here are the functions modified to allow you to pass the sheet name in as an argument...

Function LastActualDate(SheetName As String) As Variant
  Dim X As Long
  With Sheets(SheetName)
    For X = 52 To 1 Step -2
      If Application.Sum(.Range(.Cells(3, X), .Cells(100, X))) > 0 Then
        LastActualDate = .Cells(1, X)
        Exit Function
      End If
    Next
  End With
End Function

Function LastExpectedDate(SheetName As String) As Variant
  Dim X As Long
  With Sheets(SheetName)
    For X = 51 To 1 Step -2
      If Application.Sum(.Range(.Cells(3, X), .Cells(100, X))) > 0 Then
        LastExpectedDate = .Cells(1, X)
        Exit Function
      End If
    Next
  End With
End Function

 

[mahmed1]

Thank you

i will give this a go

by your second point do you meane dont loop again as when the worksheet is passed as an argument, it will do the looping on that sheet?

you have been awesome

 

[Rick Rothstein]

by your second point do you meane dont loop again as when the worksheet is passed as an argument, it will do the looping on that sheet?

Correct... just call the function with whatever sheet you want to process as the argument and it will return the date you wanted.

 

[mahmed1]

Hi Rick

The expected part does not give the right results as expected

Say i run the code today and i wanted to get the next expected payment. If i had Person 1 to give me 200pounds in November and Person 2 expected to give me money in December then the function returns December. I would expect the result to be November as im expecting a payment in November

The actual amount CODE works fine