Excel 2007 Macro/VB6 Run-time error 91:Object variable or with block variable not set

[alrjr79]

I'm trying to create a simple macro that changes header name. Example: header name: vcom#, run macro will change to itemnumber. I want the macro to check the first row for vcom# and change it. The header vcom# will never be in the same cell, it will vary worksheet to worksheet.
When I run the macro, it changes vcom# to itemnumber but stops and gives me an error message 91.

Here is the code:

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Find(What:="vcom#", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="vcom#", Replacement:="itemNumber", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -3).Range("A1").Select
End Sub

Can anyone help.

[manoshailu]

hi,

Try the below codings:

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveCell.Rows("1:1").EntireRow.Select

If Not (Selection.Find(What:="vcom#", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)) Is Nothing Then
Selection.Find(What:="vcom#", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="vcom#", Replacement:="itemNumber", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
While Not (Selection.FindNext(after:=ActiveCell) Is Nothing)
Selection.FindNext(after:=ActiveCell).Activate
ActiveCell.Offset(0, -3).Range("A1").Select
Wend
End If

End Sub

have a nice day:)

Shailaja:)

I'm trying to create a simple macro that changes header name. Example: header name: vcom#, run macro will change to itemnumber. I want the macro to check the first row for vcom# and change it. The header vcom# will never be in the same cell, it will vary worksheet to worksheet.
When I run the macro, it changes vcom# to itemnumber but stops and gives me an error message 91.

Here is the code:

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Find(What:="vcom#", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="vcom#", Replacement:="itemNumber", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -3).Range("A1").Select
End Sub

Can anyone help.

[alrjr79]

Thanks manoshailu, it worked.

hi,

Try the below codings:

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveCell.Rows("1:1").EntireRow.Select

If Not (Selection.Find(What:="vcom#", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)) Is Nothing Then
Selection.Find(What:="vcom#", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="vcom#", Replacement:="itemNumber", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
While Not (Selection.FindNext(after:=ActiveCell) Is Nothing)
Selection.FindNext(after:=ActiveCell).Activate
ActiveCell.Offset(0, -3).Range("A1").Select
Wend
End If

End Sub

have a nice day:)

Shailaja:)