Error:

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings again,

I am receiving an error "wrong number of arguments or invalid property assignment" on the line below:

Code:
Set Fnd = MAIN.Worksheets("M_" & AC).Range.Find(What:=COM.Worksheets(AC).Cells(j, 1).Value, After:=MAIN.Worksheets("M_" & AC).Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

Also, some of the variables I am using included in this line:

Code:
Dim Fnd As Range
Set COM = Workbooks("COMM_COMBINED.xls")
Set MAIN = Workbooks("COMM_LD_MAIN.xls")

Code:
j = 3

For Each ws In COM.Worksheets    
    AC = ws.Name
    
    Do While COM.Worksheets(AC).Cells(j, 1) <> ""
    
        Set Fnd = MAIN.Worksheets("M_" & AC).Range.Find(What:=COM.Worksheets(AC).Cells(j, 1).Value, After:=MAIN.Worksheets("M_" & AC).Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
        
        If Not Fnd Is Nothing Then
        
            MAIN.Worksheets("M_" & AC).Range(Fnd, Fnd.End(xlToRight)).Copy
            COM.Worksheets(AC).Activate
            Cells(j, 1).Select
            Selection.End(xlToRight).Offset(0, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
        End If
        
        j = j + 1
        
    Loop
    
    COM.Worksheets(AC).Activate
            Cells(j, 1).Select
            b = Selection.End(xlToRight).Offset(0, 1).Column
    
    Next ws

My intention with this chunk is to loop through dates on one worksheet to test whether or not the same date is included on another sheet by a similar name in a different workbook. If there are matching dates then I would like to copy the range from the MAIN workbook using the current fnd range variable and paste it on another sheet.

Thanks for all your help and input!
 
I did rewrite it with a set of loops and it seems to be working well and doing so within a reasonable amount of time.

I am glad you worked out that you needed another level of looping ... it wasn't clear from your previous posts that there might be multiple find results for each value of COMVAR.

I am guessing that you want to start at row 3 in each worksheet? If so, you'll need j=3 inside the For each ws ... loop

You can also do this more efficiently by using AutoFilter. For each value of COMVAR, filter for that value and simply copy all the filtered results in one go. You can use the macro recorder to help you with the syntax. It will be much faster than comparing values line by line.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top