I am trying to get a list of words starting with a specific letter..
something like

I have 3 sheets.. Sheet1, 2 & 3

Sheet 2 has data in B:B

Sheet1 has the letter in B2 and

result should appear in Sheet 3

Can anyone help me on this please..?

AdvancedFilter with a formula criteria: =A2=LEFT(A2,3)="nut"
http://www.ozgrid.com/Excel/advanced-filter.htm



--
Regards
Dave Hawley
www.ozgrid.com


"Kash" wrote in message
...
I am trying to get a list of words starting with a specific letter..
something like

I have 3 sheets.. Sheet1, 2 & 3

Sheet 2 has data in B:B

Sheet1 has the letter in B2 and

result should appear in Sheet 3

Can anyone help me on this please..?

On 7 mei, 03:04, "ozgrid.com" wrote:
AdvancedFilter with a formula criteria: =A2=LEFT(A2,3)="nut"http://www.ozgrid.com/Excel/advanced-filter.htm

--
Regards
Dave Hawleywww.ozgrid.com

"Kash" wrote in message

...



I am trying to get a list of words starting with a specific letter..
something like

I have 3 sheets.. Sheet1, 2 & 3

Sheet 2 has data in B:B

Sheet1 has the letter in B2 and

result should appear in Sheet 3

Can anyone help me on this please..?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Kash,

In Excel 2003 I created this macro:

Sub FindOnCharacters()
Dim lngRow As Long
Dim lngLast As Long
Dim lngFind As Long
Dim lngChars As Long
Dim strChars As String
'
If Not IsEmpty(Sheets("Sheet1").Range("B2")) Then
' cleanup previous results
If Not IsEmpty(Sheets("Sheet3").Range("B2")) Then
Sheets("Sheet3").Range("B:B").ClearContents
End If

strChars = Sheets("Sheet1").Range("B2").Value
lngChars = Len(strChars)
lngRow = 1
lngLast = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count ,
2).End(xlUp).Row
For lngFind = 1 To lngLast
If StrComp(Left(Sheets("Sheet2").Cells(lngFind, 2).Value,
lngChars), strChars, vbTextCompare) = 0 Then
lngRow = lngRow + 1
Sheets("Sheet3").Cells(lngRow, 2).Value = _
Sheets("Sheet2").Cells(lngFind, 2).Value
End If
Next
End If
End Sub

(If you get some red line after you copy this code, combine the top
one with the next)

HTH,

Wouter

Rather than warning OP about red lines, when writing and posting code you
should learn to use line-continuation character( _ )to wrap lines of code so
red lines don't appear.

lngLast = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count , _
2).End(xlUp).Row

If StrComp(Left(Sheets("Sheet2").Cells(lngFind, 2).Value, _
lngChars), strChars, vbTextCompare) = 0 Then


Gord Dibben MS Excel MVP

On Mon, 10 May 2010 13:00:20 -0700 (PDT), Wouter HM
wrote:

(If you get some red line after you copy this code, combine the top
one with the next)