Avoid select, two-stage formula, faster

Question

How can I avoid select and do I really needs to use that function or is there better one. Thank you for your time.

   Sub Makro3()
        Range("D5").Select: ActiveCell.FormulaR1C1 = "=INDEX(S_Skills_1[L],(MATCH(16622,S_Skills_1[ID])))"
    x = Range("D5").Value
        Range("D5").Select: ActiveCell.FormulaR1C1 = "=INDEX(S_Skills_2[L],(MATCH(16622,S_Skills_2[ID])))"
    y = Range("D5").Value
        Range("D5").Value = Application.WorksheetFunction.max(x, y)

        Range("D6").Select: ActiveCell.FormulaR1C1 = "=INDEX(S_Skills_1[L],(MATCH(3446,S_Skills_1[ID])))"
    A = Range("D6").Value
        Range("D6").Select: ActiveCell.FormulaR1C1 = "=INDEX(S_Skills_2[L],(MATCH(3446,S_Skills_2[ID])))"
    B = Range("D6").Value
        Range("D6").Value = Application.WorksheetFunction.max(x, y)
    End Sub

Better how? What are you looking for? – Jean-François Corbett Oct 26 '14 at 20:23 — Jean-François Corbett, Oct 26 '14 at 20:23

Siddharth Rout · Accepted Answer · 2014-10-26T20:25:40.870

3

Try this. UNTESTED

Sub Makro3()
    X = Evaluate("=INDEX(S_Skills_1[L],(MATCH(16622,S_Skills_1[ID])))")
    Y = Evaluate("=INDEX(S_Skills_2[L],(MATCH(16622,S_Skills_2[ID])))")
    Range("D5").Value = Application.WorksheetFunction.Max(X, Y)

    A = Evaluate("=INDEX(S_Skills_1[L],(MATCH(3446,S_Skills_1[ID])))")
    B = Evaluate("=INDEX(S_Skills_2[L],(MATCH(3446,S_Skills_2[ID])))")
    Range("D6").Value = Application.WorksheetFunction.Max(X, Y)
End Sub

edited Oct 26 '14 at 20:25

answered Oct 26 '14 at 20:18

Siddharth Rout

147,039
17
206
250

2

+1 - But wait, no [link to post about not using Active & Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)? :-) – Automate This Oct 26 '14 at 20:25
lol@PortlandRunner: I was thinking whether to post that link or not :D Kind of tired doing that... But anyways you did it so kool! – Siddharth Rout Oct 26 '14 at 20:26
@PortlandRunner ah thank you, there is still small glitch in my answer with defining my 'SP1' range for named array. – Alenn G'Kar May 02 '15 at 11:26

Alenn G'Kar · Answer 2 · 2015-05-02T11:22:06.447

This is my final code If you want to know what I've needed. Thanks to @Siddharth Rout

Sub FindMatchingValue()
    Dim dat1 As Variant:    Dim dat2 As Variant
    Dim SP1 As Range:       Set SP1 = [T5:V500] 'Range("S_Skills_1") why I cant use named areas for columns ?
    Dim SP2 As Range:       Set SP2 = [W5:Y500]
    Dim i As Long

    Find1 = 16622
    dat1 = SP1.Value
    dat2 = SP2.Value
    Find2 = 3446

Range("D5, D6, D12, D13").Value = 0

FirstCheck:
    For i = 1 To 300
    If dat1(i, 1) = Find1 Then _
    X = (dat1(i, 3))
    If dat2(i, 1) = Find1 Then _
    Y = (dat2(i, 3))
    Next

SecondCheck:
If Not X = 5 Or Y = 5 Then GoTo NoMore:

FullSkill:
    For i = 1 To 300
    If dat1(i, 1) = Find2 Then _
    A = (dat1(i, 3))
    If dat2(i, 1) = Find2 Then _
    B = (dat2(i, 3))
    Next
Range("D5, D12").Value = 5
Range("D6, D13").Value = Application.WorksheetFunction.max(A, B)
GoTo Finish:

NoMore:
Range("D5, D12").Value = Application.WorksheetFunction.max(X, Y)
Range("D6, D13").Value = 0

Finish:
Call L_Set
Call LockS
Range("D1").Select

End Sub

Avoid select, two-stage formula, faster

2 Answers2