Extend VBA code to cover a multi column range

[bobhc]

Good day All


I have a piece of code to find the maximum value in a column, and I would like to extend it to cover a arrange of A:G.


Sub ExitForDemo()

Dim MaxVal As Double

Dim Row As Long

MaxVal = Application.WorksheetFunction. _

Max(Range("A:A"))

For Row = 1 To Rows.Count

If Range("A1").Offset(Row - 1, 0).Value = MaxVal Then

Range("A1").Offset(Row - 1, 0).Activate

MsgBox "Max value is in Row " & Row

Exit For

End If

Next Row

End Sub

 

[NARAYANK991]

Hi Bob ,


Try this :

[pre]

Sub ExitForDemo()
Dim MaxVal As Double
Dim Rw As Long, cl As Long
Dim Data_Range As Range

Set Data_Range = ActiveSheet.Range("H5:O20")            ' Change this as required
MaxVal = Application.WorksheetFunction.Max(Data_Range)
For Rw = 1 To Data_Range.Rows.Count
For cl = 1 To Data_Range.Columns.Count
If Range("H5").Offset(Rw - 1, cl - 1).Value = MaxVal Then
Range("H5").Offset(Rw - 1, cl - 1).Activate
MsgBox "Max value is in Row " & Rw+4 & " Column " & cl+7
Exit For
End If
Next cl
Next Rw
End Sub

[/pre]
The +4 is because the data starts on row 5 ( which is 4 more than 1 ) , and column H ( which is column 8 , 7 more than 1 ). These offsets will need to be changed if your data starts elsewhere in the sheet.


Narayan

 

[Hui]

Bobhc

[pre]

Sub ExitForDemo()
Dim MaxVal As Double
Dim cell As Range

MaxVal = Application.WorksheetFunction.Max(Range("A:G"))

For Each cell In Range("A1").CurrentRegion
If cell.Value = MaxVal Then
MsgBox "Max value is in Row " & cell.Row & " Column " & cell.Column
End If
Next
End Sub

[/pre]

 

[bobhc]

NARAYANK991

My thanks for your help, I have used your code, slight alterations re ranges, to make two more macros.

........BUT I have a problem in that all will run from the run macro dialogue, but only the two range buttons work, the min and max buttons throw up an error about macros? When I try and save the work book I get the same error message, I have to close the work book by X

XLSM when saved so macros are ENABLED.

I have uploaded the workbook


https://dl.dropbox.com/u/75495784/max%20in%20range.xlsm

 

[bobhc]

I have sorted out the buttons, do not know why or how but they had lost there link to a macro.....But still getting the privacy warning error message which is annoying


Update, Googled the warning message and got rid of by disabling Document Inspector.

 

[NARAYANK991]

Hi Bob ,


Good for you , I was about to post the same.


Narayan

 

[bobhc]

Good day NARAYANK991


I have uploaded the update to previous post link. All seem to do as I hoped it would

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Good afternoon, old dog.


I took the liberty of downloading your uploaded file and making some minor changes. Here's the link:

https://dl.dropbox.com/u/60558749/Extend%20VBA%20code%20to%20cover%20a%20multi%20column%20range%20-%20max%20in%20range%20%28for%20bobhc%20at%20chandoo.org%29.xlsm


Briefly I did this:

- included Option Explicit as first line en each module or sheet page

- indented all code to the standard 4 space (do you know you can use Tab key for indenting code? settings in Alf-F11, Tools, Options, Editor... check if Require Variable Declarations is on too)

- changed all procedures to display multiple occurrences of searched values (the con is that only last occurrence got selected)


Hope it helps.


Regards!

 

[bobhc]

Good Evening old Friend


I welcome your proof reading at any time, I thought I had done the first three but I will check and double check.

Your last improvement is something that had crossed my mind and I did do a half-harted search to try and find out about multiple instances but I will be honest and say that what I say told me to learn more VBA first. I will digest and compare the two different codes to learn.My thanks

 

[SirJB7]

Hi, b(ut)ob(ut)hc!

Don't even mention it...

Regards!

PS: Just say Carlsberg! (I wouldn't care if you add "Export" or "Special Brew")

 

[bobhc]

Good evening SirJB7


I have studied you spread sheet and I think I understand how you get the multi listings displayed on your buttons.........But there is some thing I do not know and it happens in yours and my spreadsheet, if you click on of the buttons there is the waiting icon which does not go away until you re-click the button, and when the active cell is at the bottom of the range you can not scroll to find the other cells that are listed on your button with out re-clicking and loosing the displayed listings

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Good afternoon, old dog.


If when you say "re-click" the button you mean to click on the only button of the displayed message box, then it's right that on the sheet, specifically over the button clicked first, remains the waiting icon, since code execution is stopped at the MsgBox statement waiting for user input. Same explanation for unavailability to scroll. Let us see why.


MsgBox function has this syntax (available pressing F1 on the word from the VB editor):

MsgBox(prompt[, buttons][, title][, helpfile, context])


But better, download my uploaded file again from same link and give a look inside.


Regards!


If you mean anything else, I'm afraid I don't understand your English. Tried with British English?

 

[bobhc]

SirJB7

Perfect for more study time, again my thanks