Search workbook for keyword - copy column(s) to other workbook (Extremely New to VBA)

[tolocdn]

Hello all,

(Very first post and as mentioned extremely new to VBA - which programmers tell me will melt my brain )

I have some code already that works to open a workbook and search for a keyword and copy the cells next to that word. Now I have a need to search designated workbooks for a keyword and copy the entire column of data (or last cell in that column that contains a value).

Example:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Speed 1[/TD]
[TD]Condition Count[/TD]
[TD]Speed 2[/TD]
[TD]Condition Count[/TD]
[TD]Total Counts[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]456[/TD]
[TD]4[/TD]
[TD]654[/TD]
[TD]6[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]789[/TD]
[TD]6[/TD]
[TD]345[/TD]
[TD]7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]234[/TD]
[TD]4[/TD]
[TD]345[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD]1379[/TD]
[TD][/TD]
[TD]1344[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]










I would have to search the workbook (which is really only 1 sheet in csv format) for the word Speed and then return the last cell values (1379 and 1344 in this case). The book it is searching can see anywhere from 3-8 instances of the keyword.

Thanks in advance!

Dave

 

[AlphaFrog]

Here's a good step-by-step guide on the .Find method.

The .Find method

The bottom of the article shows code to do a search for multiple occurrences.

 

[tolocdn]

Thanks AlphaFrog! I have the search part down ok - what I'm stuck on is how do I select the columns (or preferably the last value in those columns) so I can paste them into the original workbook.

A little more info: Workbook X asks users to select 2 other workbooks (Y and Z) to search for values belonging to a client. The wb X then opens and searches the Y and Z wbs to search for the client names and return totals for pre-determined titles, but variable secondary values. These titles can range from 1 to 8 in occurrence. For example, one client can have BBBB 1, BBBB 2, BBBB 5 and another client can have BBBB 0, BBBB 4, BBBB 7.

 

[AlphaFrog]

Thanks AlphaFrog! I have the search part down ok - what I'm stuck on is how do I select the columns (or preferably the last value in those columns) so I can paste them into the original workbook.

If R is the .Find range variable, this will copy last used cell in R's column.

Cells(Rows.Count, R.Column).End(xlUp).Copy

 

[tolocdn]

AlphaFrog thanks again for your super awesome help. I've gotten a lot further along but now have another related question kicking my butt. I've included the code that is doing me in. The following Error 91's out at the search for "Case". I thought using Find it would just skip the search if it could not find the word? The issue here is that all the workbooks being searched may not contain the all the values being searched for. Any help from anyone would be GREATLY appreciated.

The

ActiveCell = Range("A1").Select

part just ensures I'm going back to cell A1 as the starting point for the search.

...snippet

Do While Ccnt < RepTot


'
'Rep1
'


Workbooks.Open FileName:=wbn3


