Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Object Variable or With Block variable not set?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 576
Default 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.


.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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.


.



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime Error '91' Object variable or With block variable not set Alec Coliver Excel Discussion (Misc queries) 2 October 24th 09 02:29 PM
Object Variable Not Set Error on Selection object Jean Excel Worksheet Functions 3 July 24th 06 06:45 PM
Run time error '91' object variable-explain in plain english?? KristyBT Excel Discussion (Misc queries) 2 April 27th 06 07:53 PM
object variable or with block variable not set Diego Excel Discussion (Misc queries) 1 August 9th 05 02:46 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Excel Worksheet Functions 1 July 9th 05 03:05 AM


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement