Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 132
Default Help with Error
I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. I get the
error "Object variable or With Block variable not set" at "sh.Activate"
after the sheet is created. Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?


Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
Set sh = Worksheets("Control")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = ("Control")
sh.Activate 'errorObject variable or With Block variable not set
With ActiveSheet
Range("A2").Select
ActiveCell.FormulaR1C1 = "Start of FY"
Range("B2").Select
ActiveCell.FormulaR1C1 = "7/31/2007"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Date Header Row"

'set date formulas
Range("E5").Select
ActiveCell.FormulaR1C1 = "=Control!R2C2"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("Q5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("T5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("W5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("Z5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AC5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AF5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AI5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AL5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

'set col header
Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
End With
Else


'copy new header row to Cheops Sheet
Sheets("Control").Select
Rows("5:5").Select
Selection.Copy
Sheets("Cheops").Select
Rows("13:13").Select
ActiveSheet.Paste


End If
End Sub

--
Jim
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 367
Default Help with Error
On Jan 29, 2:54*pm, Jim G wrote:
I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. *I get the
error "Object variable or With Block variable not set" *at "sh.Activate"
after the sheet is created. *Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?

Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
* * Set sh = Worksheets("Control")
* * On Error GoTo 0
* * If sh Is Nothing Then
* * * * Worksheets.Add.Name = ("Control")
* * * * sh.Activate 'errorObject variable or With Block variable not set
* * With ActiveSheet
* * Range("A2").Select
* * ActiveCell.FormulaR1C1 = "Start of FY"
* * Range("B2").Select
* * ActiveCell.FormulaR1C1 = "7/31/2007"
* * Range("A4").Select
* * ActiveCell.FormulaR1C1 = "Date Header Row"

* * 'set date formulas
* * Range("E5").Select
* * ActiveCell.FormulaR1C1 = "=Control!R2C2"
* * Range("H5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("K5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("N5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Q5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("T5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("W5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Z5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AC5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AF5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AI5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AL5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

* * 'set col header
* * Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
* * Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
* * End With
* * Else

* * 'copy new header row to Cheops Sheet
* * Sheets("Control").Select
* * Rows("5:5").Select
* * Selection.Copy
* * Sheets("Cheops").Select
* * Rows("13:13").Select
* * ActiveSheet.Paste

End If
End Sub

--
Jim

Try to enter following line before sh.activate:
set sh = activesheet

otherwise sh would be nothing and you cannot activate nothing.

For your other problem you could do following:

'set date formulas
Range("E5").FormulaR1C1 = "=Control!R2C2"
Range("H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5").Form ulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

there will be line wraps...look out for them!

Hth Carlo
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 367
Default Help with Error
On Jan 29, 3:59*pm, carlo wrote:
On Jan 29, 2:54*pm, Jim G wrote:





I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. *I get the
error "Object variable or With Block variable not set" *at "sh.Activate"
after the sheet is created. *Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?

Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
* * Set sh = Worksheets("Control")
* * On Error GoTo 0
* * If sh Is Nothing Then
* * * * Worksheets.Add.Name = ("Control")
* * * * sh.Activate 'errorObject variable or With Block variable not set
* * With ActiveSheet
* * Range("A2").Select
* * ActiveCell.FormulaR1C1 = "Start of FY"
* * Range("B2").Select
* * ActiveCell.FormulaR1C1 = "7/31/2007"
* * Range("A4").Select
* * ActiveCell.FormulaR1C1 = "Date Header Row"

* * 'set date formulas
* * Range("E5").Select
* * ActiveCell.FormulaR1C1 = "=Control!R2C2"
* * Range("H5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("K5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("N5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Q5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("T5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("W5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Z5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AC5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AF5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AI5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AL5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

* * 'set col header
* * Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
* * Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
* * End With
* * Else

* * 'copy new header row to Cheops Sheet
* * Sheets("Control").Select
* * Rows("5:5").Select
* * Selection.Copy
* * Sheets("Cheops").Select
* * Rows("13:13").Select
* * ActiveSheet.Paste

End If
End Sub

--
Jim

Try to enter following line before sh.activate:
set sh = activesheet

otherwise sh would be nothing and you cannot activate nothing.

For your other problem you could do following:

'set date formulas
* * Range("E5").FormulaR1C1 = "=Control!R2C2"
* * Range("H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5").Form ulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

there will be line wraps...look out for them!

Hth Carlo- Hide quoted text -

- Show quoted text -

Oh misunderstanding...but the other works as well, you can use the
same for the headers:
'set col header
Range("G5,J5,M5,P5,S5,V5,Y5,AB5,AE5,AH5,AK5") = "Costs After"

cheers
Carlo
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 132
Default Help with Error
Thanks Carlo, both worked perfectly and another lesson learned!
--
Jim


"carlo" wrote:

On Jan 29, 3:59 pm, carlo wrote:
On Jan 29, 2:54 pm, Jim G wrote:





I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. I get the
error "Object variable or With Block variable not set" at "sh.Activate"
after the sheet is created. Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?

Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
Set sh = Worksheets("Control")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = ("Control")
sh.Activate 'errorObject variable or With Block variable not set
With ActiveSheet
Range("A2").Select
ActiveCell.FormulaR1C1 = "Start of FY"
Range("B2").Select
ActiveCell.FormulaR1C1 = "7/31/2007"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Date Header Row"

'set date formulas
Range("E5").Select
ActiveCell.FormulaR1C1 = "=Control!R2C2"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("Q5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("T5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("W5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("Z5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AC5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AF5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AI5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
Range("AL5").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

'set col header
Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
End With
Else

'copy new header row to Cheops Sheet
Sheets("Control").Select
Rows("5:5").Select
Selection.Copy
Sheets("Cheops").Select
Rows("13:13").Select
ActiveSheet.Paste

End If
End Sub

--
Jim

Try to enter following line before sh.activate:
set sh = activesheet

otherwise sh would be nothing and you cannot activate nothing.

For your other problem you could do following:

'set date formulas
Range("E5").FormulaR1C1 = "=Control!R2C2"
Range("H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5").Form ulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

there will be line wraps...look out for them!

Hth Carlo- Hide quoted text -

- Show quoted text -

Oh misunderstanding...but the other works as well, you can use the
same for the headers:
'set col header
Range("G5,J5,M5,P5,S5,V5,Y5,AB5,AE5,AH5,AK5") = "Costs After"

cheers
Carlo

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 367
Default Help with Error
On Jan 30, 9:24*am, Jim G wrote:
Thanks Carlo, both worked perfectly and another lesson learned!
--
Jim



"carlo" wrote:
On Jan 29, 3:59 pm, carlo wrote:
On Jan 29, 2:54 pm, Jim G wrote:

I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. *I get the
error "Object variable or With Block variable not set" *at "sh.Activate"
after the sheet is created. *Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?

Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
* * Set sh = Worksheets("Control")
* * On Error GoTo 0
* * If sh Is Nothing Then
* * * * Worksheets.Add.Name = ("Control")
* * * * sh.Activate 'errorObject variable or With Block variable not set
* * With ActiveSheet
* * Range("A2").Select
* * ActiveCell.FormulaR1C1 = "Start of FY"
* * Range("B2").Select
* * ActiveCell.FormulaR1C1 = "7/31/2007"
* * Range("A4").Select
* * ActiveCell.FormulaR1C1 = "Date Header Row"

* * 'set date formulas
* * Range("E5").Select
* * ActiveCell.FormulaR1C1 = "=Control!R2C2"
* * Range("H5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("K5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("N5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Q5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("T5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("W5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Z5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AC5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AF5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AI5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AL5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

* * 'set col header
* * Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
* * Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
* * End With
* * Else

* * 'copy new header row to Cheops Sheet
* * Sheets("Control").Select
* * Rows("5:5").Select
* * Selection.Copy
* * Sheets("Cheops").Select
* * Rows("13:13").Select
* * ActiveSheet.Paste

End If
End Sub

--
Jim

Try to enter following line before sh.activate:
set sh = activesheet

otherwise sh would be nothing and you cannot activate nothing.

For your other problem you could do following:

'set date formulas
* * Range("E5").FormulaR1C1 = "=Control!R2C2"
* * Range("H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5").Form ulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

there will be line wraps...look out for them!

Hth Carlo- Hide quoted text -

- Show quoted text -

Oh misunderstanding...but the other works as well, you can use the
same for the headers:
* * 'set col header
* * Range("G5,J5,M5,P5,S5,V5,Y5,AB5,AE5,AH5,AK5") = "Costs After"

cheers
Carlo- Hide quoted text -

- Show quoted text -

You're welcome, thanks for the feedback

Carlo
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
run time error 1004 general odbc error excel 2003 vba Mentos Excel Programming 5 January 24th 11 02:56 PM
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
Error Handling - On Error GoTo doesn't trap error successfully David Excel Programming 9 February 16th 06 05:59 PM
Form Err.Raise error not trapped by entry procedure error handler [email protected] Excel Programming 1 February 8th 06 10:19 AM
Automation Error, Unknown Error. Error value - 440 Neo[_2_] Excel Programming 0 May 29th 04 05:26 AM


All times are GMT +1. The time now is 02:07 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