Ok here is my problem, I have com

[andrew299]

Ok here is my problem, I have completed a search for wildcards in my macro and now want to use this wildcard variable to pick out any cells containing anything similar
by that I mean the first few letters match

wildcard = dav
therefore will pick out david, dave or davina in my worksheet

her is my code so far

C = the wildcard


Sub Transfer_data(C)

If Not C = "" Then
Index2 = 1
Sheets("main&quot.Select
Sheets(C).Select
Range("A2&quot.Select
Sheets("main&quot.Select

Columns(2).Select
Do While Cells(Index2, 1) <> &quot;&quot;


If Cells(Index2, 2) = (C) Then
Rows(Index2).Select
Selection.Copy

ActiveSheet.Select
Sheets(C).Select

ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select

Sheets(&quot;main&quot.Select
End If

Index2 = Index2 + 1

Loop

Else: End If

End Sub 'Transfer_data

The problem comes with the if statement being too precise. I thought about a 'LIKE' stament

dim LIKEA

MYLIKE = C LIKE ACTIVECELL.VALUE

But this doesnt work.

The main problem is that I the code works fine when not doing a wildcard search but specifying the variable exactly so I dont want to change it so that it stops doing these searches properly

Any ideas?

Andrew299

 

[xlbo]

Guess you'll need a button / choice to enable users to select a wildcard search

PSEUDOCODE

Select Case WildCard
case vbyes
find &quot;*&quot; & SearchText & &quot;*&quot;
case vbno
find SearchText
end select Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[andrew299]

Sorry Geoff
I have already got the option to do the search with a * and have done that. The problem that I am trying to work around is that excel doesnt allow worksheets to be created with a * in the name.
I have got round this by removing the * from the wildcard. This leaves me with a code only a few characters long (the dav bit in my original post)

The problem lies on this line of code



If Cells(Index2, 2) = (C) Then


The values in the cells will be the full code ie David
but 'C' is DAV and therefore the if_loop ends as in code. Is there a way to make it recognise that it only needs to compare the first few letters or an alternative line to the one above that is not so absolute? Kind of like the LookAt:= xlPart bit in the find command - if you see what I mean.
Thanks
andrew299

 

[xlbo]

Well, without actually using the FIND method, you could use LEFT or MID or INSTR

Instead of:
If Cells(Index2, 2) = (C) Then

you could use

If instr(1,cells(Index2,2),C,1) <> 0 then
C found somewhere in cell
else
C not found in cell
end if Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[andrew299]

Dim Ccell
With ActiveCell
Set Ccell = .Find(What:=C, LookIn:=xlValues, LookAt:= _
xlPart)

If Ccell Is Nothing Then
CcellA = False
G = O
Else
CcellA = True
G = 1
End If
End With


If CcellA = True Then


Thanks Geoff

I wrote the code above before you had put up your latest post It looks like it might work in a similar way. Thanks for your help.