VBA test for Max value

[mdipalma]

I am trying to find the max value of a group of seperate cells in VBA.

Here is my code;

Dim r As Integer
Dim c As Integer
r = 3
c = 3

While r <= iNameRows
While c <= iColCount
ActiveSheet.Cells(r, c).Select
If ActiveCell.Value = Application.WorksheetFunction.Max(ActiveCell.Value, ActiveCell.Offset(0, 3).Value, ActiveCell.Offset(0, 6).Value, ActiveCell.Offset(0, 9).Value, ActiveCell.Offset(0, 12).Value, ActiveCell.Offset(0, 15).Value, ActiveCell.Offset(0, 18).Value, ActiveCell.Offset(0, 21).Value) Then

With ActiveSheet
.Range(.Cells(r, c), .Cells(r, c + 2)).Select
End With
Selection.Font.Bold = True
End If
c = c + 3
Wend
r = r + 1
Wend

I'm getting a run-time error (1004) when it's trying to resolve the max value, I think because some of the tested cells are empty.

Would greatly appreciate help finding a workaround.

Thanks,

Mike D

 

[mdipalma]

Upon further review, I think I have other issues. Using the looping structure together with the offsets will only work when the current cell is in column C anyway.

I need a way to test columns C, F, I, L, O, R, U and X in each row, and select that cell and the two to the right of it.

I suppose that I could lose the column loop and just set a variable equal to the column property of the cell with the highest value, assuming that I can get the max function to resolve.

Trying that now...

-Mike D

 

[NateO]

Howdy Mike,

Try the following tweaks:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> TestArr()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range, myAr(1 <SPAN style="color:darkblue">To</SPAN> 8) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, z <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> Sheets(1).[a1:a24]
    <SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> 8
        <SPAN style="color:darkblue">If</SPAN> IsNumeric(Cells(cl.Row, i * 3).Value) <SPAN style="color:darkblue">Then</SPAN> myAr(i) = _
            Cells(cl.Row, i * 3).Value
    <SPAN style="color:darkblue">Next</SPAN>
    <SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> 8
        <SPAN style="color:darkblue">If</SPAN> myAr(i) = Application.Max(myAr) <SPAN style="color:darkblue">Then</SPAN>
            z = i * 3: <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">For</SPAN>
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN>
    Sheets(1).Cells(cl.Row, z).Resize(, 3).Font.Bold = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Hope this helps.

 

[mdipalma]

Nate,

That's very helpful, thank you. This gets me almost there, but I do have a couple questions;

- In the "[a1:a24]" portion, I don't understand the significance of the [] brackets. I realize that this is probably a silly question, but I can't find reference to them, and I'd like to understand why they're necessary.
- I changed "[a1:a343]" in order to get it to run through all of the rows in my test sheet. I experimented with different ways to dynamically define the lower boundary of the sheet, as it will change often, but I had trouble getting anything to work. This is probably related to the above question.
- On occasion, the routine will pass a row and not bold anything, even though there's data there. I'm continuing to experiment with this, but should I be setting the array to nothing in between rows?

Thanks again for your help.

-Mike D

 

[NateO]

Howdy Mike,

Nate, That's very helpful, thank you.

You're welcome.

- In the "[a1:a24]" portion, I don't understand the significance of the [] brackets. I realize that this is probably a silly question, but I can't find reference to them, and I'd like to understand why they're necessary.

Here's the significance. They're not necessary, I'm simply looping through a range, you can use variable ranges in the range loop.

- I changed "[a1:a343]" in order to get it to run through all of the rows in my test sheet. I experimented with different ways to dynamically define the lower boundary of the sheet, as it will change often, but I had trouble getting anything to work. This is probably related to the above question.

How did you change it? Yes, it is. This won't be dynamic, it's a hard-coded method, use range to work with variable ranges.

- On occasion, the routine will pass a row and not bold anything, even though there's data there. I'm continuing to experiment with this, but should I be setting the array to nothing in between rows?

