I have the following formula drawing the maximum value from the data in column F over multiple sheets.
我有以下公式从多列工作表中的F列数据中绘制最大值。
=MAX('Aug242018LB3STRDF$000'!F5:F39004,'Aug242018LB3STRDF$001'!F5:F39004,'Aug242018LB3STRDF$002'!F5:F39004,'Aug242018LB3STRDF$003'!F5:F39004,'Aug242018LB3STRDF$004'!F5:F39004,'Aug242018LB3STRDF$005'!F5:F39004,'Aug242018LB3STRDF$006'!F5:F39004,'Aug242018LB3STRDF$007'!F5:F39004,'Aug242018LB3STRDF$008'!F5:F39004,'Aug242018LB3STRDF$009'!F5:F39004,'Aug242018LB3STRDF$010'!F5:F39004,'Aug242018LB3STRDF$011'!F5:F39004,'Aug242018LB3STRDF$012'!F5:F39004,'Aug242018LB3STRDF$013'!F5:F39004,'Aug242018LB3STRDF$014'!F5:F39004,'Aug242018LB3STRDF$015'!F5:F39004,'Aug242018LB3STRDF$016'!F5:F39004,'Aug242018LB3STRDF$017'!F5:F39004,'Aug242018LB3STRDF$018'!F5:F39004,'Aug242018LB3STRDF$019'!F5:F39004,'Aug242018LB3STRDF$020'!F5:F39004,'Aug242018LB3STRDF$021'!F5:F39004,'Aug242018LB3STRDF$022'!F5:F39004,'Aug242018LB3STRDF$023'!F5:F39004,'Aug242018LB3STRDF$024'!F5:F39004,'Aug242018LB3STRDF$025'!F5:F39004,'Aug242018LB3STRDF$026'!F5:F39004,'Aug242018LB3STRDF$027'!F5:F39004,'Aug242018LB3STRDF$028'!F5:F39004,'Aug242018LB3STRDF$029'!F5:F39004)
It works really well, however it is not very adaptable. Often times I have a different # of sheets or the sheet name varies and so this forumla can only be used in one workbook. I was hoping there was a way in VBA to build a macro or application that would be able to detect the number of sheets, and possible be able to select my own range to compare (different columns)
它运作得很好,但它的适应性不强。通常我会有不同的工作表数或工作表名称不同,所以这个论坛只能在一个工作簿中使用。我希望在VBA中有一种方法可以构建一个能够检测工作表数量的宏或应用程序,并且可以选择我自己的范围进行比较(不同的列)
2 个解决方案
#1
1
Solved both issues of looping through all sheets and selecting the range/column. I created 2 functions (functions can be found below) one for the minimum and the other for the maximum. You only need to select the range as demonstrated in the picture below. Both function work even if you have blanks or text in the selected range across sheets, but if you have error they do not work.
解决了遍历所有工作表并选择范围/列的两个问题。我创建了2个函数(函数可以在下面找到),一个用于最小值,另一个用于最大值。您只需选择如下图所示的范围。即使您在选定范围内的工作表中有空白或文本,这两种功能都可以正常工作,但如果您有错误,则它们不起作用。
To be able to use these functions. You need to copy past the functions source code (found below) in a module. The functions will be available only in the workbook you copied the functions to.
能够使用这些功能。您需要复制模块中的函数源代码(如下所示)。这些功能仅在您复制功能的工作簿中可用。
If you want the functions to be available to any workbook you open. You need to save the workbook that contains the function as an add in, then activate the add in. This is a really simple step Click Here to see how to do the add in thing
如果您希望这些函数可用于您打开的任何工作簿。您需要将包含该功能的工作簿保存为添加,然后激活添加。这是一个非常简单的步骤点击此处查看如何添加内容
Note, if you type the functions say in "sheet1" Then you go to a different sheet say "sheet2" and change the numbers the functions will not automatically calculate. You need to go to the formula bar and press enter. If the number you change is in the same sheet as the sheet you typed the functions. It updates automatically
请注意,如果您在“sheet1”中键入函数,那么您将转到另一个表单“sheet2”并更改函数不会自动计算的数字。您需要转到公式栏并按Enter键。如果您更改的数字与您键入函数的工作表位于同一工作表中。它会自动更新
Minimum Across Sheets Function
Public Function Minimum_Across_Sheets(rngSelection As Range) As Double
Dim dMinimum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each rng In rngSelection
If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then
If Not bFirst_Value_Obtained Then
dMinimum_Value = wks.Cells(rng.Row, rng.Column)
bFirst_Value_Obtained = True
End If
If wks.Cells(rng.Row, rng.Column) < dMinimum_Value Then
dMinimum_Value = wks.Cells(rng.Row, rng.Column)
End If
End If
Next rng
Next wks
Minimum_Across_Sheets = dMinimum_Value
End Function
Maximum Across Sheets Function
Public Function Maximum_Across_Sheets(rngSelection As Range) As Double
Dim dMaximum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each rng In rngSelection
If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then
If Not bFirst_Value_Obtained Then
dMaximum_Value = wks.Cells(rng.Row, rng.Column)
bFirst_Value_Obtained = True
End If
If wks.Cells(rng.Row, rng.Column) > dMaximum_Value Then
dMaximum_Value = wks.Cells(rng.Row, rng.Column)
End If
End If
Next rng
Next wks
Maximum_Across_Sheets = dMaximum_Value
End Function
Hope this is useful to you.
希望这对你有用。
#2
4
You can create a 'first' worksheet at the beginning and hide it. Repeat the process with a 'last' worksheet at the end of the series. The formula becomes,
您可以在开头创建“第一个”工作表并将其隐藏。使用系列末尾的“最后”工作表重复此过程。公式变成,
=max(first:last!f5:f39004)
#1
1
Solved both issues of looping through all sheets and selecting the range/column. I created 2 functions (functions can be found below) one for the minimum and the other for the maximum. You only need to select the range as demonstrated in the picture below. Both function work even if you have blanks or text in the selected range across sheets, but if you have error they do not work.
解决了遍历所有工作表并选择范围/列的两个问题。我创建了2个函数(函数可以在下面找到),一个用于最小值,另一个用于最大值。您只需选择如下图所示的范围。即使您在选定范围内的工作表中有空白或文本,这两种功能都可以正常工作,但如果您有错误,则它们不起作用。
To be able to use these functions. You need to copy past the functions source code (found below) in a module. The functions will be available only in the workbook you copied the functions to.
能够使用这些功能。您需要复制模块中的函数源代码(如下所示)。这些功能仅在您复制功能的工作簿中可用。
If you want the functions to be available to any workbook you open. You need to save the workbook that contains the function as an add in, then activate the add in. This is a really simple step Click Here to see how to do the add in thing
如果您希望这些函数可用于您打开的任何工作簿。您需要将包含该功能的工作簿保存为添加,然后激活添加。这是一个非常简单的步骤点击此处查看如何添加内容
Note, if you type the functions say in "sheet1" Then you go to a different sheet say "sheet2" and change the numbers the functions will not automatically calculate. You need to go to the formula bar and press enter. If the number you change is in the same sheet as the sheet you typed the functions. It updates automatically
请注意,如果您在“sheet1”中键入函数,那么您将转到另一个表单“sheet2”并更改函数不会自动计算的数字。您需要转到公式栏并按Enter键。如果您更改的数字与您键入函数的工作表位于同一工作表中。它会自动更新
Minimum Across Sheets Function
Public Function Minimum_Across_Sheets(rngSelection As Range) As Double
Dim dMinimum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each rng In rngSelection
If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then
If Not bFirst_Value_Obtained Then
dMinimum_Value = wks.Cells(rng.Row, rng.Column)
bFirst_Value_Obtained = True
End If
If wks.Cells(rng.Row, rng.Column) < dMinimum_Value Then
dMinimum_Value = wks.Cells(rng.Row, rng.Column)
End If
End If
Next rng
Next wks
Minimum_Across_Sheets = dMinimum_Value
End Function
Maximum Across Sheets Function
Public Function Maximum_Across_Sheets(rngSelection As Range) As Double
Dim dMaximum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each rng In rngSelection
If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then
If Not bFirst_Value_Obtained Then
dMaximum_Value = wks.Cells(rng.Row, rng.Column)
bFirst_Value_Obtained = True
End If
If wks.Cells(rng.Row, rng.Column) > dMaximum_Value Then
dMaximum_Value = wks.Cells(rng.Row, rng.Column)
End If
End If
Next rng
Next wks
Maximum_Across_Sheets = dMaximum_Value
End Function
Hope this is useful to you.
希望这对你有用。
#2
4
You can create a 'first' worksheet at the beginning and hide it. Repeat the process with a 'last' worksheet at the end of the series. The formula becomes,
您可以在开头创建“第一个”工作表并将其隐藏。使用系列末尾的“最后”工作表重复此过程。公式变成,
=max(first:last!f5:f39004)