Excel VBA Me.Range gives contradictory results. Why?

0

I have 2 Me.Range codes in Excel VBA that I’m testing:

 With Me.Range("o4", Range("o" & Rows.Count).End(xlUp))

        .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
        '.Value = .Value

      End With

        With Me.Range("p4", Range("p" & Rows.Count).End(xlUp))

            .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
            '.Value = .Value

        End With

What it supposed to do is copy the formula all the way down to my last row in Excel. And yet for some unexplained reason. The first one works fine but the other one went the complete opposite direction. Both of these are 100% identical save for the range. Why?

Full Macro

Sub syncSQL()

On Error GoTo EH

Dim sht As Worksheet
Dim conn As New ADODB.Connection
Dim iRowNo As Integer, lastRow As Long, last50 As Range
Dim proj, inv, desc, edt, dt, time, details, stat, rmks, loc, okng, astk, pstk, pic, flag As String
Dim sconnect As String, ssqlstring As String
Dim rs As New ADODB.Recordset
Dim sSQLQry As String, sSQLdel As String, sSQLupd As String
Dim ReturnArray
Dim sForm As String
Dim lRow As Long
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheet7

sForm = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"

 With Me.Range("o4", Range("o" & Rows.Count).End(xlUp))

    .Value = .Value

 End With

With Worksheets("Entry Form")

    'Open a connection to SQL Server
    sconnect = "driver={SQL Server};server=server;database=SQLIOT;uid=admin;pwd=admin"
    conn.Open sconnect

    'Skip the header row
    iRowNo = 4

    'Loop until empty cell
    Do Until .Cells(iRowNo, 1) = ""

        If .Cells(iRowNo, "o").Value = 0 Then
        proj = "'" & .Cells(iRowNo, 1) & "'"
        inv = "'" & .Cells(iRowNo, 2) & "'"
        desc = "'" & .Cells(iRowNo, 3) & "'"
        edt = "'" & Format(.Cells(iRowNo, 4), "yyyy-mm-dd") & "'"
        dt = "'" & Format(.Cells(iRowNo, 5), "yyyy-mm-dd") & "'"
        time = "'" & TimeSerial(Hour(.Cells(iRowNo, 6)), Minute(.Cells(iRowNo, 6)), Second(.Cells(iRowNo, 6))) & "'"
        details = "'" & .Cells(iRowNo, 7) & "'"
        stat = "'" & .Cells(iRowNo, 8) & "'"
        rmks = "'" & .Cells(iRowNo, 9) & "'"
        loc = "'" & .Cells(iRowNo, 10) & "'"
        okng = "'" & .Cells(iRowNo, 11) & "'"
        astk = "'" & .Cells(iRowNo, 12) & "'"
        pstk = "'" & .Cells(iRowNo, 13) & "'"
        pic = "'" & .Cells(iRowNo, 14) & "'"
        flag = "'" & .Cells(iRowNo, 15) & "'"

        'Replace single quote with 2 single quotes
        details = "'" & Replace(.Cells(iRowNo, 7), "'", "''") & "'"

        sSQLQry = "select * from [SQLIOT].[dbo].[ZDIE_MAINT_ENTRY] where [Project No] = " & proj & " And [Inv No] = " & inv & " And [Description] = " & desc & " And [Entry Date] = " & edt & " And [Date] = " & dt & " and [Time] = " & time & " and [Problem + Repair Details] = " & details & " and [Status] = " & stat & " and [Remarks] = " & rmks & " and [Location] = " & loc & " and [Measurement (OK/NG)] = " & okng & " and [Accumulative Stroke] = " & astk & " and [Preventive Stroke] = " & pstk & " and [PIC] = " & pic & " and [Flag] = " & flag & ""
        rs.Open sSQLQry, conn, adOpenForwardOnly, adLockReadOnly

        'insert new record if doesn't exist
        If rs.EOF Or rs.BOF Then

            'generate & execute sql, import excel rows to sql table
            ssqlstring = "insert into [SQLIOT].[dbo].[ZDIE_MAINT_ENTRY]([Project No], [Inv No], [Description], [Entry Date], [Date], [Time], [Problem + Repair Details], [Status], [Remarks], [Location], [Measurement (OK/NG)], [Accumulative Stroke], [Preventive Stroke], [PIC], [Flag]) values (" & proj & ", " & inv & ", " & desc & ", " & edt & ", " & dt & ", " & time & ", " & details & ", " & stat & ", " & rmks & ", " & loc & ", " & okng & ", " & astk & ", " & pstk & " , " & pic & " , " & flag & ")"
            conn.Execute ssqlstring

            'update flag status to from 0 -> 1
            '.Cells(iRowNo, "o").Value = 1

        End If

        rs.Close

        End If

        iRowNo = iRowNo + 1

      Loop

      With ws
        lRow = .Range("o" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("o4:o" & lRow)
        With rng
            .Formula = sForm
        End With

        lRow = .Range("p" & .Rows.Count).End(xlUp).Row
        Set rng = Range("p4:p" & lRow)
        With rng
            .Formula = sForm
        End With
      End With


      'With Me.Range("o4", Range("o" & Rows.Count).End(xlUp))

      '  .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
        '.Value = .Value

      'End With

      '  With Me.Range("p4", Range("p" & Rows.Count).End(xlUp))

      '      .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
            '.Value = .Value

      '  End With

    'MsgBox "Data imported."

    conn.Close
    Set conn = Nothing

End With

Exit Sub

EH:

 MsgBox Err.Description
 Debug.Print sSQLQry
 Debug.Print ssqlstring

End Sub