Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Index function in coding macro?
Does anyone have any suggestions on how to use index function in coding macro?
myCell.Offset(0, 2).Value = Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp worksheet],0)) Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Index function in coding macro?
Eric,
The workaheet formula =INDEX(B1:B9,MATCH(A1,Sheet2!D1:D9,FALSE),1) can be used in macro like this Lvalue = Sheets("Sheet1").Range("A1").Value Set IRange = Sheets("Sheet1").Range("B1:B9") Set Vrange = Sheets("Sheet2").Range("D1:D9") Set Mycell = Sheets("Sheet2").Range("D1") Mycell.Offset(0, 2).Value = Application.WorksheetFunction.Index _ (IRange, Application.WorksheetFunction.Match(Lvalue, Vrange, False), 1) Mike "Eric" wrote: Does anyone have any suggestions on how to use index function in coding macro? myCell.Offset(0, 2).Value = Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp worksheet],0)) Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Index function in coding macro?
There are just a handful of worksheet functions that can be called directly
from 'Application' more efficiently than from 'WorksheetFunction', eg Index and Match in the following context - Sub Test() Dim r1 As Range, r2 As Range, r3 As Range Dim LookUpVal As Variant SampleData Set r1 = Range("A1:A10") Set r2 = Range("B1:B10") Set r3 = Range("C1") For i = 1 To 10 LookUpVal = i Cells(i, 3) = i Set r3 = Cells(i, 4) With Application r3.Value = .Index(r2, .Match(LookUpVal, r1, 0)) End With Next End Sub Sub SampleData() Dim i As Long For i = 1 To 10 Cells(1, 1) = Int(Rnd() * 11) Cells(i, 2) = Chr(64 + i) Next End Sub There's a second advantage in bypassing 'WorkSheetFunction' in the above - if there is no match an error value is returned rather than the code breaking due to error. Regards, Peter T "Eric" wrote in message ... Does anyone have any suggestions on how to use index function in coding macro? myCell.Offset(0, 2).Value = Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp worksheet],0)) Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for index/match function | Excel Programming | |||
coding an anagram function | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Coding in VLOOKUP function | Excel Programming |