Help with Error

J

Jim G

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 'error>>Object 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
 
C

carlo

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 'error>>Object variable or With Block variablenot 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
Click to expand...

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").FormulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

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

Hth Carlo
 
C

carlo

I've recorded the code below to create a new sheet if one does not existand
then populate control fields before copying them to a template.  I getthe
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?
Click to expand...
Is there a simplier way to write the code for the column headers?
Click to expand...
Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet
Click to expand...
On Error Resume Next
    Set sh = Worksheets("Control")
    On Error GoTo 0
    If sh Is Nothing Then
        Worksheets.Add.Name = ("Control")
        sh.Activate 'error>>Object 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"
Click to expand...
    '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)"
Click to expand...
    '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
Click to expand...
    '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
Click to expand...
End If
End Sub
Click to expand...

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").FormulaR1C1 =
"=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 -
Click to expand...

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
 
J

Jim G

Thanks Carlo, both worked perfectly and another lesson learned!
--
Jim


carlo said:
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?
Click to expand...
Is there a simplier way to write the code for the column headers?
Click to expand...
Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet
Click to expand...
On Error Resume Next
Set sh = Worksheets("Control")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = ("Control")
sh.Activate 'error>>Object 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"
Click to expand...
'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)"
Click to expand...
'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
Click to expand...
'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
Click to expand...
End If
End Sub
Click to expand...

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").FormulaR1C1 =
"=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 -
Click to expand...

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
Click to expand...
 
C

carlo

Thanks Carlo, both worked perfectly and another lesson learned!
--
Jim



carlo said:
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.  Iget 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 'error>>Object 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") = "CostsAfter"
    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").FormulaR1C1 =
"=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 -
Click to expand...
Click to expand...
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"
Click to expand...
cheers
Carlo- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

You're welcome, thanks for the feedback

Carlo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"=TEXT(RC[-1],"MMM")" 4
how to use Solver in Excel VBA 1
Data samples 3
macro to copy rows that contain a cell with a certain number 3
Macro 3
Macro that deletes and pastes filterd rows and then some 2
Help with my VBA and formula 8
smart tags 1

Top