Application defined or object defined error - VBA Code

[CC268]

I keep getting this error on the highlighted line of code below - can't seem to figure out what is wrong. I adapted this from another similar code of mine and I think it isn't like the Copy Destination since it is the same sheet?

Thanks!

Option Explicit


Sub Merge_Data_Rev2()
Call OptimizeCode_Begin


Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
Dim array1, array2, counter1 As Long, counter2 As Long
Dim StartTime As Double
Dim MinutesElapsed As String


Set rng1 = Sheets("Sheet1").Range("B2:B" & Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "B").End(xlUp).row)
Set rng2 = Sheets("Sheet1").Range("M2:M" & Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "M").End(xlUp).row)


array1 = rng1.Offset(0, 4).Resize(, 5).Value 'creating an array by taking rng1 (column B) and offsetting it by 4 (to include up to column F), then resizing to include columns B through F (5 columns total)
array2 = rng2.Offset(0, 5).Resize(, 6).Value 'same as above but using rng2


For counter2 = 1 To UBound(array2) 'for each row in array2 - from row 1 in array 2 to upper bound of array 2 (row 3865)
    For counter1 = 1 To UBound(array1) 'for each row in array1 - from row 1 in array 1 to upper bound of array 1 (row 2390)
    
    'if array 2 -> sheet 1, current row (starting with row 1 of the array), column 1 of the array (Column M, NHA Part Number) equals array 1 -> sheet 1, current row, column 1 of the array (Column B, NHA Part Number) _
     AND if array 2 -> sheet 1, current row, column 5 of the array (Column Q , Part Number) equals array 1 -> sheet 1, current row, column 4 of the array (Column E, Part Number) THEN _
     copy data from current row in array 1 (columns C & F) into current row in array 2 (columns P & R)
    
        If array2(counter2, 1) = array1(counter1, 1) And array2(counter2, 5) = array1(counter1, 4) Then
[COLOR=#ff0000][B]            Sheets("Sheet1").Range("C" & counter1 + 1 & "F" & counter1 + 1).Copy Destination:=Sheets("Sheet1").Range("P" & counter2 + 1 & "R" & counter2 + 1)[/B][/COLOR]
        Exit For
        End If
    Next
Next
     
Call OptimizeCode_End
End Sub

 

[Joe4]

I am guessing that you are trying to build ranges like this:
Range("C1:F10")
Note in your code you are missing the ":" part.
Add that in like:

Sheets("Sheet1").Range("C" & counter1 + 1 & "[B][COLOR=#ff0000]:[/COLOR][/B]F" & counter1 + 1).Copy Destination:=Sheets("Sheet1").Range("P" & counter2 + 1 & "[B][COLOR=#ff0000]:[/COLOR][/B]R" & counter2 + 1)

 

[CC268]

I am guessing that you are trying to build ranges like this:
Range("C1:F10")
Note in your code you are missing the ":" part.
Add that in like:

Sheets("Sheet1").Range("C" & counter1 + 1 & "[B][COLOR=#ff0000]:[/COLOR][/B]F" & counter1 + 1).Copy Destination:=Sheets("Sheet1").Range("P" & counter2 + 1 & "[B][COLOR=#ff0000]:[/COLOR][/B]R" & counter2 + 1)

No...I did in a previous code which this is adapted from...I want to copy JUST the value in column C and column F and copy into Column P and Column R.

 

[Joe4]

The range references you are building are not valid.
Add this line before the line you highlighted in red:

MsgBox "C" & counter1 + 1 & "F" & counter1 + 1

What does that return?
Does that look like a valid range reference you would insert inside Range(...)?
What SHOULD your range reference look like?

 

[CC268]

The range references you are building are not valid.
Add this line before the line you highlighted in red:

MsgBox "C" & counter1 + 1 & "F" & counter1 + 1

What does that return?
Does that look like a valid range reference you would insert inside Range(...)?
What SHOULD your range reference look like?

I thought this might work but I keep getting a "mismatch" error:

Sheets("Sheet1").Cells(counter2 + 1, "P").Value = Sheets("Sheet1").Cells(counter1 + 1, "C").Value And Sheets("Sheet1").Cells(counter2 + 1, "R").Value = Sheets("Sheet1").Cells(counter1 + 1, "F").Value

 

[Joe4]

Can you just explain (in plain English) exactly what that line highlighted in red is supposed to do?
Maybe give us an example with actual ranges so we know what it is supposed to look like?

 

[CC268]

Can you just explain (in plain English) exactly what that line highlighted in red is supposed to do?
Maybe give us an example with actual ranges so we know what it is supposed to look like?

The code is commented right above and spells it out as clear as I can think of?

 

[CC268]

I need to go back to the drawing table on this one....I got the code to work, but it isn't doing what I want to do at all...

 

[Joe4]

The code is commented right above and spells it out as clear as I can think of?

OK. I think I see what you are doing now. I don't think you can copy a multi-cell non-contiguous range to another multi-cell non-contiguous range like that.
Do it in two lines.

So replace your line in red with this:

Sheets("Sheet1").Range("P" & counter2 + 1) = Sheets("Sheet1").Range("C" & counter1 + 1)
Sheets("Sheet1").Range("R" & counter2 + 1) = Sheets("Sheet1").Range("F" & counter1 + 1)

 

[CC268]

OK. I think I see what you are doing now. I don't think you can copy a multi-cell non-contiguous range to another multi-cell non-contiguous range like that.
Do it in two lines.

So replace your line in red with this:

Sheets("Sheet1").Range("P" & counter2 + 1) = Sheets("Sheet1").Range("C" & counter1 + 1)
Sheets("Sheet1").Range("R" & counter2 + 1) = Sheets("Sheet1").Range("F" & counter1 + 1)

Thanks that should work I will give it a shot tomorrow!