1

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.

Community
  • 1
  • 1
joshc
  • 13
  • 4
  • 1
    Sorry, It SHOULD take the numbers and place then in the first row to create the unique ID for someone, eg Jcott30081983, as my name and birthday used it would look like this.Thanks – joshc Jul 14 '16 at 04:12

2 Answers2

1

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.

DAXaholic
  • 33,312
  • 6
  • 76
  • 74
  • Right, Understood. Thanks very much for your help, my interview is in two days and this will complete a fairly large project I have undertaken at work. as simple as it may seem this is the last step and my brain would not figure it out. Thankyou very very much – joshc Jul 18 '16 at 11:09
0

Just use the Record Macro and check the code

ThisWorkbook.Sheets("Sheet1").Range("C2:E11").Formula = "=LEFT(B2)&C2&TEXT(H2,""ddmmyyyy"")"
Slai
  • 22,144
  • 5
  • 45
  • 53