code not looping.

[orsm6]

hi all - i thought this code would loop through the range and put a formula in every box ... but it doesn't. how can it be changed to make it loop?

    With Range("E2:E100").Find("Z004").Offset(0, 10)
        .Value = "=((K3*M3)/N3)"
    End With

if it is possible, can the code just loo for Z00 rather than Z004.... i could use that to fill the rest of the column rather than have separate codes for each....

thank you

 

[orsm6]

Sub enterformula()

Dim x As Long
Dim lr As Long
Dim c As Range

lr = Range("E" & Rows.Count).End(xlUp).Row

    For Each c In Range("E3:E" & lr)
        If c.Value = "CONV" Then
            c.Offset(0, 10).Formula = "=((RC[-4]*RC[-2])/RC[-1])"
        End If
    Next c
   
End Sub

that code works for me when running just CONV.... thank you immensely.

just a side question, where can i get info on the R1C1 formulas? lol...... i'll hit up google i think

 

[footoo]

Do you need to find only one specific value in column E, or more than one ?

 

[orsm6]

Do you need to find only one specific value in column E, or more than one ?

Hi footoo.... it's a little hard to explain.

I need to find all the values but not every value will have the same formula. the code i was given earlier has done what i need it to do.

 

[footoo]

Hi footoo.... it's a little hard to explain.

I need to find all the values but not every value will have the same formula. the code i was given earlier has done what i need it to do.

Just thought you might want to have one macro only.

 

[orsm6]

Just thought you might want to have one macro only.

if you're up for the challenge please feel free lol. one would be good

 

[footoo]

if you're up for the challenge please feel free lol. one would be good

Post the values you want to find in column E together with the formula required for each value.

 

[orsm6]

Post the values you want to find in column E together with the formula required for each value.

hi footoo... here is the table i'm working with.
formula that goes into column O: =((K3*M3)/N3) row number needs to change as it fills down
happy if code looks for any of these values and if found inserts formula: Z004, Z003, Z002, CONV
sum total is always 5 cells to right of 'total raw and pack cost cell
sum total not always in the same cell in column O

COGs runner.xlsm
	E				I	J	K	L	M	N	O
