Excel Formula as VBA

[sg2209]

Hi Friends,

Can you please review the below code and do let me know where i am lacking.

Column C has the dates and i am trying entering the formula " =IF( OR(RIGHT(C2,3) = " PM",RIGHT(C2,3) = " AM"),A1, CONCATENATE(LEFT(A1,LEN(C2)-2)," ",RIGHT(C2,2))) " to add space between timestamp

6:18PM needs to be come up as 6:18 PM however the highlighted part in RED throws an error ,

also this should be autofill till the last row has the data Column A
Please help

Sub CMS_Avaya()


Dim C As Integer


C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column


Do Until C = 0


If WorksheetFunction.CountA(Columns(C)) = 0 Then


Columns(C).Delete


End If


C = C - 1


Loop


'Insert Columns


Range("E:G").EntireColumn.Insert


'Insert Headers


Range("E1").Value = "Time In"
Range("F1").Value = "Time Out"
Range("G1").Value = "Log In Date"


Worksheets("Sheet4").Range("E1:G1").Font.Bold = True
Worksheets("Sheet4").Range("E2").Formula="=IF( OR(RIGHT(A1,3) = " PM",RIGHT(A1,3) = " AM"),A1, CONCATENATE(LEFT(A1,LEN(A1)-2)," ",RIGHT(A1,2)))"
End Sub

<tbody>
</tbody>

 

[Joe4]

The issue is that the quotes in your formula are getting confused with the quotes that VBA uses to denote what is literal text and what is VBA.
One easy way to get the formula you have on a worksheet working correctly in VBA is to turn on the Macro Recorder, and then record yourself entering the formula on your sheet.
That will give you VBA code that will create that formula without errors.

 

[sg2209]

and what about autofill till the last row when its get recorded do we have that option too ??

 

[Joe4]

and what about autofill till the last row when its get recorded do we have that option too ??

Not quite, but that part is easy enough to add in. We just need to figure out the last row we need to apply it to.
The formula that you record will probably be in R1C1 format. So all we need to do is change the formula from something like this:

Range("E2").FormulaR1C1 = "...

to

Range("E2:E" & lastRow).FormulaR1C1 = "...

where "lastRow" is a variable we used to calculate where the last row resides.

If you are looking at column D to determine that, it would look something like:

lastRow = Cells(Rows.Count,"D").End(xlUp).Row