No, the array is a vb information type, not an object, erasing it isn't the problem. It has to do with your implimentation, skipping rows doesn't sound right, at all... If you can correct the above and this still has the behaviour you describe, I'll have a look: Nate@Blah1Blah1TheOfficeExperts.com

Pull the Blah1Blah1 portion.

Thanks again for your help.

No Problemo, this should fire, flip an end method into the loop range, etc.... Could be good.

 

[mdipalma]

Nate,

Bingo. It works great.

Thanks for the links in regard to the use of []. They were very enlightening. I think I have a fundamental understanding of the nut-and-bolts of arrays, but don't always recognize when a situation suggests that I should apply them. Still learning...

'I changed "[a1:a343]"...'

"How did you change it? ... use range to work with variable ranges"

This should have read "I changed [a1:a24] to [a3:a343]..." and, as such, it did work. After reading the links you provided, I changed it to "Range("A3:A" & iRowIndex)" (My data begins in row 3, and the row index is passed when this call is issued.) and it seems to be working perfectly.

"On occasion, the routine will pass a row and not bold anything..."

"No, the array is a vb information type, not an object, erasing it isn't the problem. It has to do with your implimentation, skipping rows doesn't sound right, at all..."

I was having this symptom when I ran the code with [a3:a343]. I set watches on (Cells(cl.Row, i * 3).Value), myAr(i) and Application.Max(myAr) and stepped through the code;

- On row 29, the 8 cells that are tested contain; 99618, 6796, 217, 250, 380, "", 216 and "". As the code runs through this row, it loads the array, identifies myAr(1) as being the max value and dutifully bolds that cell and the two to it's immediate right. So far, so good...
- On row 30, the 8 cells that are being tested contain; 5326, "", "", "", "", "", "" and "". As I stepped through the code, I noticed that it loads 5326 into myAr(1), but since none of the other values are numeric, it doesn't execute "myAr(i) = Cells(cl.Row, i * 3).Value" for i = 2 to 8. Then, when it goes to determine the max value of myAr, is finds that myAr(2) still contains 6796 from row 29, and determines that columns 6-8 need to be bolded. (So, it wasn't really skipping rows when it was bolding data, it was bolding cells without any data in them, and I didn't notice at first.)

For the time being, I have resolved this issue by adding "Erase myAr" just before the outermost For structure loops back to evaluate the next row, so that values are carried from one row to the next. If there is a better way to resolve this, please let me know.

I truly thank you for your help. It is greatly appreciated.

Sorry I let this get so long...

-Mike D

 

[NateO]

Hello again Mike,

- On row 29, the 8 cells that are tested contain; 99618, 6796, 217, 250, 380, "", 216 and "". As the code runs through this row, it loads the array, identifies myAr(1) as being the max value and dutifully bolds that cell and the two to it's immediate right. So far, so good...
- On row 30, the 8 cells that are being tested contain; 5326, "", "", "", "", "", "" and "". As I stepped through the code, I noticed that it loads 5326 into myAr(1), but since none of the other values are numeric, it doesn't execute "myAr(i) = Cells(cl.Row, i * 3).Value" for i = 2 to 8. Then, when it goes to determine the max value of myAr, is finds that myAr(2) still contains 6796 from row 29, and determines that columns 6-8 need to be bolded. (So, it wasn't really skipping rows when it was bolding data, it was bolding cells without any data in them, and I didn't notice at first.)

For the time being, I have resolved this issue by adding "Erase myAr" just before the outermost For structure loops back to evaluate the next row, so that values are carried from one row to the next. If there is a better way to resolve this, please let me know.

Yeah, I just thought about that this morning, and it should've clicked when you mentioned it bonking out on nulls... This is the best way to handle this with the fixed dimensions. Originally, I had scaled this via redim, and erasing the array with this will kill your dimensions and you'd need to continually re dimension your array, which is expensive/why I didn't do this in my original response (continually changing code, all the way to the board :D ). Good catch and I apologize for not clicking on this immediately.

You're welcome. Have a good one.