• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 970
  • Last Modified:

VBA Excel 'Find' Function

Howdy

I'm attempting to match the strings in a specific column of worksheet A with strings in a specific column in worksheet B.  Each match found results in the entire row being copied into a third worksheet.  The number of entries in the column in worksheet A varies, so the loop would continue until whatever the VBA equivalent of EOF would be when readiing from a column.   I attempted to record a macro where I used the 'Find'/CTRL-F function.  It didn't record anything, so I'm looking for the VBA function that's tied to this action.

As always, any help is greatly appreciated.

Jason
0
cdromes
Asked:
cdromes
  • 5
  • 3
1 Solution
 
dirknibleckCommented:
you just need to do:


activesheet.columns("A").find(activesheet.range("B1"))

(replace activesheet.range("B1") with whatever you are searching on)
0
 
dirknibleckCommented:
The find function returns either a range or Nothing. (Nothing if it is empty)

so

while values in B

if not activesheet.column("A").find(activesheet.range("B1")) is nothing

  copy row to third sheet

end if

wend
0
 
cdromesAuthor Commented:
Normally, if I want to select an entire row prior to copying I do something like

Rows("1294:1294").Select

In this case, I don't have the row number of the item that was found, so how do I go about marking the entire row for copying?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dirknibleckCommented:
row = activesheet.column("A").find(activesheet.range("B1")).row
0
 
cdromesAuthor Commented:
I'm getting an error at the IF statement.  Do I need to add an '.offset' or a '.value' in order to be more specific about what's being tested?

RowCount = 1
Do
    Sheets("HiltboldRaw").Select
    If ActiveSheet.Column("A").Find(Sheets("AffyTags").Range("B1")) <> " " Then
       Sheets("AffyTags").Range("B1").Offset(RowCount, 0).Row.Select
       Selection.Copy
       Sheets("Test").Select
       Rows("RowCount:RowCount").Select
       ActiveSheet.Paste
    End If
    RowCount = RowCount + 1
Loop Until IsEmpty(Sheets("AffyTags").Range("B1").Offset(RowCount, 0).Value)
0
 
dirknibleckCommented:
This:
 If ActiveSheet.Column("A").Find(Sheets("AffyTags").Range("B1")) <> " "

Has to be:

 If Not (ActiveSheet.Columns("A").Find(Sheets("AffyTags").Range("B1")) is nothing)

*note Columns not Column (that was an error in my original post)
0
 
cdromesAuthor Commented:
It ran, but nothing was copied.  There are 32 matches that I know of.  Are there issues with this:

Sheets("AffyTags").Range("B1").Offset(RowCount, 0).Row.Select
Selection.Copy

?
0
 
dirknibleckCommented:
try:

rowcount= sheets("AffyTags").range("B1").end(xldown).row


for i = 1 to rowcount

    Sheets("HiltboldRaw").Select
    If Not (ActiveSheet.Columns("A").Find(Sheets("AffyTags").Range("B1")) is nothing) Then
       Sheets("AffyTags").Rows(i).Copy
       Sheets("Test").Select
       Rows("i").Select
       ActiveSheet.Paste
    End If
next i
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now