Problems with Objects

[Debbs76]

Hi,

Can somebody please explain to me why I am getting the object variable not set error when I am doing the following code?

Sub HideMonths()
'
' HideMonths Macro
'
Dim myFind As Range
'
Set myFind = Rows(1).Find(What:="1/5/2007", _
After:=Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Range("C1", myFind.Offset(0, -1)).EntireColumn.Hidden = True
End Sub

I have copied this straight out of the Step by Step Office Excel 2007 Visual Basics for Applications book and no matter how many times I try it I get the same error and it's driving me mad.

Help would be very much appreciated - thx

The code is supposed to hide everything from Column C to the column before the date in the find (in this case column H)

 

[Leith Ross]

Hello Debbs76,

The result of the Find method will either be the cell where the value was found or the special object value Nothing. You need to test the result of the Find after it is executed to decide what action should be taken next.

Sub HideMonths()
'
' HideMonths Macro
'
Dim myFind As Range
'
Set myFind = Rows(1).Find(What:="1/5/2007", _
After:=Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
  If Not myfind Is Nothing Then
     Range("C1", myFind.Offset(0, -1)).EntireColumn.Hidden = True
  Else
    MsgBox What & "Not Found."
End Sub

Sincerely,
Leith Ross

 

[Marcelo Branco]

Hi,

1.Try change from
Lookin=xFormulas
to
Lookin=xlValues

2.If this doesnt work check if your dates in row 1 are text or dates.

If they are dates try
Find(What:=CDate("1/5/2007"), ...


HTH

M.

 

[Debbs76]

Hi both for getting back so quickly. The result of the find is giving me 'nothing' as the value. But the book seems to think that this code will result in the columns being hidden. I'm in Australia. Would this affect the result of the find - as in is there another format Excel would recognise?? If I do a find in excel it works no problem but when I'm looking in the code it returns 'nothing' and therefore doesn't work. I'm just frustrated to be reading through the book and have errors.

The next line of code does deal with the code returning an empty value but it should be working which is why I jumped on here to ask.