INDEX and MATCH with VBA

[ron.bo]

I need help creating a routine using Index and Match. What I need to do is;

1. Get input from a user input box = Amt
2. Starting at D4 (with range D4..O4) check D4 and if it =1, then
3. formula in D10 = INDEX(DATA!$A$5:DATA!$BB$200,MATCH($B8,DATA!$A$5:$A$200,0),MATCH(D$6,DATA!$A$5:$BB$5,0)) * Amt, then
4. Copy the formula down to D46 then change to value (rather than leaving as formula)
5. Check the next cell to the right of D4 (E4) and if it =1, repeat
6. Loop until the next cell to the right <> 1.

Notes:
1. The formula in the next cell (E4) = INDEX(DATA!$A$5:DATA!$BB$200,MATCH($B8,DATA!$A$5:$A$200,0),MATCH(E$6,DATA!$A$5:$BB$5,0)) * Amt (Note the last MATCH value changed from D$6 to E$6 and must do that for each corresponding formula.
2. I need the routine to start at D4 and loop through O4 looking for any cell in row 4 that has a 1 and then start at that column and continue until the next cell in row 4 <>1.

Thanks,
Ronbo

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Amt As Variant
    Dim Cell As Range
    Amt = InputBox("Enter amount")
    If Amt = "" Then Exit Sub
    For Each Cell In Range("D4:O4")
        If Cell.Value = 1 Then
            With Cell.Offset(6).Resize(37)
                .FormulaR1C1 = _
                    "=INDEX(DATA!R5C1:DATA!R200C54,MATCH(R[-2]C2,DATA!R5C1:R200C1,0),MATCH(R6C,DATA!R5C1:R5C54,0))*" & Amt
                .Value = .Value
            End With
        Else
            Exit Sub
        End If
    Next Cell
End Sub

 

[ron.bo]

The routine puts the original amout in D8 and does not increase it by the input amt. Then it fills all of the other cells with the same amount. It is not changing the match cells or increasing it.

Thanks for your help.

 

[Andrew Poulsom]

The code worked when I tried it. It puts formula is rows 10 to 46 (not row 8). If you want to see the formulas, temporarily comment out this line:

.Value = .Value

 

[ron.bo]

I got it. I had other code that was conflicting. Thanks a lot for your time and expertise.

Ronbo.

PS I am new to this site. Is there a way to note that the issue/problem was solved? I did not see anything in the ethics but I did click the "thumbs-up".

 

[Andrew Poulsom]

PS I am new to this site. Is there a way to note that the issue/problem was solved? I did not see anything in the ethics but I did click the "thumbs-up".

There is no formal way to mark a question as solved. An acknowledgement of the solution is fine.

 

[ron.bo]

Andy -

I have another situation, that I did not do a good job explaining in my initial question.

In the range D4..O4 the value can be either 1 or 2. I have a different routine for each. What I need the routine to do is to check each cell in the range starting at D4 and continue until the next cell does not = the strating value some examples,

D4 E4 F4 G4 H4 I4 J4 K4 L4 M4 ... O4

a. 1 1 1 1 1 1 2 2 2 2 ... 2
b. 1 1 2 2 2 2 2 2 2 1 ... 1
c. 2 2 2 2 2 2 2 2 1 1... 1

In example a the routine for 1 would start at D4 and run through I4.
In example a the routine for 2 would start at J4 and run through O4

In example b the routine for 1 would start at D4 and run through E4.
In example b the routine for 2 would start at F4 and run through L4

In example c the routine for 1 would start at L4 and run through O4.
In example c the routine for 2 would start at D4 and run through K4

Again, thanks for the help,
Ronbo