Using an Excel function in VB question

Question

So today a received an excel spreadsheet with over 20,000 cells onto it. I had to separate them by name (ever 14 rows starts a new client), and total the columns of prices. So, I wrote:

Sub NewSpace()
'
' NewSpace Macro
'
' Keyboard Shortcut: Ctrl+p
'
        'create the count variable, initilize it to 17
        Dim count As Integer
        count = 17

    While count <= 30003

            'add first row
            Rows(count & ":" & count).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

            'add second row
            Rows(count & ":" & count).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

            'Select cell A in the first created row, write "Total", and format properly
            Range("A" & count).Select
            ActiveCell.FormulaR1C1 = "Total"
            With ActiveCell.Characters(Start:=1, Length:=5).Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleNone
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
            End With

            'Select cell H in the first created row, sum up the values
            Range("H" & count).Select
            ActiveCell.FormulaR1C1 = "=sum(H" & (count - 15) & ":H" & (count - 1) & ")"
            With ActiveCell.Characters(Start:=1, Length:=5).Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleNone
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
            End With


    'increment the counter by 16, start again
    count = count + 16

    Wend
End Sub

However, I keep getting a #NAME error on this line: ActiveCell.FormulaR1C1 = "=sum(H" & (count - 15) & ":H" & (count - 1) & ")"

Everything there looks fine to me, and honestly i'm not to sure what I have to change.

EDIT: I also forgot to mention, in the #NAME error, the formula shows correct, however it adds ' to the function, like so: =SUM('H__':'H__')

Bart · Accepted Answer · 2010-09-17 12:58:43Z

3

Try ActiveCell.Formula instead of ActiveCell.FormulaR1C1...

.FormulaR1C1 is for different style of addressing cells

ActiveCell.Formula = "=A1"

and

ActiveCell.FormulaR1C1 = "=R1C1"

refers to the same cell.

answered Sep 17, 2010 at 12:58

Bart

2,2062 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AgainstClint Over a year ago

That worked exactly like it should have. Thanks for the help Bart.

Collectives™ on Stack Overflow

Using an Excel function in VB question

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related