Results 1 to 5 of 5

Thread: How can I simplify this?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How can I simplify this?

    Hi

    I've managed to come up with this code:

    Code:
        Dim v, i As Long
        v = Range("D2:Q" & Range("D" & Rows.Count).End(xlUp).Row).Value
        '
        For i = 1 To UBound(v)
            'N Is 20
            If Left(v(i, 3), 1) = "N" Then
                v(i, 2) = "20"
            End If
            '0 = 20
            If v(i, 4) = "0" Then
            v(i, 2) = "20"
            End If
            
    '       M-RT = Op20
            If (Left(v(i, 9), 4) = "M-RT") Then
                v(i, 2) = "20"
            End If
    '       M-RT = Op20
            If (Left(v(i, 11), 4) = "M-RT") Then
                v(i, 2) = "20"
            End If
    '       M-RT = Op20
            If (Left(v(i, 13), 4) = "M-RT") Then
                v(i, 2) = "20"
            End If
    
    
    '       (Area5) = Op20
            If (Left(v(i, 10), 7) = "(AREA5)") Then
                v(i, 2) = "20"
            End If
            If (Left(v(i, 12), 7) = "(AREA5)") Then
                v(i, 2) = "20"
            End If
            If (Left(v(i, 14), 7) = "(AREA5)") Then
                v(i, 2) = "20"
            End If
            
    '      (Area 7) = Op20
            If (Left(v(i, 10), 7) = "(AREA7)") Then
                v(i, 2) = "20"
            End If
            If (Left(v(i, 12), 7) = "(AREA7)") Then
                v(i, 2) = "20"
            End If
            If (Left(v(i, 14), 7) = "(AREA7)") Then
                v(i, 2) = "20"
            End If
            
            'T not added
            If Left(v(i, 3), 1) = "T" Then
                v(i, 2) = "T"
            End If
            'LA not added
            If Left(v(i, 3), 2) = "LA" Then
                v(i, 2) = "LA"
            End If
            'C not added
            If Left(v(i, 3), 1) = "C" Then
                v(i, 2) = "C"
            End If
    
    
        Next i
        
        Range("D2:E2").Resize(UBound(v)).Value = v
    Its using the array to analyse some data and set a value (typically using the number 20) based on that data.

    There's quite a few different rules analysing different things that ultimately do the same thing.

    I would like to simplify this code and perhaps pick up some of the rules from cells on a worksheet (in another array?). But I don't know where to start.

    Thanks

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How can I simplify this?

    A
    B
    C
    1
    column
    value
    out
    2
    3
    N 20
    3
    4
    0 20
    4
    9
    M-RT 20
    5
    11
    M-RT 20
    6
    12
    M-RT 20
    7
    10
    (AREA5) 20
    8
    12
    (AREA5) 20
    9
    14
    (AREA5) 20
    10
    10
    (AREA7) 20
    11
    12
    (AREA7) 20
    12
    14
    (AREA7) 20
    13
    3
    T T
    14
    3
    LA LA
    15
    3
    C C


    Code:
    Sub xrobc()
      Dim av1           As Variant
      Dim av2           As Variant
      Dim i             As Long
      Dim j             As Long
    
      av1 = Range("D2", Cells(Cells(Rows.Count, "D").End(xlUp).Row, "Q")).Value
      av2 = Range("A2", Cells(Rows.Count, "C").End(xlUp))
      '
      For i = 1 To UBound(av1)
        For j = 1 To UBound(av2)
          If Left(av1(i, av2(j, 1)), Len(av2(j, 2))) = av2(j, 2) Then av1(i, av2(j, 1)) = av2(j, 3)
        Next j
      Next i
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2015
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I simplify this?

    Thanks, I think that I can just about follow what's happening.

    Because all of my original formula are nearly identical, you have replace the values that change with variables.

    However, when I try to write that back with

    Code:
    Range("D2:E2").Resize(UBound(av1)).Value = av1
    Nothing appears to happen. It looks like it is overwriting like with like, when I look in the locals window the value of av1(i,2) is Empty.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How can I simplify this?

    Code:
    Range("D2:Q2").Resize(UBound(av1)).Value = av1

  5. #5
    New Member
    Join Date
    Feb 2015
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I simplify this?

    That makes sense, just write over the whole lot. Should have seen that one, but I'm not really an expert on this yet.

    I changed the formula to get the write back in the correct place as it was overwriting the lookup value.

    Code:
    If Left(av1(i, av2(j, 1)), Len(av2(j, 2))) = av2(j, 2) Then av1(i, 2) = av2(j, 3)
    One other observation was that is didn't like the value of 0 in position B3 of the table, but when I tried with a different character it work perfectly.

    Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •