Application defined or object defined error??
 

TIA for any help offered. (I know I post a ton of questions here, but I
am learning a lot thanks to all the great advice!!)

I am trying to set 4 ranges. With my limited experience, it looks like
the coding below should work OK. However, when I test the macro to make
sure everything I have written so far works, I get an "Application
defined or object defined error" and the editor highlights the "Set
rngBS1" statement.

I have to be missing something..... I just don't know what it is.

here is the beginning of my code...

Option Explicit

Sub BlankNums()
Dim rngBS1 As Range, rngBS2 As Range, rngBS3 As Range, rngBS4 As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim irow As Integer, jrow As Integer

With Worksheets("Budget Summary")
ActiveSheet.UsedRange
Set rngBS1 = .Range(.Cells(3, 3), .Cells(3,
Columns.Count).End(xlLeft))
Set rngBS2 = .Cells(4, 2)
Application.FindFormat.Interior.ColorIndex = 6
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Set rngBS3 = Cells.Find(What:="", After:=rngBS2, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True)
Application.FindFormat.Clear
irow = rngBS3.Row
Set rngBS4 = .Range(.Cells(irow, 3), .Cells(irow,
Columns.Count).End(xlLeft))
End With


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=530210

Application defined or object defined error??
 

Set rngBS1 = .Range(.Cells(3, 3), .Cells(3, .Cells(3,
Columns.Count).End(xlToLeft).Column))

Similarly for rngBS4

"Celt" wrote:


TIA for any help offered. (I know I post a ton of questions here, but I
am learning a lot thanks to all the great advice!!)

I am trying to set 4 ranges. With my limited experience, it looks like
the coding below should work OK. However, when I test the macro to make
sure everything I have written so far works, I get an "Application
defined or object defined error" and the editor highlights the "Set
rngBS1" statement.

I have to be missing something..... I just don't know what it is.

here is the beginning of my code...

Option Explicit

Sub BlankNums()
Dim rngBS1 As Range, rngBS2 As Range, rngBS3 As Range, rngBS4 As Range
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim irow As Integer, jrow As Integer

With Worksheets("Budget Summary")
ActiveSheet.UsedRange
Set rngBS1 = .Range(.Cells(3, 3), .Cells(3,
Columns.Count).End(xlLeft))
Set rngBS2 = .Cells(4, 2)
Application.FindFormat.Interior.ColorIndex = 6
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Set rngBS3 = Cells.Find(What:="", After:=rngBS2, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True)
Application.FindFormat.Clear
irow = rngBS3.Row
Set rngBS4 = .Range(.Cells(irow, 3), .Cells(irow,
Columns.Count).End(xlLeft))
End With


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=530210

Application defined or object defined error??
 

Thanks Toppers! That worked.

Can I ask you another question? Is the statement:

irow = rngBS3.Row

need a "Set" infront of it? I am getting an "Object variable or Wit
block variable not set" error here.

I have used "icol = -rngname-.Column" in a similar manner and had n
problems.

Thanks for you help

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=53021

Application defined or object defined error??
 

Oops, nevermind that last question. My worksheet is protected, so th
"find" function was not working (ie. returning a range o
-Nothing-).... hence my error message!

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=53021

Application defined or object defined error??
 

No ... but you will get an error if no match is found.

From VBA help ....

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5 <=== irow=c.row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With


"Celt" wrote:


Thanks Toppers! That worked.

Can I ask you another question? Is the statement:

irow = rngBS3.Row

need a "Set" infront of it? I am getting an "Object variable or With
block variable not set" error here.

I have used "icol = -rngname-.Column" in a similar manner and had no
problems.

Thanks for you help!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=530210