Object Required Error Copying Values Using Conditional Loop

Question

I am trying to write a loop that will look for a specific value "Fund" on one sheet, copy the following range, find that value on another sheet and then paste it in adjacent to it. I keep getting syntax errors such as object required and subscript out of range. Can someone please help?

Thanks in advance!

Dim wkb As Workbook
Dim b As Long
Dim c As Long
Dim LCellP As Long
Dim LCellCopy As Long

Set wkb = Application.ActiveWorkbook
Set IandC = wkb.Sheets("Instructions and CrossRef")
Set Comparison = wkb.Sheets("Comparison")
Set Previous = wkb.Sheets("Previous")

'Move Previous Flows to Comparison Sheet
With Comparison
LCellCopy = Cells(Rows.Count, "A").End(xlUp).Row
End With

Cells(1, 4) = "Good"
Cells(1, 5) = "Not on Previous"

For b = 1 To LCellP
    v = Cells(b, 1).Value
        If Left(v, 4) = "Fund" Then
                For c = 1 To LCellCopy
                u = Comparison.Cells(c, 1).Value
                    If u = Cells(b, 1).Value Then
                        Comparision.Range(Cells(c, 4), Cells(c + 15, 5)) = Previous.Range(Cells(b, 1), Cells(b + 15, 2)).Value
                    End If
                Next c
        End If
Next b

Which line is raising the error? You can't get *different* errors in the same routine, unless you're skipping over the first error. The code will always break at the first error. So let's see what that is and try to diagnose it from there. — David Zemens, Sep 16 '14 at 22:56
You may also want to experiment with the range `.Find` method. If you truly don't know where the cell might be found, that would be preferable. If you know which row or column, then you can use that or the `WorksheetFunction.Match` function. — David Zemens, Sep 16 '14 at 22:58
Thanks for the advice David! I will play around with what you said and see if I can figure it out. Cheers, Cam — user3822304, Sep 17 '14 at 20:59

score 1 · Answer 1 · answered Sep 16 '14 at 23:01

Your With Comparison block doesn't actually resolve anything back to that worksheet. Try this (notice the . preceding the Cells and Rows, this means that these belong to the With Comparison object.

'Move Previous Flows to Comparison Sheet
With Comparison
    LCellCopy = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

That snippet shouldn't cause any errors, but I wanted to point it out.

On a related note, any time you refer to an unqualified Range or Cells object, it always defaults to the ActiveSheet, so:

Range("A1")

Is equivalent to:

ActiveSheet.Range("A1")

Likewise:

Cells(1, 3)

Is equivalent to:

ActiveSheet.Cells(1,3)

So this becomes a problem when you are mixing your qualifications. If Sheet1 is active, this will always raise an error:

Worksheets("Sheet2").Range(Cells(1,1), Cells(1,3)).Value

Why?? Because even though Cells is inside a parentheses, it is being evaluated in the context of the ActiveSheet, which may not be the same as the qualifying "Sheet2", it's essentially saying:

Worksheets("Sheet2").Range(Worksheets("Sheet1").Cells(1,1), Worksheets("Sheet1").Cells(1,3)).Value

And since the range defined on Sheet1 does not, and cannot exist on Sheet2, that raises the familiar 1004 error.

So, although I don't know exactly which line is causing you problems, I suspect it is realted to poorly-qualified or unqualified range objects.

score 0 · Answer 2 · answered Sep 17 '14 at 21:54

Got it working with the below, probably not the best but thanks!

If u = Cells(b, 1).Value Then
    Sheets("Previous").Select
    Range(Cells(b, 1), Cells(b + 15, 2)).Select
    Selection.Copy
    Sheets("Comparison").Select
    Range("D" & c).Select
    ActiveSheet.Paste
    Sheets("Previous").Select
End If

Object Required Error Copying Values Using Conditional Loop

2 Answers2