The Find function returns the maximum row of the array, suggesting lack of object-CodePudding

I think the cell G25 G30 to find out the maximum (Score column), and then the corresponding counterparts F listed the contents of the cell is assigned to another worksheet the Summary of the G8 cell, but lack of debugging said Object, why?

Arr=Worksheets (" Analysis "). The Range (" G25: G30)

For x=1 To UBound (arr)
M=Application. WorksheetFunction. Max (arr)

Set ro=Cells. The Find (I="m"). The Row the line where I want to Find a maximum

Sheets (" Summary "). The Range (" G8 ")=Sheets (" Analysis ".) cells (ro, 6), the value

Thanks for the teacher's guidance,

CodePudding user response:

Reference:

 Sub Test () 
"' VBA 
Dim Rng As Range, Rng1 As Range, iMax As Single 
The Set Rng1=Sheets (" Analysis "). The Range (" g25: g30) 
IMax=WorksheetFunction. Max (Rng1) 
For Each Rng Rng1 In 
If Rng. Value=https://bbs.csdn.net/topics/iMax Then 
Sheets (" Summary "). The Range (" g8 ")=Rng. Offset (1, 0) 
The Exit Sub 
End the If 
Next 
End Sub

CodePudding user response:

Thank you very much provides another way of thinking, there is another problem, the find () can be used in the array to find? After I change the code to the following operation is wrong, so I want to find function can only be used for cell, and can not be used in array to look for?

Arr=Worksheets (" Analysis "). The Range (" G25: G30)

For x=1 To UBound (arr)
M=Application. WorksheetFunction. Max (arr)

Ro=arr. Find (I="m"). The Row in the array to Find the maximum

And I'm a little confusion on the array dimension, for example, if I define arr=Worksheets (" Analysis "). The Range (" F25: G30) "' two columns array
If I want To traverse G25 To G30, it should use the For x=1 To UBound (arr. 2)?
If you want to find the maximum in G25 and G30 is should be m=Application WorksheetFunction. Max (arr. 2)?

Thank you very much for your correct,

CodePudding user response:

1, the find can only work in the lookup table or area, can't find inside for array;
2, direct use ubound (arr) or ubound (arr, 1), you can print out and see ubound (arr, 2)=? , UBound (arr, 1)=?
3, Application. WorksheetFunction. Max (arr, 2) only ever return to a maximum of the last column, you can do a experiment about Application. WorksheetFunction. Max (arr, 1)=?

Suggestion: cell area itself is a "visual array", can be used for each or for I... For j... Loop 2 ways, why don't want to get into array to operate?