Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Runtime Error '91' Object variable or With block variable not set
I have a problem as per the subject line.
The problem region of code is listed below. The actual error is occurring at this point of execution in the Sub Next_Bold_Font_Found() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate The routine works perfectly in that all subtotals are identified and formatted. I tried different lists (they're dynamic) to make sure and no problems in the formatting except for the macro going into a continuous loop. Sub Format_All_Subtotals() ' ' Finds & Formats The First Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True Range("A50").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:J1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select Application.Run "Test_Next_Cell_Down" End Sub Sub Next_Bold_Font_Found() ' 'Finds & Formats The Remaining Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True ActiveCell.Offset(1, 0).Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:K1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select ActiveCell.Offset(1, 0).Select End Sub Sub Test_Next_Cell_Down() ' ' Tests that the cell below Is Not Empty Do Application.Run "Next_Bold_Font_Found" Loop While Not IsEmpty(ActiveCell) Application.Run "Go_Home" formWhatNext.Show End Sub Sorry if the code is as bit long winded, but I'm teaching myself VBA and learning as I go. Any help with this post is very much appreciated. ----- AlecC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Runtime Error '91' Object variable or With block variable not set
Hi,
I can see where the code is going wrong but dont know what you're trying to do. We start the search in A50. Let's say we find a bold cell in A55, what is this bit of code supposed to do? I can see you want to make something bold 7 columns to the right but the A1 -J1 has me stumped. ActiveCell.Offset(0, 7).Range("A1:J1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select Mike "Alec Coliver" wrote: I have a problem as per the subject line. The problem region of code is listed below. The actual error is occurring at this point of execution in the Sub Next_Bold_Font_Found() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate The routine works perfectly in that all subtotals are identified and formatted. I tried different lists (they're dynamic) to make sure and no problems in the formatting except for the macro going into a continuous loop. Sub Format_All_Subtotals() ' ' Finds & Formats The First Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True Range("A50").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:J1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select Application.Run "Test_Next_Cell_Down" End Sub Sub Next_Bold_Font_Found() ' 'Finds & Formats The Remaining Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True ActiveCell.Offset(1, 0).Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:K1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select ActiveCell.Offset(1, 0).Select End Sub Sub Test_Next_Cell_Down() ' ' Tests that the cell below Is Not Empty Do Application.Run "Next_Bold_Font_Found" Loop While Not IsEmpty(ActiveCell) Application.Run "Go_Home" formWhatNext.Show End Sub Sorry if the code is as bit long winded, but I'm teaching myself VBA and learning as I go. Any help with this post is very much appreciated. ----- AlecC . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Runtime Error '91' Object variable or With block variable not set
I think it's a good idea to set a variable to the results of the .find()
statement. Then you can check that to see if it was found. Dim FoundCell as range set foundcell = cells.find(What:="", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:= xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=True) if foundcell is nothing then 'not found, don't do anything else foundcell.offset(0,7).Range("A1:J1").Font.Bold = True end if =========== And if you want to loop through your data, look at the example for FindNext in VBA's help. It'll find the first, keep track of where that was found, then keep finding until the foundcell loops back to that first address. Alec Coliver wrote: I have a problem as per the subject line. The problem region of code is listed below. The actual error is occurring at this point of execution in the Sub Next_Bold_Font_Found() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate The routine works perfectly in that all subtotals are identified and formatted. I tried different lists (they're dynamic) to make sure and no problems in the formatting except for the macro going into a continuous loop. Sub Format_All_Subtotals() ' ' Finds & Formats The First Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True Range("A50").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:J1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select Application.Run "Test_Next_Cell_Down" End Sub Sub Next_Bold_Font_Found() ' 'Finds & Formats The Remaining Transport Company Totals in Bold Case Application.FindFormat.Font.Bold = True ActiveCell.Offset(1, 0).Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=True).Activate ActiveCell.Offset(0, 7).Range("A1:K1").Select Selection.Font.Bold = True ActiveCell.Offset(0, -7).Range("A1").Select ActiveCell.Offset(1, 0).Select End Sub Sub Test_Next_Cell_Down() ' ' Tests that the cell below Is Not Empty Do Application.Run "Next_Bold_Font_Found" Loop While Not IsEmpty(ActiveCell) Application.Run "Go_Home" formWhatNext.Show End Sub Sorry if the code is as bit long winded, but I'm teaching myself VBA and learning as I go. Any help with this post is very much appreciated. ----- AlecC -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 434, object does not support this method or property | Excel Discussion (Misc queries) | |||
Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run time error '91' object variable-explain in plain english?? | Excel Discussion (Misc queries) | |||
object variable or with block variable not set | Excel Discussion (Misc queries) |