List words starting with a specific letter

  • Thread starter Thread starter Kash
  • Start date Start date
K

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..?
 
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 -
Click to expand...

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
 
Back
Top