0

I am trying to create a relatively simple copy and paste procedure. I have certain cell addresses stored in an array (Results1()) and I am trying to extrapolate the row which it is in to copy an entire row of data into another Excel sheet. I have the following so far and currently I am getting an object required error when I try to define what my 'NextRow' is. Any advice or feedback would be greatly appreciated!

For i1 = LBound(Results1) To UBound(Results1)
    Set NextRow = Worksheets("searchresult").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                Range(Results(i1)).EntireRow.Copy NextRow
Next i1

Editted code

    For i1 = LBound(Results1) To UBound(Results1)
        Worksheets("properties").Select
        Set p1results = Range(Results1(i1))
        p1results.EntireRow.Copy
            With Worksheets("SearchResult").Select
                NextRow = Range("D65536").End(xlUp).Row + 1
                Cells(NextRow, 1).Select
                ActiveSheet.Paste
            End With
    Next i1

Update: In actuality my data ranges only range from columns D:P so when I count rows I count from column D (4) because columns A-C are empty ranges. Would this affect the way it is pasting my data?

       On Error Resume Next
   For i1 = LBound(Results1) To UBound(Results1)
        Set NextRow = shSearchResult.Cells(shSearchResult.Rows.Count, 4).End(xlUp).Offset(1, 0)
        shProperties.Range(Results1(i1)).EntireRow.Copy NextRow
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "Bad address" & Results1(i1)
        End If
    Next i1
Improve this question

2 Answers 2

Reset to default
1

A few potential causes here:

Your for loop references Results1 but your copy references Results (typo in question? or route cause?)

In your Set NextRow = line references .Cells(Rows.Count, unqualified, Rows refers to the active sheet not "searchresult" sheet. Probably not what you intended, but also will meke no difference in this case.

Likewise Range(Results(i1)) refers to the active sheet, may be ok - depends on the wider context of your application

I suspect that your imediate problem is that NextRow has not been DIM'd. Try adding

Dim NextRow as Range to your Sub

In general it is good practice to use Option Explicit (first line in your module). This forces explicit declaration of all variable.

EDIT

based on your edit a "Method 'Range' of object '_Global' failed" error may be caused by an invalid adress string

Your first code was actually better than your second. Here's a refactor version

Sub zxx()
    Dim Results1(0 To 1) As Variant
    Dim i1 As Long
    Dim NextRow As Range
    Dim shProperties As Worksheet
    Dim shSearchResults As Worksheet


    '  other code ...

    Set shSearchResults = ActiveWorkbook.Worksheets("searchresult")
    Set shProperties = ActiveWorkbook.Worksheets("properties")

    On Error Resume Next
    For i1 = LBound(Results1) To UBound(Results1)
        Set NextRow = shSearchResults.Cells(shSearchResults.Rows.Count, 1).End(xlUp).Offset(1, 0)
        shProperties.Range(Results1(i1)).EntireRow.Copy NextRow
    Next i1


End Sub

To detect bad addresses try adding On Error Resume Next before the For and

        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "Bad Address " & Results1(i1)
        End If

after the copy (inside the for loop)

EDIT2

Copying an EntireRow into a single cell only works if the single cell is in column A because because the EntireRow range overhangs the right hand side of the sheet.

Use this to offset back to column A

Set NextRow = _
    shSearchResults.Cells(shSearchResults.Rows.Count, 4).End(xlUp).Offset(1, -3)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@ren Glad to help, and welcome to SO. BTW if an answer solves your problem you should accept it 9click the tick) That how SO works
Chris, thanks for the suggestion, but when I try to run the code I am getting an 'object required' error on the 'Set NextRow=...' line. Any explanations on why this might be?
@ren do you have the Dim NextRow as Range in place? 'object required' error means NextRow is not an object. Without a Dim (and no Option Explicit) variables default to Variant
@Chris Neilson ya I just realized I had to change my variable definition for NextRow. Trying to debug a massive code is rather time consuming. The macro isn't crashing anymore but it is not displaying any search results currently. It's an improvement at least!
@ren progress then... feel free to post another question on other aspects of your code (in answering I did a mockup and it does copy the row addressed in Results1. If its not giving the expected results I guess the addresses contained in Results1 are suspect)
|
0

You never use the With Statement, so just do this instead:

For i1 = LBound(Results1) To UBound(Results1)
    Worksheets("properties").Select
    Set p1results = Range(Results1(i1))
    p1results.EntireRow.Copy
    Worksheets("SearchResult").Select
    NextRow = Range("D65536").End(xlUp).Row + 1
    Cells(NextRow, 1).Select
    ActiveSheet.Paste           
Next i1

or better:

For i1 = LBound(Results1) To UBound(Results1)
    Worksheets("properties").Range(Results1(i1)).EntireRow.Copy
    Worksheets("SearchResult").Cells(Range("D65536").End(xlUp).Row + 1, 1).Paste
Next i1

Depending on what you're doing, you might want to use PasteSpecial instead.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.