CODEWITHSUNDEEP
excelvbafor-loopselectoffset
Xavi
Xavi

Reputation: 207

Apply formula, offset three columns from selected cells

I created VBA code to apply a formula which should do the following: when the user selects a range of cells, the formula is applied 3 columns on the right side of the data of the selection. For example if the user selects range G8:G18, when the user executes the macro, the formula should be applied on range J8:J18 from the data of range G8:G18

However the formula is in range G8 instead of being applied on range J8:J18.

enter image description here

Sub ghjkk()
Dim c As Range
Dim rng As Range
Set rng = Selection.Offset(0, 3)
For Each c In rng
ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=0.2,""Y5"",IF(RC[-3]=0.1,""Y6"",IF(RC[-3]=0,""V0"",IF(RC[-3]=0.021,""Y3"",IF(RC[-3]=0.055,""Y4"",FALSE)))))"
Next c
End Sub

Upvotes: 1

Views: 538

Answers (2)

Error 1004
Error 1004

Reputation: 8230

If needed, change sheet name and range & import the below code on Worksheet_Change Event on the specific sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("G8:G18")) Is Nothing Then

       Application.EnableEvents = False

            If Cells(Target.Row, 4).Value = "0.2" Then
                Cells(Target.Row, 10).Value = "Y5"
            ElseIf Cells(Target.Row, 4).Value = "0.1" Then
                Cells(Target.Row, 10).Value = "Y6"
            ElseIf Cells(Target.Row, 4).Value = "0" Then
                Cells(Target.Row, 10).Value = "V0"
            ElseIf Cells(Target.Row, 4).Value = "0.021" Then
                Cells(Target.Row, 10).Value = "Y3"
            ElseIf Cells(Target.Row, 4).Value = "0.055" Then
                Cells(Target.Row, 10).Value = "Y4"
            Else: Cells(Target.Row, 10).Value = "False"
            End If

        Application.EnableEvents = True
    End If

End Sub

Upvotes: 1

Sphinx
Sphinx

Reputation: 660

Try 

Sub ghjkk()
    Dim c As Range
    Dim rng As Range
    Set rng = Selection.Offset(0, 3)
    For Each c In rng
        c.FormulaR1C1 = _
 "=IF(RC[-3]=0.2,""Y5"",IF(RC[-3]=0.1,""Y6"",IF(RC[-3]=0,""V0"",IF(RC[-3]=0.021,""Y3"",IF(RC[-3]=0.055,""Y4"",FALSE)))))"
    Next c
End Sub

Upvotes: 4

Related Questions