Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming |