Object Required Error, Excel 2000 & 2003

  • Thread starter Thread starter jfcby
  • Start date Start date
J

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("D4:D68")
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
 
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("D4:D68")
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
 
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("D4:D68")
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
 
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("D4:D68")
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
 
Back
Top