Object Variable or With Block variable not set?
I get the above error message trying to runa recorded
macro. I'm too new to VB to understand exactly what the issue is. Perhaps someone else can help. Here's the current code I'm using: Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC[- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With ''' ''' Columns("E:E").NumberFormat = "$#,##0.00_);($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate It's the last line that gives the error, but I'm positive that the 'Grand Total' value exists, since it is placed into the spreadsheet in earlier code. Any help is greatly appreciated. |
Object Variable or With Block variable not set?
True, you put it in Range("E250").End(xlup).Offset(5,-2)
but when you run the search, you only have one cell selected - unlike in Excel itself, the find only looks in that cell. Sub AATester2() Range("C2").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub fails because the target isn't in C2 but Sub AATester2() Range("C:C").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub Works. Grand Total is in C24 in my test. -- Regards, Tom Ogilvy "Chris M." wrote in message ... I get the above error message trying to runa recorded macro. I'm too new to VB to understand exactly what the issue is. Perhaps someone else can help. Here's the current code I'm using: Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC[- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With ''' ''' Columns("E:E").NumberFormat = "$#,##0.00_);($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate It's the last line that gives the error, but I'm positive that the 'Grand Total' value exists, since it is placed into the spreadsheet in earlier code. Any help is greatly appreciated. |
Object Variable or With Block variable not set?
Chris,
Recommend that (if you don't already have) you have Option Explicit at the top of the module (above any of the code). Than go to the Debug window and compile your code. Excel should highlight problem areas for you. You can also step through your code using the F8 key and see if there is a problem. Your added code appears to work in Excel97 without difficulty. -- sb "Chris M." wrote in message ... LOL, I can't win. No sooner have I corrected this error message (with your gracious help of course) then I get another. That's what I get for messing with code that works. Here's what's occuring now. The error message is "Unable to set the Locked property of the Range class" The code is the same as the previous code, with additions. Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC[- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With Columns("E:E").NumberFormat = "$#,##0.00_);($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' Columns("C:C").Select 'ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Offset(2, -2).Range("A1").Select ActiveCell.FormulaR1C1 = "Comments/Disclosures:" With ActiveCell.Characters(Start:=1, Length:=21).Font .Name = "Arial" .FontStyle = "Bold" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Range("A1:K20").Select With Selection .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = True End With ActiveCell.Range("A1:K20").Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With ''' Range("A1:C1").Merge True Cells.Locked = True Cells.FormulaHidden = True Columns("B:B").ColumnWidth = 5 For Each Cell In Range("D1:D200") If Cell.Interior.ColorIndex = 36 Then Cell.Locked = False Next ActiveSheet.Protect Password:="xetnec" LastCell = Range("E300").End(xlUp).Address ActiveSheet.PageSetup.PrintArea = "$A$1:" & LastCell Range("A1").Select End Sub The section bordered by ''' is the code I've added. I think that the added code has messed up my frame of reference, but I'm not quite sure. Thanks again for the help. -----Original Message----- True, you put it in Range("E250").End(xlup).Offset(5,-2) but when you run the search, you only have one cell selected - unlike in Excel itself, the find only looks in that cell. Sub AATester2() Range("C2").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub fails because the target isn't in C2 but Sub AATester2() Range("C:C").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub Works. Grand Total is in C24 in my test. -- Regards, Tom Ogilvy "Chris M." wrote in message ... I get the above error message trying to runa recorded macro. I'm too new to VB to understand exactly what the issue is. Perhaps someone else can help. Here's the current code I'm using: Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC[- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With ''' ''' Columns("E:E").NumberFormat = "$#,##0.00_); ($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate It's the last line that gives the error, but I'm positive that the 'Grand Total' value exists, since it is placed into the spreadsheet in earlier code. Any help is greatly appreciated. . |
Object Variable or With Block variable not set?
You have been a fabulous help Tom. I appreciate it very
much. I have one last little issue that I'd like to resolve. The comments section that I created remains locked. In the code that creates the section, in the 'With Selection' statement I added ".locked = false" but the cell remains locked once the sheet is protected. Thanks again -----Original Message----- The problem appears to be in your loop through column D. Are there cells in that range that are part of merged cells? If so, that appears to be the problem in my tests. As an example, if I do Sub AATester3() Range("A3:C3").Merge True Cells.Locked = True Cells.FormulaHidden = True For Each cell In Range("B1:b10") cell.Locked = False Next End Sub I get the failure on Cell.Locked = False and cell at that time is refering to B3 - so for a group of merged cells, you can not change an attribute of one cell within the merged cell so it is different from the rest - that would be my take on it. Perhaps you need: For Each Cell In Range("D1:D200") If Cell.Interior.ColorIndex = 36 Then if cell.MergeArea.Count = 1 then Cell.Locked= False End if End if Next or else unlock the merged cells For Each Cell In Range("D1:D200") If Cell.Interior.ColorIndex = 36 Then Cell.MergeArea.Locked= False End if Next depending on what you want to achieve. -- Regards, Tom Ogilvy "Chris M." wrote in message ... LOL, I can't win. No sooner have I corrected this error message (with your gracious help of course) then I get another. That's what I get for messing with code that works. Here's what's occuring now. The error message is "Unable to set the Locked property of the Range class" The code is the same as the previous code, with additions. Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC[- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With Columns("E:E").NumberFormat = "$#,##0.00_); ($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' Columns("C:C").Select 'ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Offset(2, -2).Range("A1").Select ActiveCell.FormulaR1C1 = "Comments/Disclosures:" With ActiveCell.Characters(Start:=1, Length:=21).Font .Name = "Arial" .FontStyle = "Bold" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveCell.Offset(0, 1).Range("A1:K20").Select With Selection .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = True End With ActiveCell.Range("A1:K20").Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With ''' Range("A1:C1").Merge True Cells.Locked = True Cells.FormulaHidden = True Columns("B:B").ColumnWidth = 5 For Each Cell In Range("D1:D200") If Cell.Interior.ColorIndex = 36 Then Cell.Locked = False Next ActiveSheet.Protect Password:="xetnec" LastCell = Range("E300").End(xlUp).Address ActiveSheet.PageSetup.PrintArea = "$A$1:" & LastCell Range("A1").Select End Sub The section bordered by ''' is the code I've added. I think that the added code has messed up my frame of reference, but I'm not quite sure. Thanks again for the help. -----Original Message----- True, you put it in Range("E250").End(xlup).Offset(5,- 2) but when you run the search, you only have one cell selected - unlike in Excel itself, the find only looks in that cell. Sub AATester2() Range("C2").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub fails because the target isn't in C2 but Sub AATester2() Range("C:C").Select Selection.Find(What:="grand total", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub Works. Grand Total is in C24 in my test. -- Regards, Tom Ogilvy "Chris M." wrote in message ... I get the above error message trying to runa recorded macro. I'm too new to VB to understand exactly what the issue is. Perhaps someone else can help. Here's the current code I'm using: Private Sub Findtotal() Dim rng As Range Dim Cell As Range, LastCell As String Application.ScreenUpdating = False Columns("A:A").Select Set rng = Selection.Find(What:="Fees", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then rng.Select Range(ActiveCell.Offset(1, 0), ActiveCell.Offset (1, 0).End(xlDown)).Select For Each Cell In Selection If Not IsEmpty(Cell) Then Cell = Cell & " FEES" Next End If For Each Cell In Range("F3:F200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RIGHT(RC1,4) =""FEES"",RC5,0)" Next For Each Cell In Range("G3:G200") If WorksheetFunction.CountA(Cell.EntireRow) 0 Then _ Cell.FormulaR1C1 = "=IF(RC[-3]=""TOTALS"",RC [- 2],0)" Next Range("E250").End(xlUp).Offset(3, -2) = "Subtotal Less Fees" Range("E250").End(xlUp).Offset(4, -2) = "Total Fees" Range("E250").End(xlUp).Offset(5, -2) = " GRAND TOTAL" Range("E250").End(xlUp).Offset(3, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])-SUM(C [1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[1])" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(C[2])" ActiveCell.Interior.ColorIndex = 15 With ActiveCell.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With ActiveCell.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With ActiveCell.CurrentRegion.Select With Range(Selection, Selection.End(xlToLeft)) .Font.Bold = True .HorizontalAlignment = xlLeft End With ''' ''' Columns("E:E").NumberFormat = "$#,##0.00_); ($#,##0.00)" Columns("E:E").HorizontalAlignment = xlGeneral Columns("F:G").EntireColumn.Hidden = True Range("A1").EntireRow.Insert Range("A1") = "CENTEX HOMES CORPORATION" With Range("A1").Font .Name = "Century Gothic" .Bold = True .Size = 12 .ColorIndex = 3 End With ''' ActiveCell.Cells.Select ActiveCell.Activate Selection.Find(What:="grand total", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate It's the last line that gives the error, but I'm positive that the 'Grand Total' value exists, since it is placed into the spreadsheet in earlier code. Any help is greatly appreciated. . . |
All times are GMT +1. The time now is 03:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com