Riaaz66

06-03-2008, 08:28 AM

Hi,

I am pretty new with VBA and have an issue.

I download data form an SQL Query, and have to add a number of columns (5 in total).

Each column have to be labelled and should contain a particular formula.

Now the number of rows differs every month so the formulas should be copied from the second row till the last filled row.

My code works but hangs, if I want to do that for another set of columns.

I have my code in two parts, the first will enter the formula the second will copy the formula until the last cell.

The last cell is being determined by looking for the last used row by checking columns,A,B and C and returning the biggest number.

The macro (Part 2) will then simply copy down from the active cell to the row number specified.

In the example I enter formulas in column D, E F.

If you copy the code of PART 1 for another set of 10 columns (column T:AA) it stops on column 8 (AA)

Here is my code, where does it go wrong:

PART 1

Sub EnterFormula()

Range("O1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("O1").Select

Call CopyActiveCell

Range("P1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("P1").Select

Call CopyActiveCell

Range("Q1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("Q1").Select

Call CopyActiveCell

Range("R1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("R1").Select

Call CopyActiveCell

Range("S1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("S1").Select

Call CopyActiveCell

End Sub()

PART 2

Option Explicit

Sub CopyActiveCell()

Dim lRow As Long

Dim ACell As String

Dim Col As String

lRow = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, Range("B65536").End(xlUp).Row, Range("C65536").End(xlUp).Row)

ACell = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

Col = Left(ACell, 1)

Range(ACell & ":" & ACell).AutoFill Range(ACell & ":" & Col & lRow)

End Sub

I am pretty new with VBA and have an issue.

I download data form an SQL Query, and have to add a number of columns (5 in total).

Each column have to be labelled and should contain a particular formula.

Now the number of rows differs every month so the formulas should be copied from the second row till the last filled row.

My code works but hangs, if I want to do that for another set of columns.

I have my code in two parts, the first will enter the formula the second will copy the formula until the last cell.

The last cell is being determined by looking for the last used row by checking columns,A,B and C and returning the biggest number.

The macro (Part 2) will then simply copy down from the active cell to the row number specified.

In the example I enter formulas in column D, E F.

If you copy the code of PART 1 for another set of 10 columns (column T:AA) it stops on column 8 (AA)

Here is my code, where does it go wrong:

PART 1

Sub EnterFormula()

Range("O1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("O1").Select

Call CopyActiveCell

Range("P1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("P1").Select

Call CopyActiveCell

Range("Q1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("Q1").Select

Call CopyActiveCell

Range("R1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("R1").Select

Call CopyActiveCell

Range("S1") = "=SUM(RC[-4]:RC[-3])" ' Example Formula

Range("S1").Select

Call CopyActiveCell

End Sub()

PART 2

Option Explicit

Sub CopyActiveCell()

Dim lRow As Long

Dim ACell As String

Dim Col As String

lRow = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, Range("B65536").End(xlUp).Row, Range("C65536").End(xlUp).Row)

ACell = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

Col = Left(ACell, 1)

Range(ACell & ":" & ACell).AutoFill Range(ACell & ":" & Col & lRow)

End Sub