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