Write Excel Formula in VBA Coding Error "1004"

Question

I keep getting Excel VBA Error "1004" on executing the following formula..

..in VBA Code

For i = 5 To lastrow
      ws2.Cells(i, lastcolumn + 1).Formula = "=DATEVALUE(INDIRECT(LEFT($F$1,1)" & i & "))+TIMEVALUE(INDIRECT(LEFT($H$1,1)" & i & "))"
      ws2.Cells(i, lastcolumn + 1).NumberFormat = "dd.mm.yyyy hh:mm:ss"
Next i

Does anyone know how to make sure to write the VBA Code correctly?

Scott Craner · Accepted Answer · 2017-08-22 12:42:56Z

1

If i was 1 your formula resolves to

=DATEVALUE(INDIRECT(LEFT($F$1,1)1)) + +TIMEVALUE(INDIRECT(LEFT($H$1,1)1))

You are missing the & in the formula.

For i = 5 To lastrow
      ws2.Cells(i, lastcolumn + 1).Formula = "=DATEVALUE(INDIRECT(LEFT($F$1,1) & " & i & "))+TIMEVALUE(INDIRECT(LEFT($H$1,1) & " & i & "))"
      ws2.Cells(i, lastcolumn + 1).NumberFormat = "dd.mm.yyyy hh:mm:ss"
Next i

answered Aug 22, 2017 at 12:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Comments

Vityata · Accepted Answer · 2017-08-22 12:42:55Z

0

Click on the formula cell in N5, then run this code:

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

Take a look at the immediate window and fix your formula correspondingly.

answered Aug 22, 2017 at 12:42

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Write Excel Formula in VBA Coding Error "1004"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related