Error:

[SBF12345]

Greetings again,

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

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:

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

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!

 

[StephenCrump]

Set Fnd = MAIN.Worksheets("M_" & AC).[COLOR=#ff0000][B]Range[/B][/COLOR].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)

You haven't correctly defined the Range where you want the Find to look?

Perhaps UsedRange? Or Range("A:A") to look only in column A?

 

[SBF12345]

Apologies,

I'm not following...Are you saying the "After" item is not correctly defined?

I just tried to run with the following line and received an "object doesn't support this property or method" error?

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

 

[SBF12345]

Quick clarification...I did not intend to put a "?" after the word "error" in the above post.

 

[Norie]

The problem is with the highlighted part of the 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)

What range do you actually want to look for the value in?

 

[SBF12345]

The range that may contain a value defined in the "What" item is within the range MAIN.Worksheets("M_" & AC).Range("A:A"). I am receiving an object doesn't support this property or method. Have I incorrectly defined some of the properties in find?

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

 

[SBF12345]

This seems to be missing matches in date values for some reason. It misses one matching date while recognizing and copy/pasting another just a few lines below. The date formatting is the same. I'm not sure what to think...

COMVAR = COM.Worksheets(AC).Cells(j, 1)    
        Set Fnd = MAIN.Worksheets("M_" & AC).Range("A:A").Find(What:=COMVAR, LookIn:=xlValues)

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

 

[StephenCrump]

There are a few possible reasons why your Find might not be working ....

- Are you looking for dates (i.e. numbers that are valid dates) or strings that look like dates? Are you sure you have an exact match, e.g. if you are trying to find strings, there are no trailing spaces? (The date 1 Nov 2017 will not match the string "1 Nov 2017", nor will the string "1 Nov 2017" match the string "1 Nov 2017 ").

- You haven't specified the LookAt: argument? Do you want the Find to match the entire cell contents, or just part of the cell contents?

VBA Help notes that:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

 

[SBF12345]

I formatted both of the cell columns that are used as the basis for the find.

 MAIN.Worksheets("M_" & G).Activate    
    Range("A:A").Select
    Selection.NumberFormat = "mm/dd/yyyy;@"

COM.Worksheets(G).Range("A:A").Select
    Selection.NumberFormat = "mm/dd/yyyy;@"

I set a watch for the variables j and Fnd. j is looping like it should but Fnd maintains a "nothing" value. Because the j value runs through cells with matching pairs in the other sheet I am led to believe the Fnd statement is still not working though it doesn't catch an error.

I ran it with this find statement most recently with no luck:

Set Fnd = MAIN.Worksheets("M_" & AC).Range("A:A").Find(What:=COMVAR, LookIn:=xlValues, LookAt:=xlWhole)

 

[SBF12345]

While to early to declare victory entirely 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. Here it is...

j = 3    
    For Each ws In COM.Worksheets
    
    AC = ws.Name
    
    Do While COM.Worksheets(AC).Cells(j, 1) <> ""
    
        Set COMVAR = COM.Worksheets(AC).Cells(j, 1)
        
        z = 4
        
        Do While MAIN.Worksheets("M_" & AC).Cells(z, 1) <> ""
        
            Set MAINVAR = MAIN.Worksheets("M_" & AC).Cells(z, 1)
            
            If MAINVAR = COMVAR Then
                
                MAIN.Worksheets("M_" & AC).Range(MAINVAR, MAINVAR.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
                
                z = z + 1
                
                Loop

  j = j + 1
        
    Loop