Checking the last character is in a list

  • Thread starter Thread starter harg7769
  • Start date Start date
H

harg7769

I'm probably staring at the answer here, but can't get it yet.

What I anm trying to do is go through a list of street numbers, and if
the last character is between A to F then copy that character to the
cell to the right of it, otherwise, leave it alone.

So far I've done this:

Sub SplitFlat()
Dim rgSheet As Range
Dim i As Integer
Dim stContents As String


Set rgSheet = Range("Properties")
For i = 1 To rgSheet.Rows.Count
stContents = rgSheet.Cells(i, 1).Value
If Right(stContents, 1) = "A" Then
rgSheet.Cells(i, 2).Value = Right(stContents, 1)
Else
rgSheet.Cells(i, 2).Value = Null
End If
Next i

End Sub

I thought I would be able to do

If Right(stContents, 1) between "A" to "F" Then ....

but it doesn't like that for some reason.

Would I be better using a case statement to do this, or will I have to
put the 6 possible values into an array, and check them against that?

I have always done this using a formula, but I want to try and learn
VBA, and examples like this tend to stick in my mind better than
following a book.

Cheers

Andrew H
 
I thought I would be able to do

If Right(stContents, 1) between "A" to "F" Then ....

but it doesn't like that for some reason.
Click to expand...

You'll be pleased when you test the following in the Immediate (debug)
window:
?"F"<"G"
?"G"<"F"

Ergo
If Right(stContents, 1) >= "A" And Right(stContents, 1) <= "F" Then

Your real challenge will be to have data that is that consistent enough
to fit your "rule." I have found that the consistency of street
addresses in a database can be nasty.
 
You're welcome. If you're caught in such a nasty mess in the future,
consider preceding such a line with
s$ = Right(stContents, 1)
if efficiency matters.

You still can do better than that long ugly statement even when the
letters are not fully contiguous (exactly A through F). You could put
each valid letter in a range, like
A
C
D
F

and do a isna(vlookup) - or even better, do a more efficient match<>0.
But you'd probably like using CHOOSE best of all - I'll let you
experiment with it.

But like you I'd have preferred something like "BETWEEN" or SQL's "IN"
 
Back
Top