none
VBA Macro Resize range RRS feed

  • Question

  • Hi,

    I am using below code to copy the result of query to column H, this process is working but when trying to include the code that copying a date to column J using the last empty row use by copying to H i was encountering an error to this code

         .Range("J" & myLastrow).Resize(Rows.Count) = Format(Now, "MMMM dd, yyyy")

    It say "application - defined or object-defined error" May i know that is lacking with my code. thanks.

    sqlstr = "SELECT e.DAX_ITEMID, e.DAX_MODEL " sqlstr = sqlstr & "FROM [Sheet4$] e " sqlstr = sqlstr & "WHERE e.DAX_ITEMID <> '' " sqlstr = sqlstr & "GROUP BY e.DAX_ITEMID, e.DAX_MODEL;" rec.Open sqlstr, con, 3, 1: DoEvents Dim myNewRow As Long, NumRow As Long Dim x As Long, myLastrow As Long With Sheet6 myLastrow = .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).row .Range("H" & myLastrow).CopyFromRecordset rec

    --got error

    .Range("J" & myLastrow).Resize(Rows.Count) = Format(Now, "MMMM dd, yyyy") End With


    Wednesday, October 1, 2014 7:33 AM

Answers

  • Hi,

    After running your code, I find you just copy 2 columns (column H and I) from Sheet4 into Sheet6 after querying and you want to fill the column J with a date time.

    If I understand correctly, you can use rec.RecordCount as the RowSize parameter of Range.Resize Property in the code. You can refer to the code below. If you use Sheet6.Rows.Count ("1048576") as the RowSize parameter and Range("J" & myLastrow) is not in the first row, using Resize property of Range("J" & myLastrow) with "1048576" will out of the maximum row number in Excel.

    With Sheet6
        myLastrow = .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        .Range("H" & myLastrow).CopyFromRecordset rec
    
        .Range("J" & myLastrow).Resize(rec.RecordCount).Value = Format(Now, "MMMM dd, yyyy")
    End With


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, October 9, 2014 12:24 AM
    Wednesday, October 8, 2014 6:35 AM
    Moderator

All replies

  • Your code seems to be appropriate, at least if you replace your variables with numbers. What are the values of myLastRow and Rows.Count when you debug and mouseover them?

    My guess is that you meant to type .Rows.Count with a leading period?

    I'm not confident that I could predict what would happen if your recordset returned zero records, using the .row.count either, if it were my code I'd probably test that just to be sure.
    • Edited by Keith Ruck Wednesday, October 1, 2014 3:16 PM
    • Proposed as answer by Michal Krzych Wednesday, October 1, 2014 5:12 PM
    Wednesday, October 1, 2014 3:14 PM
  • Hi Keith,

    Already placed a period at .Row.Count but still getting the error.

    My Lastrow value is 967 while .rowscount is 1048576. Hope this is what your looking for.

    varReccount = the result is 118

    Just made modification adding if recordset is  > 1

        varReccount = rec.RecordCount
        If varReccount > 1 Then
            Dim x As Long, myLastrow As Long
            With Sheet6
                myLastrow = .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).row
                Debug.Print .Range("H" & myLastrow).Address
                .Range("H" & myLastrow).CopyFromRecordset rec
                .Range("J" & myLastrow).Resize(.Rows.Count) = Format(Now, "MMMM dd, yyyy")
            
    '           copy date to cloumn J to identify the last transaction
                For x = myLastrow To myLastrow + myNewRow - 1
                    Sheet6.Cells(x, 10).Value = Format(Now, "dd-MMM")
                Next x
            End With
        Else
            Exit Sub
        End If

    Thursday, October 2, 2014 3:30 AM
  • Hi,

    After running your code, I find you just copy 2 columns (column H and I) from Sheet4 into Sheet6 after querying and you want to fill the column J with a date time.

    If I understand correctly, you can use rec.RecordCount as the RowSize parameter of Range.Resize Property in the code. You can refer to the code below. If you use Sheet6.Rows.Count ("1048576") as the RowSize parameter and Range("J" & myLastrow) is not in the first row, using Resize property of Range("J" & myLastrow) with "1048576" will out of the maximum row number in Excel.

    With Sheet6
        myLastrow = .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        .Range("H" & myLastrow).CopyFromRecordset rec
    
        .Range("J" & myLastrow).Resize(rec.RecordCount).Value = Format(Now, "MMMM dd, yyyy")
    End With


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, October 9, 2014 12:24 AM
    Wednesday, October 8, 2014 6:35 AM
    Moderator
  • Thank you very much Luna.
    Thursday, October 9, 2014 12:26 AM