Solved: Inserting Formulas With Loops

[Riaaz66]

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

[grichey]

you don't need () at [VBA]End Sub[/VBA] and this code doesn't work for me at all and is erroring on autofill.

[Riaaz66]

Hi Gavin,

That () at End Sub was by accident. Here is the code again.
Assuming that you have a worksheet which has data in it from column A to N and row 1 to 10, where the first row contains the label of the column.

If you run this code, you will see that column O till S will have a label and calculations in it. But if I do that for another 8 columns starting from the last filled column (which is column S in this example) I get stuck on it. The last row of the macro "CopyActiveCell" turns on yellow. I do not know why. It stops at the beginning of column AA. It is possible that the column starting with AA (2 characters) causes the problem?

Please help.

[vba]
Sub EnterFormula()

Range("O1") = "Extra_1"
Range("O2") = "=SUM(RC[-4]:RC[-3])" ' Example Formula
Range("O2").Select
Call CopyActiveCell

Range("P1") = "Extra_2"
Range("P2") = "=SUM(RC[-4]:RC[-3])" ' Example Formula
Range("P2").Select
Call CopyActiveCell

Range("Q1") = "Extra_3"
Range("Q2") = "=SUM(RC[-4]:RC[-3])" ' Example Formula
Range("Q2").Select
Call CopyActiveCell

Range("R1") = "Extra_4"
Range("R2") = "=SUM(RC[-4]:RC[-3])" ' Example Formula
Range("R2").Select
Call CopyActiveCell

Range("S1") = "Extra_5"
Range("S2") = "=SUM(RC[-4]:RC[-3])" ' Example Formula
Range("S2").Select
Call CopyActiveCell
End Sub

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


[/vba]

[Norie]

Wasn't this solved here?

[Bob Phillips]

It failes because you get the left character of the address, and column AA has 2, so you are trying to fill AA down into A.

I am just rewriting it for you, will post back in a mo.

[grichey]

Wasn't this solved here?

and he was banned. When you sign up for a vbax username, it should force you to click that you read the cross post stuff

[Bob Phillips]

you should be able to amend this easily

[vba]

Sub EnterFormula()
Dim lRow As Long

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

Range("O1:S1").Value = Split("Extra_1,Extra_2,Extra_3,Extra_4,Extra_5", ",")
Range("O2:S2") = "=SUM(RC[-4]:RC[-3])"

Range("O2:S2").AutoFill Range("O2:S2").Resize(lRow - 1)
End Sub

[/vba]

[Riaaz66]

Hi Norie,

It isn't solved yet. I still don't know why my code is haging on the last coderule where it say:
[VBA]Range(ACell & ":" & ACell).AutoFill Range(ACell & ":" & Col & lRow) [/VBA] This rule is turning yellow on execution.

Regards,
Riaaz66

[Riaaz66]

Hi Xld,

I will amend your code in a few minutes and get back to you.
Thanx for your efforts.

Regards,
Riaaz66

[Riaaz66]

Yeah Xld,

Your worked. Thankx.
We can consider this as solved.