'Range' of object '_Global' failed ---runtime error 1004

xiahuagreen

New Member
Joined
Oct 19, 2019
Messages
5
Hello
below is my code, it works only 1/3 of the upper screen as expected, error msg comes out anywhere else. please help to identify the root cause. Thanks so much.

Sub FindMax()
'
Dim zoomCell As Range, zoomRng As Range, maxCell As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
MsgBox "You must select a range before pressing button - Try again"
End
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"

For Each zoomCell In Range(zoomRng.Address)
zoomCell.Cells.Interior.ColorIndex = 6 'Yellow highlight selected cells.
Next zoomCell

maxCell = WorksheetFunction.Max(Range(zoomRng.Address)) 'obtain address for each cell


With ActiveSheet.Range(zoomRng.Address)
Set c = .Find(maxCell, LookIn:=xlValues) 'find the max value cell
If Not c Is Nothing Then
maxaddress = c.Address 'Find max value in select cells.
End If
End With

ActiveWindow.Zoom = True

Range(maxaddress).Cells.Interior.ColorIndex = 22 'Red highlight for Max value. <<<<<<<<<<<<< This line is in error when debug

MsgBox "Max value = " & maxCell & " at cell " & maxaddress

ActiveWindow.Zoom = 70

maxaddress = Clear
maxCell = Clear
zoomCell = Clear
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,573
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
Sub FindMax()
'
Dim MaxCell As Range, zoomRng As Range, maxvalue As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
    MsgBox "You must select a range before pressing button - Try again"
    Exit Sub
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"


zoomRng.Interior.ColorIndex = 6 'Yellow highlight selected cells.

maxvalue = WorksheetFunction.Max(zoomRng) 'obtain address for each cell


Set MaxCell = zoomRng.Find(maxvalue, LookIn:=xlValues) 'find the max value cell
If Not MaxCell Is Nothing Then
    ActiveWindow.Zoom = True
    MaxCell.Interior.ColorIndex = 22

    MsgBox "Max value = " & maxvalue & " at cell " & MaxCell.Address

    ActiveWindow.Zoom = 70
Else
    MsgBox "No Max value found"
End If
End Sub
 

xiahuagreen

New Member
Joined
Oct 19, 2019
Messages
5
Hi & welcome to MrExcel.
How about
Code:
Sub FindMax()
'
Dim MaxCell As Range, zoomRng As Range, maxvalue As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
    MsgBox "You must select a range before pressing button - Try again"
    Exit Sub
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"


zoomRng.Interior.ColorIndex = 6 'Yellow highlight selected cells.

maxvalue = WorksheetFunction.Max(zoomRng) 'obtain address for each cell


Set MaxCell = zoomRng.Find(maxvalue, LookIn:=xlValues) 'find the max value cell
If Not MaxCell Is Nothing Then
    ActiveWindow.Zoom = True
    MaxCell.Interior.ColorIndex = 22

    MsgBox "Max value = " & maxvalue & " at cell " & MaxCell.Address

    ActiveWindow.Zoom = 70
Else
    MsgBox "No Max value found"
End If
End Sub
Hi thanks for the help. I applied your routine and this time the error msg: Compile error: Ambiguous name detected: FindMax
am I getting into more trouble this time?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,573
Office Version
365
Platform
Windows
You need to delete your original macro, or change the name of one of them.
 

xiahuagreen

