Compile error

[MarkBeck]

Hi

I have a piece of code that starts as follows;

Sub UpdateCalc()

Set ShtSum = ActiveWorkbook.Sheets("Transactions")
lastrw = ShtSum.Cells(Rows.Count, "b").End(xlUp).Row

When running this code it breaks (highlighting "Set ShtSum") and gives me the following error;

Compile error
Variable not defined

What can be wrong?

Shouls i paste the entire code?

Thanks

Mark

 

[SkipVought]

Hi,

You have Option Explicit in your module, which is GOOD!

Forces you to define all variables

Sub UpdateCalc()
[b]dim ShtSum  as worksheet, lastrow as long
Set ShtSum = ActiveWorkbook.Sheets("Transactions")
lastrw = ShtSum.Cells(Rows.Count, "b").End(xlUp).Row
'...

Skip,

_[red][/red]

 

[SkipVought]

oops...

Sub UpdateCalc()
[b]dim ShtSum  as worksheet, lastrow as long[/b]
Set ShtSum = ActiveWorkbook.Sheets("Transactions")
lastrw = ShtSum.Cells(Rows.Count, "b").End(xlUp).Row
'...

Skip,

_[red][/red]

 

[MarkBeck]

Thanks Skip. I have corrected both the & "lastr(o)w" myself.

 

[SkipVought]

I should hope so! ;-)

Skip,

_[red][/red]

 

[MarkBeck]

Another question though;

This is my code

Sub UpdateCalc()
Dim ShtSum As Worksheet, lastrw As Long
Set ShtSum = ActiveWorkbook.Sheets("Transactions")
lastrw = ShtSum.Cells(Rows.Count, "b").End(xlUp).Row

Sheets("Transactions").Select
With ShtSum
.Activate
.Range("AL2:BC2").Copy .Range("AL3").Resize(lastrw - 2, 1)
Calculate
Range("AL3:BC6000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'!!enter here a paste special formula, but find the "Range" first!!
Range("a1").Select
End With
Sheets("Welcome").Select
Range("A1").Select
End Sub

I would like to achieve two things

A) >.Range("AL2:BC2").Copy .Range("AL3").Resize(lastrw - 2, 1) THIS SHOULD ONLY DO A PASTE SPECIAL BY FORMULA.

B) AFTER CALCULATION OF FORMULA
> Range("AL3:BC6000").Select (goes on to paste special results)
WOULD LIKE TO REPLACE FIXED RANGE WITH A RESIZED RANGE (AL3:AL??) USING COLUMN B AS THE FIND LAST ROW.

Thanks a lot

Mark

 

[SkipVought]

???

You don't paste into a range -- why resize anything.

You paste into a single cell. The range is the same rows deep and columns wide as the source.

Is this what you need?

Sub UpdateCalc()
    Dim ShtSum  As Worksheet, lastrw As Long
    Set ShtSum = ActiveWorkbook.Sheets("Transactions")

'    Sheets("Transactions").Select   THIS IS UNNECESSARY
    With ShtSum
'        .Activate                   THIS IS UNNECESSARY
        lastrw = .Cells(Rows.Count, "b").End(xlUp).Row
        .Range("AL2:BC2").Copy .Cells(lastrw + 1, 1)
        Calculate
        With .Range("AL3:BC6000")
            .Copy
            .PasteSpecial _
                Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
                Transpose:=False
        End With
        '!!enter here a paste special formula, but find the "Range" first!! ?????
        .Range("a1").Select
    End With
    Sheets("Welcome").Select
    Range("A1").Select
End Sub

don't know what that last comment means.

Skip,

_[red][/red]

 

[MarkBeck]

Skip

I copy a series of extensive formulas from AL2:BC2 and paste it on AL3:BClastrow. They perform evaluations and calculate results based on the info in A3:AKlastrow. I then need these results pasted as values.

Since some info in A:AK is changed throughout 3:lastrow, i need to reevaluate the AL:BC extended range. So i think i do need the Resize.

Question A)
The problem is that the formatting in row 2 is very different to the rest. if i do a simple COPY it brings along all the formatting to AL3:ALlastrow.

Can this ".Range("AL2:BC2").Copy .Cells(lastrw + 1, 1)" be changed to a paste special - formulas?

Incidently; Cells(lastrw "+ 1", 1) i have -2 to account for the elimination of Al1:BC2 which are headers.

Question B)
>With .Range("AL3:BC6000")

Can this be replaced to With a AL3:resized Range?

Thanks a lot Skip

Mark

 

[SkipVought]

last row is last row regardless of headers UNLESS it is used in a RESIZE which is relative to a RANGE.

Can this ".Range("AL2:BC2").Copy .Cells(lastrw + 1, 1)" be changed to a paste special - formulas?
.Range("AL2:BC2").Copy 
.Cells(lastrw + 1, 1).pastespecial xlvalues

B:

lastrw = ShtSum.Cells(Rows.Count, "b").End(xlUp).Row

again.

Skip,

_[red][/red]

 

[MarkBeck]

THIS calculates Columns A to R (not AL to BC) and only the last row.

Sub UpdateCalc()

Dim ShtSum As Worksheet, lastrw As Long
Set ShtSum = ActiveWorkbook.Sheets("Transactions")
With ShtSum
lastrw = .Cells(Rows.Count, "b").End(xlUp).Row
.Range("AL2:BC2").Copy
.Cells(lastrw + 1, 1).PasteSpecial xlValues
Calculate

End With
Sheets("Welcome").Select
Range("A1").Select
End Sub

I need COPYING of AL2:BC2, and pasting of FORMULA, RECALCULATE, PASTE VALUE to AL3:BClastrow

 

[SkipVought]

If I read your posts and devine the problem...

you are copying FORMULAS from row 2

pasting into the next empty row below (why are you using column B to determine the last row in column AL???)

calculating

copy the pasted range

past special - VALUES

Do I have it correct?

Skip,

_[red][/red]

 

[MarkBeck]

>pasting into the next empty row below[b/]. No, I need to paste it through the entire AL3[b/]:BClastrow to recalculate all[b/] the rows.


>(why are you using column B to determine the last row in column AL???). because column B never has empty lines (so the xlup works)

rest is correct

 

[SkipVought]

Sub UpdateCalc()
    Dim ShtSum  As Worksheet, lastrw As Long
    Set ShtSum = ActiveWorkbook.Sheets("Transactions")

    With ShtSum
        lastrw = .Cells(.Cells.Rows.Count, "b").End(xlUp).Row
        .Range("AL2:BC2").Copy .Range("AL3:AL" & lastrw)
        Calculate
        With .Range("AL3:AL" & lastrw)
            .Copy
            .PasteSpecial _
                Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
                Transpose:=False
        End With
        .Range("a1").Select
    End With
    Sheets("Welcome").Select
    Range("A1").Select
End Sub

Skip,

_[red][/red]

 

[MarkBeck]

Works a treat!

Thanks skip