2
\$\begingroup\$

I started working on some code, and it works, but I feel like it could be done more effiently. Below is a portion of it to show what I'm doing. To simplify the idea, I've made it here so if the cell in column M is A, B, or C, it puts a 1 in column L. If column M is a D, E, or F, it puts a 2 in column L. And if column M is a G, H, or I, it puts a 3 in column L.

Is there an easier way to do this than how I'm doing it? I'm going to be dealing with a couple hundred possible values. Alex P suggested I build a reference table. I've never done that before, so I'm not sure if that's my best bet or not.

Sub ChangeTest()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("M" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("M" & i).Value = "A" Or Range("M" & i).Value = "B" Or Range("M" & i).Value = "C" Then
        Range("L" & i).Value = "1"
        End If
        If Range("M" & i).Value = "D" Or Range("M" & i).Value = "E" Or Range("M" & i).Value = "F" Then
        Range("L" & i).Value = "2"
        End If
        If Range("M" & i).Value = "G" Or Range("M" & i).Value = "H" Or Range("M" & i).Value = "I" Then
        Range("L" & i).Value = "3"
        End If
    Next i
End Sub
\$\endgroup\$
4
  • \$\begingroup\$ "Simplifying" your code works well for Stack Overflow. On this site however, you'll get much more useful feedback if you include your real, actual, working code. Try it next time! (please don't edit your question in such a way that would invalidate already-given answers) - see a guide to Code Review for Stack Overflow users for more information. \$\endgroup\$ – Mathieu Guindon Nov 21 '16 at 20:47
  • \$\begingroup\$ I posted this on Stack Overflow first, and I was told to post it here. And I got a site warning for pasting my real, actual, working code because it said most of my post was code. So I thought because of that, and just so everyone would be able to make more sense out of it, I made it much simpler and shorter. \$\endgroup\$ – Robby Nov 21 '16 at 20:52
  • \$\begingroup\$ SO folks were right - getting feedback on any/all aspects of your code happens on Code Review, not SO. I've never seen that warning though, but the post length limit on CR is pretty much double that of SO, and you should be fine if your post presents the code to the reviewers, i.e. explains what's going on and why - as you've done here. \$\endgroup\$ – Mathieu Guindon Nov 21 '16 at 21:08
  • \$\begingroup\$ cross-posted from Stack Overflow \$\endgroup\$ – Mathieu Guindon Nov 21 '16 at 21:16
3
\$\begingroup\$

Don't know if it would be faster, but it would look cleaner to use a Select.

Sub ChangeTest()
    Dim LastRow As Long
    Dim i As Long
      LastRow = Range("M" & Rows.Count).End(xlUp).Row
      For i = 2 To LastRow
        Select Case Range("M" & i)
          Case "A", "B", "C"
            Range("L" & i) = "1"
          Case "D","E","F"
            Range("L" & i) = "2"
          Case "G","H","I"
            Range("L" & i) = "3"
        End Select
      Next i
End Sub
\$\endgroup\$
4
  • \$\begingroup\$ This looks good. There's going to be lots of cases, so I'm hoping this isn't too slow. It might take me a few days to fully implement. \$\endgroup\$ – Robby Nov 21 '16 at 21:24
  • \$\begingroup\$ At least this way, it is much faster to add a new condition rather than using it in an IF. \$\endgroup\$ – Rdster Nov 21 '16 at 21:32
  • \$\begingroup\$ @Robby - If this helped, please mark as the answer. \$\endgroup\$ – Rdster Nov 23 '16 at 15:05
  • \$\begingroup\$ I haven't been able to implement it yet. Sorry. I'm out of the office for a few days. \$\endgroup\$ – Robby Nov 23 '16 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.