•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Visual Basic 4 / 5 / 6 section within the Software Development category of DaniWeb, a massive community of 402,686 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,391 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums
Views: 2389 | Replies: 2
 |
•
•
Join Date: Apr 2008
Posts: 3
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Excel 2007 Macro/VB6 Run-time error 91:Object variable or with block variable not set
#1
Apr 7th, 2008
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.
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.
•
•
Join Date: Jun 2007
Posts: 78
Reputation:
Rep Power: 2
Solved Threads: 6
Junior Poster in Training
Re: Excel 2007 Macro/VB6 Run-time error 91:Object variable or with block variable not set
#2
Apr 9th, 2008
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
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
•
•
•
•
Originally Posted by 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.
•
•
Join Date: Apr 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Excel 2007 Macro/VB6 Run-time error 91:Object variable or with block variable not set
#3
Apr 14th, 2008
Thanks manoshailu, it worked.
•
•
•
•
Originally Posted by 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
|
•
•
•
•
•
•
•
•
DaniWeb Visual Basic 4 / 5 / 6 Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Previous Thread: If...end if statement or other function
- Next Thread: working offline withe the data
