AutoFill in Formula (R1C1 Style) till data found in adjacent column, multiple IF(AND

[analyst]

First Row is a header, so it should NOT be changed/disturbed.

Data Range is from Column A to O, and row data could vary from 50 to 500

Column P is just a separator and has no data.

Column, I, K, M, has Numeric data which needs to be compared.

I Need a VBA MACRO Code that could fill in RELATIVE Excel Formula (R1C1 style) through VBA Macro Code in every 4th cell in Column Q, and Column R in such way that there is data in adjacent Column M till end (XL Down)


Part -1 : Here just code is reqd., as formula is correct

cOLUMN Q, starting with 4th row i.e. Cell Q4 should have following formula, and every 4th cell thereafter like Q8, Q12 and so on till there is data till end in Column M


=IF(AND((I2/I1)>=1,(K2/K1)>=5.5,(M2/M1)>=5),ROUND((K2/K1),2)&" ABC",IF(AND(ABS((I2/I1)-1)<=1,(K2/K1)>=5.5,(M2/M1)>=5),ROUND((K2/K1),2)&" PQR","Ignore"))



Part -2: Here I need help for building formula also, but same should be filled as above required above but in Column R, (in above step it was for Column Q)

COLUMN R, starting with 4th row i.e. Cell R4 should have following formula, and every 4th cell thereafter like R8, R12 and so on till there is data till end in Column M


The formula for conditions given below should be single formula

Condition1: IF K4>(K3*5), and K3>(K2*5), and K2(K1*5),
IF ALL Above conditions are true, "TUV"

Condition2: IF K4>K3, AND K3>K2 AND K2>K1 AND ALSO I4>I3, AND I3>I2, AND I2>I1
IF ALL ABOVE Codnitions given in 2 here, is true, "EFG"

Condition3: IF K4<(K3*2), AND K3<(K2*2), ANd K2<(K1*2)
IF ALL ABOVE CODNTIONS given in 3 here, is true, "TUV"

Codnition 4: IF K4<K3, AND K3<K4, AND K2<K1 AND ALSO I4>I3, AND I3>I2, AND I2> I1
IF ALL ABOVE COnditions given in 4 here, is True, "123"

If none of the conditions 1 to 4 of Part 2 above is met, say, "IGNORE"

[patel]

attach please a sample file with notes

[analyst]

@Patel, Thank for looking at this query, here is the sample data file

[patel]

part 1 - the formula is not correct

[analyst]

@Patel, Sir, thx for pointing small mistake, but it wont affect much, as I need MAcro to auto fill in the formula in every 4th row, till there is data in adjacent cells.

Perhaps, Part 1 Formula can be changed to I4/I3, K4/K3, M4/M3, at respective places, and so on, instead of referring to Data row 1 and Row 2, i.e. I2/I1, K2/K1, and M2/M1.

Please help for Part 2 Conditions, and how to fill every 4th row in Column Q, and R through VBA Code, in R1C1 style!

Regards

[Excel Fox]

You can use this code, however, you'll need to correct the formula.



Sub FillFormula()

Dim lngRows As Long
lngRows = Cells(Rows.Count, "M").End(xlUp).Row

For lngRows = 5 To lngRows Step 4
Cells(lngRows, "Q").FormulaR1C1 = _
"=IF(AND((RC[-8]/R[-1]C[-8])>=1,(RC[-6]/R[-1]C[-6])>=5.5,(RC[-4]/R[-1]C[-4])>=5),ROUND((RC[-6]/R[-1]C[-6]),2)&"" ABC"",IF(AND(ABS((RC[-8]/R[-1]C[-8])-1)<=1,(RC[-6]/R[-1]C[-6])>=5.5,(RC[-4]/R[-1]C[-4])>=5),ROUND((RC[-6]/R[-1]C[-6]),2)&"" PQR"",""Ignore""))"
Cells(lngRows, "R").FormulaR1C1 = "=1+1"
Next lngRows

End Sub

[analyst]

Thank you Sir, the correct formula is given in this thread - http://www.excelfox.com/forum/f2/vba-code-to-autofill-in-formula-in-every-4th-row-1616/#post7554.

Sincerely, I apologize, as I did not want to break the rules of the Forum. So, please Merge the thread, and regret the inconvenience to the forum resources, if i have caused.

I would be greatful, if u can consider the other thread with correct formula given in there attached workbook, or I'll try to take clue from here.

Thanks.

P.S.: I've understood the concept, but there is mistake in formula for Column R, which just have forumla '=1+1', instead of what is being mentioned in Part 1. But, concept is clear, though i could not fix the issue.