Offset "Application.Match" result to find destination cell

[meisch]

I'll try to be as brief as possible. Here's the rundown.


Setup: I have 2 Workbooks. I'm trying to loop through Workbook#1 and for every cell that contains a blue border, I then want to search for that cell value on Workbook 2 (a result SHOULD always be present). Once the result is found, I then want to offset 1 column on Workbook#2(right next to the match that was found), and place the data from Workbook #1 there.

Problem: The code below stops working after I've stored the match result in the variable(resultM). The data stored in resultM is the row number of the correct match. I'm wondering how I can use that in a range to be able to offset by 1 column. The current error I'm getting is "rr2Dest.value = <Object variable or With block variable not set".

I've tried inserting a With End With block and that didn't work. Did I do it wrong? Because I have no idea what Object variable the error is talking about. Are my variables set correctly? I've been messing with this for days now and have no idea what's wrong! I'm about 2.5 weeks into VBA coding so an explanation would be much appreciated. Please help!!

Private Sub CommandButton3_Click()

Dim testWS As Worksheet
Dim testRange As Range, idCella As Range
Dim alastRow2 As Long, resultM As Long
Dim rr2dest As Range

Set testWS = Workbooks("Test.xlsx").Worksheets("October")                                       'set workbook#2 as testWS
Set testRange = testWS.Columns(1)                                                                               'Workbook#2 - the range my result will be in
alastRow2 = Worksheets("Reruns To Pull").Cells(Rows.Count, "A").End(xlUp).Row         'Workbook#1 - find last row in column A that contains data
Set rr2dest = Nothing

For Each idCella In Worksheets("Reruns To Pull").Range("A1:A" & alastRow2).Cells                'Workbook#1 - for each cell in Column A

        If idCella.Borders.Color = RGB(0, 0, 192) Then                                                               'Workbook#1 - if cells in Col A have a borders.color = RGB(0,0,192) then

            resultM = Application.Match(idCella.Value, testRange, 0)                                        'Search through column A of Workbook#2, find exact match, and store in variable resultM
                                                                                                                                              
                                                                                                                                              
           Set rr2dest.Value = resultM                                                          'my problem is right here.

            
            rr2dest.Value = idCella.Value
            rr2dest.Interior.Color = idCella.Interior.Color                                                 'everything I want to transfer from Column A of Workbook#1 to Column B of Workbook#2
            rr2dest.Borders.Color = idCella.Borders.Color
            rr2dest.Borders.Weight = idCella.Borders.Weight
        
      
        
        End If
    
Next idCella

End Sub

 

[davesexcel]

Set rr2dest.Value = resultM

rr2dest is a range, you can't set it to a value. You have not set the range variable.

 

[meisch]

rr2dest is a range, you can't set it to a value. You have not set the range variable.

I wasn't sure what to set the range to seeing how it depended on the match result. Tried setting it to

Range(resultM).end(xlToRight).Column

but that didn't work. However, I just figured out one solution.

Set rr2dest = testWS.Range("A" & CStr(resultM)).Offset(0, 1)

Everything populated correctly on the 2nd workbook. I didn't realize excel wasn't keeping track of the workbook it was on through the steps of the macro. Used the CStr in combination with the returned row from the match result and the offset put all the correct data into column B on the 2nd workbook. This was driving me insane!