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 |
Checking the last character is in a list
|
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" |
All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com