I started with needing 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.
Andrew P. help with;
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
The problem is that it does not work with Note 2. i.e. if D4's value is not 1 but E4 is 1, the code does not execute. In this case I need for it to start in E4 and continue until the next cell in D4..O4 does not contain 1.
Some examples are as follows;
In the range D4..O4 the value can be either 1 or 2. 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 =1
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 would start at D4 and run through I4.
In example b the routine would start at D4 and run through E4.
In example c the routine would start at L4 and run through O4.
Thanks,
Ronbo
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.
Andrew P. help with;
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
The problem is that it does not work with Note 2. i.e. if D4's value is not 1 but E4 is 1, the code does not execute. In this case I need for it to start in E4 and continue until the next cell in D4..O4 does not contain 1.
Some examples are as follows;
In the range D4..O4 the value can be either 1 or 2. 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 =1
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 would start at D4 and run through I4.
In example b the routine would start at D4 and run through E4.
In example c the routine would start at L4 and run through O4.
Thanks,
Ronbo
