CODEWITHSUNDEEP
vbaexcel
Armia
Armia

Reputation: 11

Write Excel Formula in VBA Coding Error "1004"

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

Click to see the Excel Formula Solution

..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?

Upvotes: 1

Views: 127

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 0

Related Questions