CODEWITHSUNDEEP
excelvbaformula
user183239
user183239

Reputation: 123

VBA formula error

When trying to run the following code, I get the error application or object-defined error. Can anyone detect the error? When I output the formula as a string, everything seems fine. When I try to use it in a formula however it does not work. I've been looking at it for hours and have tried anything I could come up with.

An example of the what is in the column createdColumn: Wed, 26 Nov 2014 10:28:13 +0100

    Dim createdColumn, updatedColumn, resolvedColumn As Integer

    createdColumn = GetColumnValueByName("created")
    updatedColumn = GetColumnValueByName("updated")
    resolvedColumn = GetColumnValueByName("resolved")

   Sheets("Current Data").Select
   ActiveSheet.Columns(createdColumn).Select
   Selection.Offset(0, 1).Select
   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   ActiveSheet.Cells(2, createdColumn + 1).Select
   ActiveCell.FormulaR1C1 = _
    "=LEFT(RIGHT(R[0]C[-1],LEN(R[0]C[-1])-5), LEN(R[2]C[-1])-5-6)"


    Dim dateArr As Variant
    Dim tmp As String
    tmp = Chr(34) & "1" & Chr(34)
    dateArr = Split(ActiveSheet.Cells(2, createdColumn + 1).Value, " ")
    ActiveSheet.Cells(2, createdColumn + 2).Select
    ActiveCell.FormulaR1C1 = _
        "=DATE(" & dateArr(0) & ";MONTH(DATEVALUE(" &_ 
        dateArr(1) & "&" & tmp & "));" & dateArr(2) & ") + " & dateArr(3)

Thanks in advance!

Upvotes: 2

Views: 148

Answers (2)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

If all you want is to reformat the date, there is a rather simple method. You can use something like this, which avoids your needing to do the Split:

ActiveCell.FormulaR1C1 = Format(ActiveSheet.Cells(2, createdColumn + 1).value, "d/m/yyyy hh:mm:ss")

Or to correct the syntax in what you were trying, you would use this:

ActiveCell.FormulaR1C1 = dateArr(0) & "/" & Month(DateValue("01 " & dateArr(1) & " 2014")) & "/" & dateArr(2) & " " & dateArr(3)

Upvotes: 0

Chrismas007
Chrismas007

Reputation: 6105

This is the syntax I found when using a Split:

Dim dateArr() As String
dateArr() = Split(blah)

then if you need to convert string to date you might have to use cdate(dateArr(#))

Upvotes: 1

Related Questions