New Member
Joined
Oct 19, 2019
Messages
5
Hi thanks for the help. I applied your routine and this time the error msg: Compile error: Ambiguous name detected: FindMax
am I getting into more trouble this time?
Hi, I tried this set of code and it only gave me the message "no max value found"
I am using the below set of data, hope it's not occupying too much space here...
1213.1414.1615.0615.8416.517.0417.4617.7617.941817.9417.7617.4617.0416.515.8415.0614.1613.1412
12.444913.594914.624915.534916.324916.994917.544917.974918.284918.474918.544918.494918.324918.034917.624917.094916.444915.674914.784913.774912.6449
12.998414.158415.198416.118416.918417.598418.158418.598418.918419.118419.198419.158418.998418.718418.318417.798417.158416.398415.518414.518413.3984
13.686914.856915.906916.836917.646918.336918.906919.356919.686919.896919.986919.956919.806919.536919.146918.636918.006917.256916.386915.396914.2869
14.534415.714416.774417.714418.534419.234419.814420.274420.614420.834420.934420.914420.774420.514420.134419.634419.014418.274417.414416.434415.3344
15.562516.752517.822518.772519.602520.312520.902521.372521.722521.952522.062522.052521.922521.672521.302520.812520.202519.472518.622517.652516.5625
16.790417.990419.070420.030420.870421.590422.190422.670423.030423.270423.390423.390423.270423.030422.670422.190421.590420.870420.030419.070417.9904
18.234919.444920.534921.504922.354923.084923.694924.184924.554924.804924.934924.944924.834924.604924.254923.784923.194922.484921.654920.704919.6349
19.910421.130422.230423.210424.070424.810425.430425.930426.310426.570426.710426.730426.630426.410426.070425.610425.030424.330423.510422.570421.5104
21.828923.058924.168925.158926.028926.778927.408927.918928.308928.578928.728928.758928.668928.458928.128927.678927.108926.418925.608924.678923.6289
2425.2426.3627.3628.242929.6430.1630.5630.843131.0430.9630.7630.443029.4428.7627.9627.0426
26.430927.680928.810929.820930.710931.480932.130932.660933.070933.360933.530933.580933.510933.320933.010932.580932.030931.360930.570929.660928.6309
29.126430.386431.526432.546433.446434.226434.886435.426435.846436.146436.326436.386436.326436.146435.846435.426434.886434.226433.446432.546431.5264
32.088933.358934.508935.538936.448937.238937.908938.458938.888939.198939.388939.458939.408939.238938.948938.538938.008937.358936.588935.698934.6889
35.318436.598437.758438.798439.718440.518441.198441.758442.198442.518442.718442.798442.758442.598442.318441.918441.398440.758439.998439.118438.1184
38.812540.102541.272542.322543.252544.062544.752545.322545.772546.102546.312546.402546.372546.222545.952545.562545.052544.422543.672542.802541.8125
42.566443.866445.046446.106447.046447.866448.566449.146449.606449.946450.166450.266450.246450.106449.846449.466448.966448.346447.606446.746445.7664
46.572947.882949.072950.142951.092951.922952.632953.222953.692954.042954.272954.382954.372954.242953.992953.622953.132952.522951.792950.942949.9729
50.822452.142453.342454.422455.382456.222456.942457.542458.022458.382458.622458.742458.742458.622458.382458.022457.542456.942456.222455.382454.4224
55.302956.632957.842958.932959.902960.752961.482962.092962.582962.952963.202963.332963.342963.232963.002962.652962.182961.592960.882960.052959.1029
6061.3462.5663.6664.6465.566.2466.8667.3667.746868.1468.1668.0667.8467.567.0466.4665.7664.9464
64.896966.246967.476968.586969.576970.446971.196971.826972.336972.726972.996973.146973.176973.086972.876972.546972.096971.526970.836970.026969.0969
69.974471.334472.574473.694474.694475.574476.334476.974477.494477.894478.174478.334478.374478.294478.094477.774477.334476.774476.094475.294474.3744
75.210976.580977.830978.960979.970980.860981.630982.280982.810983.220983.510983.680983.730983.660983.470983.160982.730982.180981.510980.720979.8109
80.582481.962483.222484.362485.382486.282487.062487.722488.262488.682488.982489.162489.222489.162488.982488.682488.262487.722487.062486.282485.3824
86.062587.452588.722589.872590.902591.812592.602593.272593.822594.252594.562594.752594.822594.772594.602594.312593.902593.372592.722591.952591.0625
91.622493.022494.302495.462496.502497.422498.222498.902499.462499.9024100.2224100.4224100.5024100.4624100.3024100.022499.622499.102498.462497.702496.8224
97.230998.640999.9309101.1009102.1509103.0809103.8909104.5809105.1509105.6009105.9309106.1409106.2309106.2009106.0509105.7809105.3909104.8809104.2509103.5009102.6309
102.8544104.2744105.5744106.7544107.8144108.7544109.5744110.2744110.8544111.3144111.6544111.8744111.9744111.9544111.8144111.5544111.1744110.6744110.0544109.3144108.4544
108.4569109.8869111.1969112.3869113.4569114.4069115.2369115.9469116.5369117.0069117.3569117.5869117.6969117.6869117.5569117.3069116.9369116.4469115.8369115.1069114.2569
114115.44116.76117.96119.04120120.84121.56122.16122.64123123.24123.36123.36123.24123122.64122.16121.56120.84120
119.4429120.8929122.2229123.4329124.5229125.4929126.3429127.0729127.6829128.1729128.5429128.7929128.9229128.9329128.8229128.5929128.2429127.7729127.1829126.4729125.6429
124.7424126.2024127.5424128.7624129.8624130.8424131.7024132.4424133.0624133.5624133.9424134.2024134.3424134.3624134.2624134.0424133.7024133.2424132.6624131.9624131.1424
129.8529131.3229132.6729133.9029135.0129136.0029136.8729137.6229138.2529138.7629139.1529139.4229139.5729139.6029139.5129139.3029138.9729138.5229137.9529137.2629136.4529
134.7264136.2064137.5664138.8064139.9264140.9264141.8064142.5664143.2064143.7264144.1264144.4064144.5664144.6064144.5264144.3264144.0064143.5664143.0064142.3264141.5264
139.3125140.8025142.1725143.4225144.5525145.5625146.4525147.2225147.8725148.4025148.8125149.1025149.2725149.3225149.2525149.0625148.7525148.3225147.7725147.1025146.3125
143.5584145.0584146.4384147.6984148.8384149.8584150.7584151.5384152.1984152.7384153.1584153.4584153.6384153.6984153.6384153.4584153.1584152.7384152.1984151.5384150.7584
147.4089148.9189150.3089151.5789152.7289153.7589154.6689155.4589156.1289156.6789157.1089157.4189157.6089157.6789157.6289157.4589157.1689156.7589156.2289155.5789154.8089
150.8064152.3264153.7264155.0064156.1664157.2064158.1264158.9264159.6064160.1664160.6064160.9264161.1264161.2064161.1664161.0064160.7264160.3264159.8064159.1664158.4064
153.6909155.2209156.6309157.9209159.0909160.1409161.0709161.8809162.5709163.1409163.5909163.9209164.1309164.2209164.1909164.0409163.7709163.3809162.8709162.2409161.4909
156157.54158.96160.26161.44162.5163.44164.26164.96165.54166166.34166.56166.66166.64166.5166.24165.86165.36164.74164
157.6689159.2189160.6489161.9589163.1489164.2189165.1689165.9989166.7089167.2989167.7689168.1189168.3489168.4589168.4489168.3189168.0689167.6989167.2089166.5989165.8689
158.6304160.1904161.6304162.9504164.1504165.2304166.1904167.0304167.7504168.3504168.8304169.1904169.4304169.5504169.5504169.4304169.1904168.8304168.3504167.7504167.0304
158.8149160.3849161.8349163.1649164.3749165.4649166.4349167.2849168.0149168.6249169.1149169.4849169.7349169.8649169.8749169.7649169.5349169.1849168.7149168.1249167.4149
158.1504159.7304161.1904162.5304163.7504164.8504165.8304166.6904167.4304168.0504168.5504168.9304169.1904169.3304169.3504169.2504169.0304168.6904168.2304167.6504166.9504
156.5625158.1525159.6225160.9725162.2025163.3125164.3025165.1725165.9225166.5525167.0625167.4525167.7225167.8725167.9025167.8125167.6025167.2725166.8225166.2525165.5625
153.9744155.5744157.0544158.4144159.6544160.7744161.7744162.6544163.4144164.0544164.5744164.9744165.2544165.4144165.4544165.3744165.1744164.8544164.4144163.8544163.1744
150.3069151.9169153.4069154.7769156.0269157.1569158.1669159.0569159.8269160.4769161.0069161.4169161.7069161.8769161.9269161.8569161.6669161.3569160.9269160.3769159.7069
145.4784147.0984148.5984149.9784151.2384152.3784153.3984154.2984155.0784155.7384156.2784156.6984156.9984157.1784157.2384157.1784156.9984156.6984156.2784155.7384155.0784
139.4049141.0349142.5449143.9349145.2049146.3549147.3849148.2949149.0849149.7549150.3049150.7349151.0449151.2349151.3049151.2549151.0849150.7949150.3849149.8549149.2049
132133.64135.16136.56137.84139140.04140.96141.76142.44143143.44143.76143.96144.04144143.84143.56143.16142.64142

