Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsupported object or method...
Hi,
I've been trying to get this macro to work: Sub Macro3() Dim masv() Dim rw As Long, v, i As Long, rng As Range Dim sh As Worksheet, cell As Range ReDim masv(1 To (Worksheets.Count - 1) * 484) rw = 1 For Each sh In Worksheets If LCase(sh.Name) < "teardown inventory" Then Set rng = sh.Range("A17:A500") v = rng.Value For i = LBound(v, 1) To UBound(v, 1) If Len(Trim(v(i, 1))) < 0 Then masv(rw) = LCase(v(i, 1)) rw = rw + 1 End If Next End If Next ReDim Preserve masv(1 To rw - 1) Set sh = Worksheets("Teardown Inventory") For Each cell In sh.Range("A6:A4500") For i = LBound(masv) To UBound(masv) If LCase(cell.Value) = masv(i) Then cell.Resize(1,6).ColorIndex = 6 Exit For End If Next Next End Sub ...but for some reason I keep getting an error of the cell.Resize line What it is supposed to do is highlight all matches it finds on number of grouped worksheets on a master sheet by corresponding number in column A. Any help of why the cell formatting won't work i appreciated. I use XL 2000 if that helps. Best Regards, Mjack -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsupported object or method...
This worked for me
changed that line to cell.Resize(1, 6).Interior.ColorIndex = 6 Sub Macro3() Dim masv() Dim rw As Long, v, i As Long, rng As Range Dim sh As Worksheet, cell As Range ReDim masv(1 To (Worksheets.Count - 1) * 484) rw = 1 For Each sh In Worksheets If LCase(sh.Name) < "teardown inventory" Then Set rng = sh.Range("A17:A500") v = rng.Value For i = LBound(v, 1) To UBound(v, 1) If Len(Trim(v(i, 1))) < 0 Then masv(rw) = LCase(v(i, 1)) rw = rw + 1 End If Next End If Next ReDim Preserve masv(1 To rw - 1) Set sh = Worksheets("Teardown Inventory") For Each cell In sh.Range("A6:A4500") For i = LBound(masv) To UBound(masv) If LCase(cell.Value) = masv(i) Then cell.Resize(1, 6).Interior.ColorIndex = 6 Exit For End If Next Next End Sub -- Regards, Tom Ogilvy "mjack003 " wrote in message ... Hi, I've been trying to get this macro to work: Sub Macro3() Dim masv() Dim rw As Long, v, i As Long, rng As Range Dim sh As Worksheet, cell As Range ReDim masv(1 To (Worksheets.Count - 1) * 484) rw = 1 For Each sh In Worksheets If LCase(sh.Name) < "teardown inventory" Then Set rng = sh.Range("A17:A500") v = rng.Value For i = LBound(v, 1) To UBound(v, 1) If Len(Trim(v(i, 1))) < 0 Then masv(rw) = LCase(v(i, 1)) rw = rw + 1 End If Next End If Next ReDim Preserve masv(1 To rw - 1) Set sh = Worksheets("Teardown Inventory") For Each cell In sh.Range("A6:A4500") For i = LBound(masv) To UBound(masv) If LCase(cell.Value) = masv(i) Then cell.Resize(1,6).ColorIndex = 6 Exit For End If Next Next End Sub ..but for some reason I keep getting an error of the cell.Resize line. What it is supposed to do is highlight all matches it finds on a number of grouped worksheets on a master sheet by corresponding numbers in column A. Any help of why the cell formatting won't work is appreciated. I use XL 2000 if that helps. Best Regards, Mjack --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsupported object or method...
I replaced that line and now it works a little too well. It highlighte
all items on the master sheet instead of just the ones from th selected sheets. This is beyond me. Thanks again. Regards, Mjac -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method Add of object Validation Failed - please help | New Users to Excel | |||
Object property/method problem | Excel Discussion (Misc queries) | |||
Is there a method for refreshing an object variable? | Excel Programming | |||
Method ~ of Object ~ Failed. | Excel Programming | |||
Object doesn't support this property or method | Excel Programming |