CODEWITHSUNDEEP
vbaexcelexcel-2010
joshc
joshc

Reputation: 13

VBA, Creating Formula to go down the bottom of a column with data

I'm extremely new at VBA, however using google and in particular stackoverflow I have developed some reasonable skills in VBA and managed to accomplish a lot in no more than two weeks.

I'm trying to get VBA script to paste a formula down column A from cell 2, the formula currently used is =TEXT(H2,"ddmmyy")&LEFT(B2,1)&C2 I have tried several different ways but get syntax errors and compile errors. My latest attempt is as follows.

    Sub FillDown()

        Dim strFormulas(1 To 1) As Variant

        With ThisWorkbook.Sheets("Sheet1")
            strFormulas(1) = "=TEXT(H2,"ddmmyy")&LEFT(B2,1)&C2"


            .Range("C2:E2").Formula = strFormulas
            .Range("C2:E11").FillDown
        End With
    End Sub

I get an error on the "ddmmyy" (compile error: Syntax error) , it takes the numbers and places them in front of someone's name to create unique ID's. I apologise as this might be a bad question, but I'm fairly new and achieved and learnt so much already, and I want to keep learnings VBA at FANTASTIC pace.

Upvotes: 1

Views: 118

Answers (2)

Slai
Slai

Reputation: 22876

Just use the Record Macro and check the code

ThisWorkbook.Sheets("Sheet1").Range("C2:E11").Formula = "=LEFT(B2)&C2&TEXT(H2,""ddmmyyyy"")"

Upvotes: 0

DAXaholic
DAXaholic

Reputation: 35358

You can fix your syntax error by replacing the line with 

strFormulas(1) = "=TEXT(H2,""ddmmyy"")&LEFT(B2,1)&C2"

The problem was that "ddmmyy" terminates your formula string into the two strings "=TEXT(H2," and ")&LEFT(B2,1)&C2" and ddmmyy is tried to interpret as some kind of valid VBA code. That whole statement is of course not valid VBA code therefore the error. If you want to include " within a string then you have to use a pair of them to escape the character.

Upvotes: 1

Related Questions