Excel VBA Me.Range gives contradictory results. Why?

Question

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

Your code will only work to fill in the column if the column already has something in it. In other words, if say column O only has cell 4 populated, that's the only cell that will get a formula added to it. You should probably be using a different column to determine the row to fill to. — Rory

Siddharth Rout Siddharth Rout · Accepted Answer · 2018-04-12T07:05:44

It is better to fully qualify your objects. Notice the DOTS before the range and row?

Sub SampleA() '<~~ UNTESTED
    Dim ws As Worksheet

    Set ws = Sheet1

    With ws
        With .Range("o4", .Range("o" & .Rows.Count).End(xlUp))
            .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
        End With

        With .Range("p4", .Range("p" & .Rows.Count).End(xlUp))
            .Formula = "=IF(OR(ISBLANK(A4), ISBLANK(E4), ISBLANK(F4), ISBLANK(G4), ISBLANK(H4)), """", ""1"")"
        End With
    End With
End Sub

Even better would be to work with variables and objects. See this

Sub SampleB() '<~~ UNTESTED
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim sFormula As String

    Set ws = Sheet1

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

    With ws
        lRow = .Range("O" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("O4:O" & lRow)
        With rng
            .Formula = sFormula
            .Value = .Value
        End With

        lRow = .Range("P" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("P4:P" & lRow)
        With rng
            .Formula = sFormula
            .Value = .Value
        End With
    End With
End Sub

Excel VBA Me.Range gives contradictory results. Why?

1 Answers