PDA

View Full Version : Solved: Inserting a formula with VBA



Glaswegian
05-24-2012, 06:58 AM
Hi

I'm having another bad day...

I'm trying to insert a CountA formula in code as part of a routine that pastes data to a sheet, filters the data, and renames the sheet, and so on.

For some reason I cannot find the correct syntax to have the formula inserted correctly. Code snippet below

With ActiveWorkbook
Nrow = .ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
.ActiveSheet.Cells(Nrow + 2, 7).Formula = "=CountA(G2:G" & Nrow")"
.ActiveSheet.Cells(Nrow + 2, 7).Font.Bold = True
End With

I need to have a CountA formula in a cell 2 rows below the end of the data and in column G.

I know this is something silly but it's just not happening today. Thanks.

BrianMH
05-24-2012, 07:17 AM
You forgot the & after Nrow.


Dim nrow As Integer

With ActiveWorkbook
nrow = .ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
.ActiveSheet.Cells(nrow + 2, 7).Formula = "=CountA(G2:G" & nrow & ")"
.ActiveSheet.Cells(nrow + 2, 7).Font.Bold = True
End With

Glaswegian
05-24-2012, 07:19 AM
Doh!

Many thanks! (told you it was silly...)

BrianMH
05-24-2012, 07:28 AM
BTW you used a with statement which shortens what you need to type. You can add one word to that and reduce the code even further.

Dim nrow As Integer

With ActiveWorkbook.Activesheet
nrow = .Cells(Rows.Count, 7).End(xlUp).Row
.Cells(nrow + 2, 7).Formula = "=CountA(G2:G" & nrow & ")"
.Cells(nrow + 2, 7).Font.Bold = True
End With
Don't worry. I have spent many an afternoon hunting for that elusive typo that I just can't see even though it is in front of me :)

Glaswegian
05-24-2012, 07:41 AM
Good one - thanks - much appreciated.

Bob Phillips
05-24-2012, 09:24 AM
BTW you used a with statement which shortens what you need to type. You can add one word to that and reduce the code even further.

You can add another to make it even clearer :)

Dim nrow As Long

With ActiveWorkbook.Activesheet
nrow = .Cells(Rows.Count, 7).End(xlUp).Row
With .Cells(nrow + 2, 7)
.Formula = "=CountA(G2:G" & nrow & ")"
.Font.Bold = True
End With
End With

Glaswegian
05-25-2012, 12:59 AM
Hi xld - thanks, as always, for your suggestions.