Need help to Loop

[Ashhu]

Hello friends
Need help here,
I am trying to copy Document from specific folder. Doc name will have Part Number, Serial Number. I am trying to copy with combination part number and SL.
Column A will have part number and B with Serial Number. When I loop, its copying all files with part number match and then copying Serial number. But what i need is to copy only the Document which contains both part number and SL.
For Ex: Folder contains Doc with below names
Doc number sample:
90001234_Dec 05
90009568_Sep 05, Dec 05, Oct 03
90009568_Sep 05, Jul 15, Jan 23

When i search for 90009568 (Col A) and Dec 05 (col B)- I should get only one doc "90009568_Sep 05, Dec 05, Oct 03" where as i am getting all three. because all three will have search criteria.

Below is part of code which does this search, what its doing is searching A2 criteria and executing copy, then B2. what is should do is A2:B2 combination together it should search.

Sub CopyFiles_Containing()
    Dim sSrcFolder As String, sTgtFolder As String, sFilename As String
    Dim c As Range, rPatterns As Range
    Dim bBad As Boolean
    Dim File_Name As String
   
    sSrcFolder = "D:\"
    sTgtFolder = "D:\Users\Desktop\New folder\"
   
 
 
    Set rPatterns = ActiveSheet.Range("A2:B500").SpecialCells(xlConstants)

           
        For Each c In rPatterns
       
        sFilename = Dir(sSrcFolder & "*" & c.Text & "*")
       
        If sFilename = "" Then
        c.Interior.ColorIndex = 3
        bBad = True
        Else
            While sFilename <> ""
                FileCopy sSrcFolder & sFilename, sTgtFolder & sFilename
                sFilename = Dir()
            Wend
        End If
   
    Next c
   

End Sub

round about: what we are doing now is we are putting part number * SL to get right file, but our input will be always in separate column and we concatenate first with * at center and put that as search criteria.

Please Help

 

[gyansetu]

is it solved

 

[Ashhu]

No Gyan, can you help?
awaiting someone to look at it, highly unlikely in this forum. thanks for your concern anyway.

 

[Deepak]

No Gyan, can you help?
awaiting someone to look at it, highly unlikely in this forum. thanks for your concern anyway.

The straight answer for the no response - You have failed to attract the attention due to unclear query & missing sample.

Will you pls share the sample worksheet with manual approach as well as snippet of the folder.

 

[Marc L]

Hi !

To find a text part within a text, see InStr function in VBA inner help …​

 

[Ashhu]

The straight answer for the no response - You have failed to attract the attention due to unclear query & missing sample.

Will you pls share the sample worksheet with manual approach as well as snippet of the folder.

you might be right, the situation which i am in really complicated

anyway, question in simpler way,
In my search sheet i have inputs like
Column A = Fruit name and Column B = Month

Apple Jan, Feb, Mar
Orange Mar, Apr, May
Grape May, June, July
Apple Aug, Sep, Oct

My documents names from where i am copying (source folder) will have names like this "Apple_Jan, Feb, Mar", "Orange_Mar, Apr, May" so on....

If i go to folder and search "*apple*" i get two results and
when i search "*apple*Mar* i get one result.
with my current code i am getting two results but i need one.

 

[Marc L]

To concatenate strings, do not forget the & VBA operator …

To search part of a filename using Dir VBA function,
as written in VBA inner help sample, use * wildcard …
​