Object Required Error, Excel 2000 & 2003

[jfcby]

Hello,

Why am I getting the Object required Error in the following macro
code?:

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Dim wsB As Worksheet

Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_ZoneBuildingEquipList.xls")
For Each wsA In wbA.Sheets
For Each cell In wbB.Worksheets("ZONE 5 - BLDG
LIST").Range("D468")
If Right(wsA.Range("C2"), 4).Value = cell Then GoTo nws 'Object
Required Error
If Right(wsA.Range("C2"), 4).Value <> cell Then GoTo hws 'Object
Required Error
Next cell
hws:
ws.Visible = xlSheetHidden 'xlSheetVisible
nws:
Next wsA

End Sub

Thenk you for your help,
jfcby

 

[JE McGimpsey]

Couple of errors:

1) Right(wsA.Range("C2"), 4).Value doesn't make sense - Right() takes
the .Value of wsa.Range("C2") and returns a 4-character string. The
string doesn't have a .Value property.

2) You're obviously not using Option Explicit at the top of the module
or you'd find that

hws:
ws.Visible = xlSheetHidden

refers to an undefined variable, ws.

I"m not positive, but perhaps this will do what you're looking for:

Public Sub HideWorkSheetsC()
Dim wsA As Worksheet
Dim rTest As Range
Dim sCheck As String

Set rTest = Workbooks("FF_ZoneBuildingEquipList.xls").Worksheets( _
"ZONE 5 - BLDGLIST").Range("D468")
For Each wsA In Workbooks("Equip_List_FF.xls").Worksheets
sCheck = Right(wsA.Range("C2").Text, 4)
If Application.CountIf(rTest, sCheck) > 0 Then _
wsA.Visible = xlSheetHidden
Next wsA
End Sub

 

[jfcby]

Hello JE McGimpsey,

Thank you for your macro code (works great!), it help me figure out
how to get the following macro code to work. But, I did not know how
to modify your code (still learning how to wright macro's) so I
continued to change the following code:

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wsA As Worksheet
Dim ceTa As String
Dim ceTb As String

Set wbA = Workbooks("Equip_List_FF.xls")
For Each wsA In wbA.Sheets
For Each cell In
Workbooks("FF_ZoneBuildingEquipList.xls").Worksheets( _
"ZONE 5 - BLDG LIST").Range("D468")
ceTa = Right(Range("A2").Text, 4)
ceTb = Right(Range("C2").Text, 4)
If ceTa = cell Or ceTb = cell Then GoTo nws
Next cell
wsA.Visible = xlSheetHidden
nws:
Next wsA

End Sub

Thank you for your help and macro,
jfcby

 

[jfcby]

Hello,

Corrections made to the above macro code:


Sub HideWorkSheetsC()


Dim wbA As Workbook
Dim wsA As Worksheet
Dim ceTa As String
Dim ceTb As String


Set wbA = Workbooks("Equip_List_FF.xls")
For Each wsA In wbA.Sheets
For Each cell In
Workbooks("FF_ZoneBuildingEquipList.xls").Worksheets( _
"ZONE 5 - BLDG LIST").Range("D468")
ceTa = Right(wsA.Range("A2").Text, 4)
ceTb = Right(wsA.Range("C2").Text, 4)
If ceTa = cell Or ceTb = cell Then GoTo nws
Next cell
wsA.Visible = xlSheetHidden 'xlSheetVisible
nws:
Next wsA


End Sub


jfcby