How can I simplify this?

xrobc

New Member
Joined
Feb 25, 2015
Messages
20
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,752
Office Version
2010
Platform
Windows
A​
B​
C​
1​
column
value
out
2​
3​
N20
3​
4​
020
4​
9​
M-RT20
5​
11​
M-RT20
6​
12​
M-RT20
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​
TT
14​
3​
LALA
15​
3​
CC

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
 

xrobc

New Member
Joined
Feb 25, 2015
Messages
20
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,752
Office Version
2010
Platform
Windows
Code:
Range("D2:[COLOR="#FF0000"][B]Q[/B][/COLOR]2").Resize(UBound(av1)).Value = av1
 

xrobc

New Member
Joined
Feb 25, 2015
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top