VBA - Index Match

[dipsy]

I am getting an error message when I use the following
formula in VBA:

Cells(I, (16 + K)).formula = _
"=if(iserror(index(MONTHLY!$A:$N), _
match(Plan!(Cells(I, 3).Value), MONTHLY!$A:$A, False),
j),0 _
(index(MONTHLY!$A:$N), match(Plan!Cells(I, 3).Value,
MONTHLY!$A:$A, False), j)"

Here - K, I and J are different counters. When I use the
following formula I am fine

Cells(I, (16 + K)) = Application.Index(Worksheets
("MONTHLY").Range("$A:$N"), _
Application.Match(Worksheets("Plan").Cells(I, 3).Value, _
Worksheets("MONTHLY").Range("$A:$A"), False), j)

but I need to check for the error.

Thanks in advance.

 

[Bob Phillips]

Dipsy,

Try this

sformula = "INDEX(MONTHLY!C1:C14, MATCH(Plan!R" & i & "C3,
MONTHLY!C1:C1, False)," & j & ")"
Cells(i, (16 + k)).FormulaR1C1 = "=IF(ISERROR(" & sformula & ")" & _
",0," & sformula & ")"

 

[Myrna Larson]

First of all, the code that you say works fine isn't inserting a formula in the worksheet cell.
It's inserting a value -- the result of evaluating the formula withing VBA. Those are two
different things. I am assuming you want to put a *formula* in the cell.

When trying to get the formula onto one line, I see there's an error in this piece from the tail
of the 2nd line into the beginning of the 3rd:

MONTHLY!$A:$A, False),j),0 (index(MONTHLY...

I presume you omitted the comma after the 0.

Another error is that you can't put line continuation characters inside of a quoted string. If
you do that, they are part of the text, not a line continuation character.

"=if(iserror(index(MONTHLY!$A:$N), _
match(Plan!(Cells(I, 3).Value), MONTHLY!$A:$A, False), j),0 _

has to be

="IF(ISERROR(INDEX(MONTHLY!$A:$N)," _
& "MATCH(.....

The formula that you construct in VBA code must be *identical* to what you would type into the
cell if you were entering the formula at the keyboard. You wouldn't type "I" or "J" -- if you
did, in a formula they would be references to named ranges -- and you wouldn't type "Cells(I,
3).Value". You'd type something like "B3".

You must substitute the VALUE of I and J for the variable name in your formula, and the values
from the cell for the Cells(...).Value. So the first part would look something like this.

"=IF(ISERROR(INDEX(MONTHLY!$A:$N),MATCH(" & Cells(I, 3).Value & _
",MONTHLY!$A$A, False), " & j & "), 0, ....."

The way I usually construct a formula is to put it into a variable, using placeholders for the
variable information, then use Replace to replace the placeholders with the variable values.
Note that I've assumed the INDEX formula will not give an error if the MATCH result is OK, so
here I've shortedned the formula.

F = "=IF(ISERROR(MATCH(#I3#,MONTHLY!$A:$A,False)),0," _
& "INDEX(MONTHLY!$A:$N,MATCH(#I3#,MONTHLY!$A:$A,False),#J#)"
F = Replace(F, #I3#, Worksheets("Plan").Cells(I, 3).Value)
F = Replace(F, #J#, Cstr(J))
Cells(I, 16+K).Formula = F

The above puts a literal value in the MATCH formulas. If you want a cell reference, it would be

F = Replace(F, #I3#, "Plan!" & Cells(I, 3).Address)

If you are using XL97 or earlier, you have to use Application.Substitute instead of Replace.

 

[Myrna Larson]

Are you letting yourself in for a lesson on R1C1 notation <g> ?

 

[Bob Phillips]

Do you know, judging by the original post, I think I just may be.

Bob

Are you letting yourself in for a lesson on R1C1 notation <g> ?