Copy a Formula If a certain Conditions are met

[Chris van der Berg]

Good Day

I wrote a very clumsy macro to achieve my objective.
However I can't get the macro to stop.

Can you please help.

Many Thanks

Chris

 

[Chris van der Berg]

Good Day
I came up with this VBA code, that resolve all the issues

Sub Copy_Formula()
'copy formula from cell U4 to cell A 2
If Range("R1") = 1 Then
Range("A2:A40").Formula = Range("U2").Formula
End If
If Range("R1") = 2 Then
Range("A2:A40").Formula = Range("U3").Formula
End If
If Range("R1") = 3 Then
Range("A2:A40").Formula = Range("U4").Formula
End If
If Range("R1") = 4 Then
Range("A2:A40").Formula = Range("U5").Formula
End If
If Range("R1") = 5 Then
Range("A2:A40").Formula = Range("U6").Formula
End If
If Range("R1") = 6 Then
Range("A2:A40").Formula = Range("U7").Formula
End If
If Range("R1") = 7 Then
Range("A2:A40").Formula = Range("U8").Formula
End If
If Range("R1") = 8 Then
Range("A2:A40").Formula = Range("U9").Formula
End If
If Range("R1") = 9 Then
Range("A2:A40").Formula = Range("U10").Formula
End If
If Range("R1") = 10 Then
Range("A2:A40").Formula = Range("U11").Formula
End If
If Range("R1") = 11 Then
Range("A2:A40").Formula = Range("U12").Formula
End If
If Range("R1") = 12 Then
Range("A2:A40").Formula = Range("U13").Formula
End If
End Sub

 

[YasserKhalil]

Try this instead

Dim I As Integer
For I = 1 To 12
    If Range("R1") = I Then Range("A2:A40").Formula = Range("U" & I + 1).Formula: Exit Sub
Next I

 

[YasserKhalil]

Or this

Dim arr1, arr2, I As Integer
arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
arr2 = Array("U2", "U3", "U4", "U5", "U6", "U7", "U8", "U9", "U10", "U11", "U12", "U13")

For I = LBound(arr1) To UBound(arr1)
    If Range("R1").Value = arr1(I) Then
        Range("A2:A40").Formula = Range(arr2(I)).Formula: Exit Sub
    End If
Next I

 

[Chris van der Berg]

Hi Yasser,

Thanks for the codes, both are super nice and works well.

Regards

Chris

 

[YasserKhalil]

You're welcome. Thanks for the feedback
Glad I can offer some help