List words starting with a specific letter

[Kash]

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..?

 

[ozgrid.com]

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

 

[Wouter HM]

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

--
Regards
Dave Hawleywww.ozgrid.com











- 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

 

[Gord Dibben]

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