1	Total Manufactured Cost
2	Material Type				Resource	Resource Text	Quantity	Base UoM	Price	Base Unit	Zero BoM
3	Z003				RA001003		-1.394	KG	$2,530.00	1000	-$3.53
4	Z003				RA001003		1.394	KG	$2,530.00	1000	$3.53
5	Z003				10003106		354.06	KG	$3,050.00	1000	$1,079.88
6	Z002				SA000450		521.174	KG	$3,987.05	1000	$2,077.95
7	Z003				RA001003		-1.046	KG	$2,530.00	1000	-$2.65
8	Z003				RA001003		1.046	KG	$2,530.00	1000	$2.65
9	Z003				RA001011		0.774	KG	$13,650.00	1000	$10.57
10	Z003				RA001030		31.645	KG	$4,407.00	1000	$139.46
11	Z003				RA001256		1.165	KG	$32,499.34	1000	$37.86
12	Z003				RA001260		2.326	KG	$2,450.00	1000	$5.70
13	Z003				RA001672		6.537	KG	$397.80	1000	$2.60
14	Z002				SA001212		1227.393	KG	$1,468.24	1000	$1,802.11
15	Z004				PA029751		6122.4	EA	$16.43	1000	$100.59
16	Z004				20061422		6060.6	EA	$78.31	1000	$474.61
17	Z004				20061414		6060.6	EA	$45.81	1000	$277.64
18	Z004				20061423		1010.1	EA	$392.00	1000	$395.96
19	Z004				PA021700		2040.8	EA	$7.55	1000	$15.41
20	Z004				PA027596		10.677	EA	$530.40	1000	$5.66
21						Total Raw & Pack Cost					$6,426.00
Layout

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O21:O22	Expression	=$J20="Total Raw & Pack Cost"	text	NO
O21:O22	Expression	=$J20="Total Conversion Cost"	text	NO
O21:O22	Expression	=$J20="Total Manufactured Cost"	text	NO
V3:V103,A3:P20,A24:P103,A21:I23,K21:P23	Expression	=$E3="CONV"	text	NO
K3:K103,V3:V103,O3:O103	Expression	=$K3="Unit Price"	text	NO
K3:K103,V3:V103,O3:O103	Expression	=$K3="Total"	text	NO
K3:K103,V3:V103,O3:O103	Expression	=$K3="Conversion"	text	NO
K3:K103,V3:V103,O3:O103	Expression	=$K3="Raw"	text	NO
K3:K103,V3:V103,O3:O103	Expression	=$K3="pack"	text	NO
A3:V20,A24:V103,A21:I23,K21:V23	Expression	=$E3="Z002"	text	NO
A3:V20,A24:V103,A21:I23,K21:V23	Expression	=$E3="Z003"	text	NO
A3:V20,A24:V103,A21:I23,K21:V23	Expression	=$E3="Z004"	text	NO
J24:J103,J3:J22,O24:O103,O3:O20,V24:V103,V3:V20	Expression	=$J3="Total Raw & Pack Cost"	text	NO
J24:J103,J3:J22,O24:O103,O3:O20,V24:V103,V3:V20	Expression	=$J3="Total Conversion Cost"	text	NO
J24:J103,J3:J22,O24:O103,O3:O20,V24:V103,V3:V20	Expression	=$J3="Total Manufactured Cost"	text	NO
J21:J22	Expression	=$E23="CONV"	text	NO
J21:J22	Expression	=$E23="Z002"	text	NO
J21:J22	Expression	=$E23="Z003"	text	NO
J21:J22	Expression	=$E23="Z004"	text	NO
O21:O22,V21:V22	Expression	=#REF!="Total Raw & Pack Cost"	text	NO
O21:O22,V21:V22	Expression	=#REF!="Total Conversion Cost"	text	NO
O21:O22,V21:V22	Expression	=#REF!="Total Manufactured Cost"	text	NO

 

[footoo]

You wrote previously that each value requires a different formula. I see that is not the case, so :

Sub enterformula()
Dim ray: ray = Array("Z002", "Z003", "Z004", "CONV")
[E:E].AutoFilter Field:=1, Criteria1:=ray, Operator:=xlFilterValues
Dim lr&: lr = Cells(Rows.Count, "E").End(3).Row
If lr > 2 Then Range("O3:O" & lr).SpecialCells(xlCellTypeVisible) _
    .FormulaR1C1 = "=RC[-4]*RC[-2]/RC[-1]"
[E:E].AutoFilter
End Sub

 

[orsm6]

You wrote previously that each value requires a different formula. I see that is not the case, so :

Sub enterformula()
Dim ray: ray = Array("Z002", "Z003", "Z004", "CONV")
[E:E].AutoFilter Field:=1, Criteria1:=ray, Operator:=xlFilterValues
Dim lr&: lr = Cells(Rows.Count, "E").End(3).Row
If lr > 2 Then Range("O3:O" & lr).SpecialCells(xlCellTypeVisible) _
    .FormulaR1C1 = "=RC[-4]*RC[-2]/RC[-1]"
[E:E].AutoFilter
End Sub

yeah i think i was thinking of another column or just not described what i was after properly.... thank you

 

[footoo]

yeah i think i was thinking of another column or just not described what i was after properly.... thank you

It occurs to me that what you might want is to put the formula in column O in every row where column E contains any value.
If so, the code can be much simpler :

Sub enterformula()
Range("O3:O" & Cells(Rows.Count, "E").End(3).Row) _
    .SpecialCells(xlCellTypeConstants).FormulaR1C1 = "=RC[-4]*RC[-2]/RC[-1]"
End Sub