Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA find maximum value in range on specific sheet

Tags:

excel

vba

I found that the following code is getting max value in range:

Cells(Count, 4)=Application.WorksheetFunction.Max(Range(Cells(m, 1),Cells(n, 1)))

How can I search within a specific sheet? Data sheet in this case

Like:

Worksheets(d).Cells(x, 4).Value = Worksheets("Data")... ????? FIND MAX ????
like image 586
user829174 Avatar asked Aug 09 '15 16:08

user829174


People also ask

Is there a max function in VBA?

As the name suggests, Max is used to finding the maximum value from a given data set or array. Although it is a worksheet function, one may use it with the worksheet method as a worksheet function.

What is VBA Worksheetfunction?

Excel VBA Worksheet Functions. Worksheet function in VBA one may use when we have to refer to a specific worksheet. Normally, when we create a module, the code executes in the currently active sheet of the workbook. Still, if we want to execute the code in the specific worksheet, we use the Worksheet function.


1 Answers

This will often work, but is missing a reference:

 worksheets("Data").Cells(Count, 4)=  Application.WorksheetFunction.Max _
    ( worksheets("Data").range( cells(m,1) ,cells(n,1) )

The text "cells" should be preceded by a reference to which worksheet the cells are on, I would write this:

worksheets("Data").Cells(Count, 4) = Application.WorksheetFunction.Max _
    ( worksheets("Data").range( worksheets("Data").cells(m,1) ,worksheets("Data").cells(n,1) )

This can also be written like this which is clearer:

with worksheets("Data")
    .Cells(Count, 4) =  Application.WorksheetFunction.Max _
                            ( .range( .cells(m,1) ,.cells(n,1) )
End With 

I hope this helps.

Harvey

like image 65
HarveyFrench Avatar answered Sep 28 '22 00:09

HarveyFrench