Find Large VBA

M

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
mahmed1 said:
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
Click to expand...
Is there anything in Columns A:C:E etc. between Rows 2:100? If so, what kind of values (it not, why did you merge A1:B1, C1:D1, etc.)?
 
Upvote 0
Hi

yes there is

sorry i meant from row the 3
in A2 i have expected and in B2 Actual copied across so i can get and expected value and actual value hence the reason i merged and im trying to get the largest actual value(s) found and for what month

i hope this makes sense

many thanks
 
Upvote 0
Hello Rick

was that info ok. I hope im not confusing things
 
Upvote 0
Rick Rothstein said:
I am working on it now... I'll be back when I get the code working correctly.
Click to expand...
Okay, I think I got it working... give this macro a try
Code: 
Sub ShowMonthsWithMaxActualAmounts()
  Dim R As Long, C As Long, Msg As String, vArr As Variant
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A2:AZ100),2)=0)*(A2:AZ100=MAX(A2:AZ100)),A2:AZ100,""""))")
  For C = 2 To 52 Step 2
    For R = 1 To 99
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C - 1)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub
 
Upvote 0
Hi Rick

Thank you but unfortunately this does not work as exoected because if there is a larger value in the Expected columns then it ignores the actual received values.

Ultimately i will be doing the same checks for both Expected and actual values.So list the months i am exected to receive the most amount and also expected amounts

The code works fine for the actual amounts if the actual amount has the max value overall and therefore if Expected amount is more it does not give the correct result

Thank You
 
Upvote 0
mahmed1 said:
Thank you but unfortunately this does not work as exoected because if there is a larger value in the Expected columns then it ignores the actual received values.
Click to expand...
Sorry, I had made a silly assumption in that code.


mahmed1 said:
Ultimately i will be doing the same checks for both Expected and actual values.
Click to expand...

Here are two macros, one for Expected values and one for Actual values, that should work correctely...
Code: 
Sub ShowMonthsWithMax[COLOR=#0000ff][B]Actual[/B][/COLOR]Amounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(A2:AZ100*(MOD(COLUMN(A2:AZ100),2)=0))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A2:AZ100),2)=0)*(A2:AZ100=" & Max & "),A2:AZ100,""""))")
  For C = 2 To 52 Step 2
    For R = 1 To 99
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C - 1)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

Sub ShowMonthsWithMax[COLOR=#0000ff][B]Expected[/B][/COLOR]Amounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(A2:AZ100*(MOD(COLUMN(A2:AZ100),2)=1))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A2:AZ100),2)=1)*(A2:AZ100=" & Max & "),A2:AZ100,""""))")
  For C = 1 To 51 Step 2
    For R = 1 To 99
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub
 
Upvote 0
Thank you

I get a type mismatch error on this line

Dim R As Long, C As Long, max As Double, Msg As String, vArr As Variant
max = Evaluate("MAX(A2:AZ100*(MOD(COLUMN(A2:AZ100),2)=0))")
 
Upvote 0
mahmed1 said:
Thank you

I get a type mismatch error on this line

Dim R As Long, C As Long, max As Double, Msg As String, vArr As Variant
max = Evaluate("MAX(A2:AZ100*(MOD(COLUMN(A2:AZ100),2)=0))")
Click to expand...
A type mismatch error means some of your cells are displaying text (probably "" from a formula). See if these modified macros now work for you...
Code: 
Sub ShowMonthsWithMaxActualAmounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(IF(ISNUMBER(A2:AZ100),A2:AZ100,0)*(MOD(COLUMN(A2:AZ100),2)=0))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A2:AZ100),2)=0)*(A2:AZ100=" & Max & "),A2:AZ100,""""))")
  For C = 2 To 52 Step 2
    For R = 1 To 99
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C - 1)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

Sub ShowMonthsWithMaxExpectedAmounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(IF(ISNUMBER(A2:AZ100),A2:AZ100,0)*(MOD(COLUMN(A2:AZ100),2)=1))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A2:AZ100),2)=1)*(A2:AZ100=" & Max & "),A2:AZ100,""""))")
  For C = 1 To 51 Step 2
    For R = 1 To 99
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
https://www.mrexcel.com/board/threads/vba-code-for-combinations-given-a-sum.753905/
Replies
1
Views
536
michaelsmith559
M
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
400
Legacy 352679
L
I
  • Solved
Advice to edit code so deleting by mistke doesnt happen
Replies
7
Views
429
ipbr21054
I
V
  • Solved
Running total Last 12 months per category (excluding Zeros)
Replies
5
Views
335
Fluff
Fluff
P
  • Locked
  • Question
Edit VBA code to let it automatically create Folder
Replies
5
Views
467
RoryA
RoryA

Forum statistics

Threads
1,214,983
Messages
6,122,595
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top