ActiveCell = Range("A1")


    Cells.Find(What:="Break", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         ActiveCell.End(xlDown).Select
    r1 = ActiveCell.Offset(1, 2).Value
        
ActiveCell = Range("A1").Select


    Cells.Find(What:="Project", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         ActiveCell.End(xlDown).Select
    r2 = ActiveCell.Offset(1, 2).Value
    
ActiveCell = Range("A1").Select


    Cells.Find(What:="Lunch", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         ActiveCell.End(xlDown).Select
    r3 = ActiveCell.Offset(1, 2).Value
    
ActiveCell = Range("A1").Select


    Cells.Find(What:="Case", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         ActiveCell.End(xlDown).Select
    r4 = ActiveCell.Offset(1, 2).Value
    
ActiveCell = Range("A1").Select


    Cells.Find(What:="Duration1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
         ActiveCell.End(xlDown).Select
    r5 = ActiveCell.Value
    
ActiveWorkbook.Close False
    
Ccnt = Ccnt + 1
    
'
'Rep2
'


Workbooks.Open FileName:=wbn4


ActiveCell = Range("A1")

.....this then Loops through for up to 9 agents/workbooks

 

[AlphaFrog]

Three issues:

1.)
ActiveCell = Range("A1").Select
Is not the correct syntax to select cell A1. Just use this...
Range("A1").Select

2.) You don't have to .Select any cell for this to work. I understand noobs use .Select often (as I once did) because it's how the macro recorder records code. But it is rarely necessary to .Select a cell, and it's inefficient. In your case, if you just omit the optional After argument, it will default to after the 1st cell in the search range or A1.

3.)
The .Activate command is throwing the error when there is no match because there is nothing to activate.

Cells.Find(What:="Duration1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

The solution (as illustrated in the link I had provided in post #2) is to set the .Find to a Range variable. Then test if that range variable is Nothing.

[COLOR=darkblue]Dim[/COLOR] Found [COLOR=darkblue]As[/COLOR] Range

Workbooks.Open Filename:=wbn3

    [COLOR=darkblue]Set[/COLOR] Found = Cells.Find(What:="Break", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
         r1 = Found.End(xlDown).Offset(1, 2).Value
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

 

[tolocdn]

AlphFrog - you are a gentleman and a scholar! I am 90+% done my report 'cause of your fixes Just one little Do loop not working. In the code below I'm trying to get it to cycle through the workbooks until the number of workbooks processed is greater than the total reps - 1. It however is continuing to process workbooks and errors out due to the 5th workbook not being present/selected!

Ccnt = Cycle count
RepTot = total reps and based on a cell value - 1

Ccnt is initially set to 0 before the loop runs.

Do


If Ccnt > RepTot Then Exit Do


'
'Rep1
'


Workbooks.Open FileName:=wbn3


    Set Found = Cells.Find(What:="Break", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    If Not Found Is Nothing Then
         r1 = Found.End(xlDown).Offset(1, 2).Value
    End If
        
    Set Found = Cells.Find(What:="Project", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    If Not Found Is Nothing Then
         r2 = Found.End(xlDown).Offset(1, 2).Value
    End If


    Set Found = Cells.Find(What:="Lunch", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    If Not Found Is Nothing Then
         r3 = Found.End(xlDown).Offset(1, 2).Value
    End If


    Set Found = Cells.Find(What:="Case Work", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    If Not Found Is Nothing Then
         r4 = Found.End(xlDown).Offset(1, 2).Value
    End If


    Set Found = Cells.Find(What:="Duration1", LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
                           
    If Not Found Is Nothing Then
         r5 = Found.End(xlDown).Offset(1, 2).Value
    End If
    
    ActiveWorkbook.Close False
    
Ccnt = Ccnt + 1

.....and next rep book up until a max of 9

.....

The end of the loop is:

Loop While Ccnt < 10

 

[AlphaFrog]

AlphFrog - you are a gentleman and a scholar! I am 90+% done my report 'cause of your fixes Just one little Do loop not working. In the code below I'm trying to get it to cycle through the workbooks until the number of workbooks processed is greater than the total reps - 1. It however is continuing to process workbooks and errors out due to the 5th workbook not being present/selected!

Ccnt = Cycle count
RepTot = total reps and based on a cell value - 1

Ccnt is initially set to 0 before the loop runs.

I do not follow what that means and your posted code doesn't show the part that would need fixing; The opening of subsequent workbooks.

 

[tolocdn]

Sorry I was explaining the variables in the code.

Ccnt and RepTot are defined earlier in the Sub and used to limit the number of loops. I have a workbook that opens anywhere from 1-9 other workbooks to find certain values for different representatives. Right now instead of stopping when the loop counter (Ccnt) exceeds the representative total (RepTot) it tries to cycle on, and errors out as there is no additional defined workbook. After each workbook open and search I increment the Ccnt.

For example: if I have 3 representatives and 3 workbooks to open. RepTot = 2 (as I have it set for cell value of 3 - 1). In the entire loop code I have:


Do

if Ccnt > TotRep then exit Do

Rep 1 - open workbook1, search and assign values
Closeworkbook 1
Increment Ccnt by 1 - Ccnt should be 1 right now - (Ccnt = Ccnt +1)

Rep 2 - open workbook2, search and assign values
Closeworkbook 2
Increment Ccnt by 1 - Ccnt should be 2 right now - (Ccnt = Ccnt +1)

Rep 3 - open workbook3, search and assign values
Closeworkbook 3
Increment Ccnt by 1 - Ccnt should be 3 right now - (Ccnt = Ccnt +1)

******************* CODE SHOULD EXIT HERE BUT DOESN'T ****************

Rep 4 - open workbook4, search and assign values
Closeworkbook 4
Increment Ccnt by 1 - Ccnt should be 4 right now - (Ccnt = Ccnt +1)

.... [SNIP] ....

Rep 9 - open workbook9, search and assign values

Loop While Ccnt < 9 (end of loop code)

.....
.....
.....

Code continues on from here to paste variables assigned and is working fine for that part, just that the loop is not exiting above to get to this part due to the missing workbooks definition.

Hope that clarifies the issue?!

 

[AlphaFrog]

Yes, that explains it. There's a couple of things wrong.

1.)Your code construction is not really a loop. You have nine separate code blocks to open-search-close. A loop does one code block n times.

With your current code, you could put an IF-GOTO to skip past the subsequent code blocks.

Rep 1 - open workbook1, search and assign values
Closeworkbook 1
[B]If Reptot = 1 Then GoTo Output
[/B]
Rep 2 - open workbook2, search and assign values
Closeworkbook 2
[B]If Reptot = 2 Then GoTo Output[/B]

Rep 3 - open workbook3, search and assign values
Closeworkbook 3
[B]If Reptot = 3 Then GoTo Output[/B]

Rep 4 - open workbook4, search and assign values
Closeworkbook 4
[B]If Reptot = 4 Then GoTo Output[/B]

.... [SNIP] ....

Rep 9 - open workbook9, search and assign values
'Not needed if its the last workbook. (If Reptot = 9 Then GoTo Output)
[B]
Output:[/B]
'Code continues on from here to paste variables assigned and is working fine for that part, 
just that the loop is not exiting above to get to this part due to the missing workbooks definition.

2.)
The loop method could be constructed something like this...

    [COLOR=darkblue]For[/COLOR] Ccnt = 1 [COLOR=darkblue]To[/COLOR] Reptot
        [COLOR=green]'open workbook(Ccnt),[/COLOR]
        [COLOR=green]'search and assign values[/COLOR]
        [COLOR=green]'output[/COLOR]
        [COLOR=green]'Closeworkbook(Ccnt)[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Ccnt
    
    
[COLOR=green]'Or this...[/COLOR]
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Ccnt < Reptot
        Ccnt = Ccnt + 1
        [COLOR=green]'open workbook(Ccnt),[/COLOR]
        [COLOR=green]'search and assign values[/COLOR]
        [COLOR=green]'output[/COLOR]
        [COLOR=green]'Closeworkbook(Ccnt)[/COLOR]
    [COLOR=darkblue]Loop[/COLOR]

The loop would reference the workbook names using the Ccnt as an index number or it could reference an array of the workbook names. So you would likely have to make changes to other parts of your code.