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\$ Commented Nov 21, 2016 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\$ Commented Nov 21, 2016 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\$ Commented Nov 21, 2016 at 21:08
  • \$\begingroup\$ cross-posted from Stack Overflow \$\endgroup\$ Commented Nov 21, 2016 at 21:16

1 Answer 1

Reset to default
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\$ Commented Nov 21, 2016 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\$ Commented Nov 21, 2016 at 21:32
  • \$\begingroup\$ @Robby - If this helped, please mark as the answer. \$\endgroup\$ Commented Nov 23, 2016 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\$ Commented Nov 23, 2016 at 15:17

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.