CODEWITHSUNDEEP
vbaexcelexcel-formula
Batyr Nazarov
Batyr Nazarov

Reputation: 51

Formula with quotes into cell using VBA throws error

I have a code below which concatenate Values in A and B columns. When I want to add an empty space between A and B which is an Excel formula =CONCATENATE(A4," ",B4) or =A4 & " " & B4 the macro would flag it as an error when using VBA:

With ws3.Range("E4:E" & LastRow3)
    .Formula = "=A4&B4"
End With

Upvotes: 3

Views: 341

Answers (4)

Vityata
Vityata

Reputation: 43565

"Translating" a formula from Excel to VBA is probably the most popular task that a person has to do in VBA. This is an algorithm for it:

  • Write the formula, so it works in Excel;
  • Select it manually;
  • Run this:
Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub
  • It prints the formula as it should look like in the immediate window;
  • Copy it;

If you follow these steps with =CONCATENATE(A4," ",B4), it prints "=CONCATENATE(A4,"" "",B4)"

If you are using local Excel formulas, e.g. a Russian Excel, where =CONCATENATE() is =СЦЕПИТЬ(), in order to get the local formula printed you should write Selection.FormulaLocal instead of Selection.Formula.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

This appears to work:

Sub jhgfd()
    Set ws3 = ActiveSheet
    LastRow3 = 10

    With ws3.Range("E4:E" & LastRow3)
        .Formula = "=A4 & CHAR(32) & B4"
    End With
End Sub

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37337

You could do this alternatively:

For i = 4 to LastRow3
    Cells(i, 5).Value = Cells(i, 1).Value & " " & Cells(i, 2).Value
Next

This is simple loop, that loops thorugh fifth (E) column, rows between 4 and LastRow3 and sets the value of this cell to concatenated A and B columns in the same rows.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57673

Quotes within quotes need to be doubled

.Formula = "=A4 & "" "" & B4"

and this will result in =A4 & " " & B4 as a formula.

Upvotes: 3

Related Questions