Find a letter

[WelshyWizard]

I've got a long list of values in column A on an Excel spreadsheet.
Example:
891001A
891001B
891001C
921009
922001
978012A
821320
Etc...

Using VBA I want to be able to find all values that have a letter in them (Could be A - Z but will always be the final character), and copy them onto a seperate sheet.

Any ideas anyone?

Thanks

Today is the tomorrow you worried about yesterday - and all is well.....

 

[xlbo]

if all the other characters are numeric then:

=IF(ISERROR(VALUE(A1)),"Letter At End","No Letter")

shoiuld work for data in A1 - you can then filter for "Letter At End" and copy to a new sheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lgclims]

For an answer using VBA see below.

Sub test()

Dim Found(255) 'array containing cells with letter at the end
Dim r 'row
Dim x 'array counter

r = 1
x = 0

Do

ActiveSheet.Cells(r, 1).Select

If (Asc(UCase(Right(ActiveCell.Value, 1))) >= 65 And
Asc(UCase(Right(ActiveCell.Value, 1))) <= 90) Then
x = x + 1
Found(x) = ActiveCell.Value
End If

r = r + 1

Loop Until Cells(r, 1).Value = ""


If x = 0 Then Exit Sub

r = 1
x = 1

Sheets.Add

Do
ActiveSheet.Cells(r, 1).Value = Found(x)
r = r + 1
x = x + 1
Loop Until Found(x) = ""



End Sub

 

[xlbo]

well if you want code then:

Dim test
For Each c In Range("A1:A100")'amend as per your range
    On Error Resume Next
    test = CLng(c)
        If Err.Number <> 0 Then
            c.Offset(0, 1).Value = "Letter At End"
        Else
            c.Offset(0, 1).Value = "No Letter"
        End If
    Err.Clear
    On Error GoTo 0
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Or, more simply, ...

[blue]For Each c In Range("A1:A100") 'amend as per your range
        If c <> Val(c) Then
            c.Offset(0, 1).Value = "Letter At End"
        Else
            c.Offset(0, 1).Value = "No Letter"
        End If
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

D'oh - nice one Tony - should've picked up on that as I was gonna use Val but it transformed the value of the cell with or without the letter - should've realised that was actually useful rather than going off n raising errors !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question