ForEach Maybe... Or just basic excel help

[JohnCG]

I am new to visual basic and I have been using the record macro feature to try and learn how it works but I have run into an issue. I have a list of values in column J that varies in length per sheet and I want each cell in column J that has a value to run through a macro. Placing the value from column J in T15, then running the macro and then having the result from U27 placed in the row corresponding to its column J location in column W. I have added the macro I wish to run once a value is placed in T15.

Sub AgrsvShift()
'
' AgrsvShift Macro
'

'
    Range("T16").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-1]C>1,INDEX(C[-7],MATCH(R[-1]C,Extract,0)),""--"")"
    Range("T17").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-1]C/R[-1]C[1],""--"")"
    Range("T18").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-1]C*R[-7]C,""--"")"
    Range("U19").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]>1,R[-6]C[-1],""--"")"
    Range("T19").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(((RC[1]*R[-4]C[1])/R[-4]C),""--"")"
    Range("T20").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-1]C[1]-R[-1]C,""--"")"
    Range("T21").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-2]C+R[-2]C[1],""--"")"
    Range("U23").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-3]C[-1]*R[-5]C[-1],""--"")"
    Range("U24").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-3]C[-1]*R[-6]C,""--"")"
    Range("U25").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-4]C[-1]*(1-R[-14]C[-1]),""--"")"
    Range("U26").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-3]C-R[-2]C-R[-1]C,""--"")"
    Range("U27").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(R[-1]C/R[-8]C,""--"")"
End Sub

Thanks in advance for any help

 

[wigi]

This seems to be a better code:

Sub AgrsvShift()
    Range("T16").Formula = "=IF(R[-1]C>1,INDEX(C[-7],MATCH(R[-1]C,Extract,0)),""--"")"
    Range("T17").Formula = "=IFERROR(R[-1]C/R[-1]C[1],""--"")"
    Range("T18").Formula = "=IFERROR(R[-1]C*R[-7]C,""--"")"
    Range("T19").Formula = "=IFERROR(((RC[1]*R[-4]C[1])/R[-4]C),""--"")"
    Range("T20").Formula = "=IFERROR(R[-1]C[1]-R[-1]C,""--"")"
    Range("T21").Formula = "=IFERROR(R[-2]C+R[-2]C[1],""--"")"
    Range("U19").Formula = "=IF(R[-6]C[-1]>1,R[-6]C[-1],""--"")"
    Range("U23").Formula = "=IFERROR(R[-3]C[-1]*R[-5]C[-1],""--"")"
    Range("U24").Formula = "=IFERROR(R[-3]C[-1]*R[-6]C,""--"")"
    Range("U25").Formula = "=IFERROR(R[-4]C[-1]*(1-R[-14]C[-1]),""--"")"
    Range("U26").Formula = "=IFERROR(R[-3]C-R[-2]C-R[-1]C,""--"")"
    Range("U27").Formula = "=IFERROR(R[-1]C/R[-8]C,""--"")"
End Sub

 

[wigi]

For the loop:

For Each rng In Range("J1:J100")
  If Len(rng.Value) then
    Range("T15").Value = rng.Value
    AgrsvShift
    Range("W" & Rng.Row).Value = Range("U27").Value 
  End If
Next

I do not see why you need to execute AgrsvShift a number of times, but this will give you initial code to experiment with.

Also, change the range in column J if needed.

 

[JohnCG]

Thank you for your help! I'll give it a shot

 

[JohnCG]

This was the start I needed, Thanks!!

 

[wigi]

Ok, nice, glad it helps.