VBA Challenge

[christoferlives]

If anyone could give me a hand with a cool piece of coding, it would be pretty solid.
What I need to do is create a program that sifts through the entry in a column, and finds the maximum value in a cell column.

I know It has to have a dynamic array, extract a value depending on the start and stop points.

I think it would be easier to give an example:
Here's the data set:

Column
A B

1332.96
1330.79
1322.78
1324.54
1324.87
1322.85 True
1311
1308.6
1307.61
1308.86 True
1287.17
1302.67
1301.29
1299.74 True
1291.26
1291.93
1291.21 True
1283.35
1294.6
1296.06
1293.24
1286.7
1286.87
1277.25 True


I need a code that:
Starts at the first row in column B and checks for the word "true."
Once it finds the first "true", begins array dynamic array at the first value in column A and continues until the next "true" value value in column B.

So, Start search in B1
Find "True" and starts array
Finds the next "True" value and ends the array
With the array parameters set, Find the Max of the array

Then, using the ending parameter in first array,
Start the beginning of the next array with the same value.

Then Loop the original steps.

 

[Peter_SSs]

Welcome to the MrExcel board!

First question is to confirm if you really need a macro. Would this direct worksheet formula suffice?

If you really need a macro, you might also tell us what should happen to the results. Are they to be put on the sheet somewhere (where?) or what?

Formula in C2 copied down. (You may need to adjust each $30 in my formula first if your data goes further down the sheet)

Excel Workbook
	A	B	C
1
2	1332.96
3	1330.79
4	1322.78
5	1324.54
6	1324.87
7	1322.85	TRUE	1322.85
8	1311
9	1308.6
10	1307.61
11	1308.86	TRUE	1308.86
12	1287.17
13	1302.67
14	1301.29
15	1299.74	TRUE	1299.74
16	1291.26
17	1291.93
18	1291.21	TRUE	1296.06
19	1283.35
20	1294.6
21	1296.06
22	1293.24
23	1286.7
24	1286.87
25	1277.25	TRUE
26
Local Max

 

[Peter_SSs]

Note also that as you have described it (or at least how I have interpreted your description) the 'TRUE' rows are included in two arrays. This may not be your intention - see this example and results. This issue will be relevant whether you use a macro or formula.

Excel Workbook
	A	B	C
6	1324.87
7	1322.85	TRUE	1322.85
8	1311
9	1308.6
10	1307.61
11	1308.86	TRUE	3000
12	1287.17
13	1302.67
14	1301.29
15	3000	TRUE	3000
16	1291.26
17	1291.93
18	1291.21	TRUE	1296.06
19	1283.35
Local Max

 

[christoferlives]

Hi Peter,

This is great. The situation behind this scenario is that the data is updated daily.

So, what I was looking for was something like a "signal". If the next day a "True" value appeared, I wanted the program to pick the value in the row below the "true" signal as the start of the array, and continue to the row above the next "True" signal.

So, in the origional context,
Lets say that today signaled a "true" value in column B.

I need something that pulls out the value: 1332.96 as the start of the max and that extends until the row above the next "true" value seen as: 1324.87. which completes the array. So the ending array formula shoud end up =max(A2:A6) <- Number of rows 5

As seen, as you progress through the sheet, the number of rows in the array change, so I need something that can fit into those parameters

So the first array:
=max(A2:A6) <- Number of rows 5
but the next array:
=max(A8:A10) <- Number of rows 3

Then listing the results to the true value that signalled the process to begin, aka, the 1st "true". In context, would be listed in the new cell value at C1.

Sorry if there was confusion.

 

[Peter_SSs]

That is still a little confusing to me. For the sample data you posted, can you tells us ..

1. Exactly what cells those values are in. (eg Are they in A2:B25 as I guessed?)

2. Exactly what result(s) you expect, and where.

 

[Rick Rothstein]

I think this macro may do what you want (assuming I have understood your exchange with Peter correctly)...

Sub FindMaximums()
  Dim X As Long, PrevRow As Long, Trues As Range
  Const DataStartRow As Long = 1
  Const DataColumn As String = "A"
  Const TrueColumn As String = "B"
  Const OutputColumn As String = "C"
  Columns(OutputColumn).Clear
  Set Trues = Columns(TrueColumn).SpecialCells(xlCellTypeConstants, xlLogical)
  PrevRow = DataStartRow
  For X = 1 To Trues.Areas.Count
    Cells(Trues.Areas(X).Row, OutputColumn).Value = WorksheetFunction.Max(Range(Cells(PrevRow, DataColumn), Trues.Areas(X)))
    PrevRow = Trues.Areas(X).Offset(1).Row
  Next
End Sub

The code assumes that your first piece of data is in Row 1, your data is in Column A, your TRUE values are in Column B and the maximum value is output to Column C; however, all for these can be changed by simply changing the assignments in the Const statements (VB keyword for constants) if necessary.