How to fix in macro using if condition

Question

Trying to use this in VBA doesn't work gives error where a column contains like this

Aero 1.0
3/24/2019 2:38:08 PM
3/18/2019 2:30:40 AM
3/17/2019 4:06:46 PM
3/11/2019 12:03:20 AM
3/5/2019 9:11:53 AM
3/4/2019 1:20:56 AM
3/3/2019 3:58:11 PM
2/24/2019 3:43:24 PM
Affleck 1.0
3/24/2019 2:38:19 PM
3/18/2019 2:30:52 AM
3/17/2019 4:06:56 PM
3/11/2019 12:03:31 AM

in excel this works

=IF(OR(RIGHT(A5,2)="PM",RIGHT(A5,2)="AM"),F4,A5)

I need to put in macro.

this is for automation

IF(OR(RIGHT(A5,2)="PM",RIGHT(A5,2)="AM"),F4,A5)

here is my coding

Sub folderMerge()

    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim LastRow As Long
    Dim oldfolderpath As String
    oldfolderpath = "C:\Users\dhason\Desktop\GPLM\master.xlsm"
    Dim fol As Folder
    Set fol = fso.GetFolder("C:\Users\dhason\Desktop\GPLM\")
    Dim Postmeridian, Postmeridian, Platform As String
    Postmeridiem = "PM"
    Antemeridiem = "AM"
    For Each fil In fol.Files
    Workbooks.Open fil.Path
    For Each ws In Worksheets
         ws.Activate
         With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
     End With
      Range("f4").Value = "Platform"
     Range("g4").Value = "Date"
     Range("h4").Value = "ODM"
     Range("i4").Value = "date value"
      Range("i5").Formula = "=datevalue(a5)"
     Range("f5").Formula = "=IF(OR(RIGHT((RANGE(A5,2).Value=PM), 
       (RIGHT(RANGE(A5,2)).VALUE=AM)),F4,A5)"
    Range("g5").Formula = "=LEFT(RC[-6],9)" ' second filling
        Range("g5", "g" & LastRow).Select
    Range("g5").Copy
    Range("g5:g" & LastRow).Select
    ActiveSheet.Paste
        c = InStr(Range("A1").Value, " ") 'third filling
    Range("h5").Value = Left(Range("A1"), c)
    Range("A6").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select        
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy
        If ThisWorkbook.Worksheets(1).Range("A2").Value = "" Then
        ThisWorkbook.Worksheets(1).Range("A2").PasteSpecial xlPasteAll
        Else
        ThisWorkbook.Worksheets(1).Range("A1").End(xlDown).Offset(1, 
 0).PasteSpecial xlPasteAll
        end If
        Next
Next
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close True
End If
Next
End Sub

*"doesn't work"* is no useful error description. You need to explain what exactly you tried (include your code) and tell which error you get and in which line. Please read [ask] and how to build a [mcve] of your problem. Your question is a way to broad to answer if we don't know what exactly is going wrong. — Pᴇʜ, May 13 '19 at 13:08
I am trying to use this formula =iF(OR(RIGHT(A5,2)="PM",RIGHT(A5,2)="AM"),F4,A5) where in column a contains values like"Aero 1.0 3/24/2019 2:38:08 PM etc" in vba macro im trying to write formula so that when i run macro the formula is taken by that particular cell and gives values here below is my entire coding — reshmi22 reshmi, May 13 '19 at 13:13
@reshmi22reshmi now you should also add into your question what is wrong with that code. We cannot know if you don't tell us. Which error do you get and in which line? We need an exact error description and much more information about what you are actually doing and where your code fails. • Also please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code. — Pᴇʜ, May 13 '19 at 13:26
i get error on line Range("f5").Formula = "=IF(OR(RIGHT(A5,2)="PM",RIGHT(A5,2)="AM"),F4,A5)" .it doesn't accept the PM or AM within quotes if i comment it other codes are working fine — reshmi22 reshmi, May 13 '19 at 13:29
Try to change it into `Range("f5").Formula = "=IF(OR(RIGHT((RANGE(A5,2).Value=""PM""), (RIGHT(RANGE(A5,2)).VALUE=""AM"")),F4,A5)"` quotes need to be doubled in a string. • And please do your self a favour, even if the rest of your code works you really should read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Your code is a mess because of using `.Select`. — Pᴇʜ, May 13 '19 at 13:30
Ah of course you need to use the formula that already worked in the cells `IF(OR(RIGHT(A5,2)=""PM"",RIGHT(A5,2)=""AM""),F4,A5)` • Also note that if you declare `Dim Postmeridian, Postmeridian, Platform As String` only `Platform` is of type `String` and the other 2 variables are `Variant` you need to specify a type for **every** variable: `Dim Postmeridian As String, Postmeridian As String, Platform As String` • I recommend to activate `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. — Pᴇʜ, May 13 '19 at 13:32
OMG thank you so much i was breaking my head from 3 hours . this really worked IF(OR(RIGHT(A5,2)=""PM"",RIGHT(A5,2)=""AM""),F4,A5) — reshmi22 reshmi, May 13 '19 at 13:36
@reshmi22reshmi Because this is how it works. See the documentation: [Quotation marks in string expressions](https://learn.microsoft.com/en-us/office/vba/access/concepts/criteria-expressions/include-quotation-marks-in-string-expressions). — Pᴇʜ, May 13 '19 at 13:39
X-Y problem. Read the line as date/time variable and then just put the relevant Excel data/time function in the formula to determine if it is am or pm. If you, in the future, intend to do this through VBA you could easily use the VBA date/time functions to do the same thing. — AJD, May 13 '19 at 19:58

How to fix in macro using if condition

0 Answers0