<colgroup><col width="62" span="21" style="width:47pt"> </colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,573
Office Version
365
Platform
Windows
If I select the part with borders I get 168.4589 in N42 which is correct for where I have the data.
Check that your values are numbers & not text.
 

xiahuagreen

New Member
Joined
Oct 19, 2019
Messages
5
Hi thanks for the response. the thing that amazes me is everything works down to line row 21 or 22, as expected (the requirement is to select a zoom area and find the max value and color that cell with different color). once the zoom extends pass the row 21, 22, the program fails. is there a magical line that excel spread sheet cannot pass?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,732
Fluff's code works fine for me using the data you posted, provided that I change the Find Method line to this:

Code:
Set MaxCell = zoomRng.Find(169.875, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows)
The reason for using xlFormulas instead of xlValues is that if you run the macro multiple times with xlValues, after the first time it doesn't find the maxvalue (MaxCell is Nothing) for me, apparently b/c the first run causes the data to re-format to fewer decimal places. Using xlFormulas finds the correct value repeatably b/c it looks for the 'Formula" value which is what you see when you select a cell and look at the value in the formula bar.

For your full data set, placing the upperleft-most cell in A1, the max value is 169.8749 in cell $O$44.
 

xiahuagreen

New Member
Joined
Oct 19, 2019
Messages
5
Thank you for the explanation.
I think the search maxcell routine can find the max value, but the maxcell address routine could not record it, when all the cycles completed, the cell address has lost, ends up no way to highlight it that's why the error comes up with "range... color=22" ...
what I need to do: I can write out the max value in a remote cell, retrieve that value after the search is done, and compare that value within the zoomrng cell values, find the match and write out the address and color it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,573
Office Version
365
Platform
Windows
I think the search maxcell routine can find the max value, but the maxcell address routine could not record it
The search cannot find the Max value, that's why you get the message "No Max value found"

Based on JoeMo's suggestion try
Code:
Set MaxCell = zoomRng.Find(maxvalue, , xlFormulas, xlWhole, , , , , False)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,395
Messages
5,444,225
Members
405,274
Latest member
go2nivas

This Week's Hot Topics

Top