Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking the last character is in a list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking the last character is in a list
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking the last character is in a list
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking whether a value in a list is present in a second list | Excel Discussion (Misc queries) | |||
checking for a value in a list | Excel Discussion (Misc queries) | |||
Error Checking for Duplicates in List | Excel Worksheet Functions | |||
Checking if different items are in a list | Excel Discussion (Misc queries) | |||
Checking if value is in a list | Excel Worksheet Functions |