How to select multi offset range?

[alee001]

How to select multi offset cells in range?Anyone advice me...Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

With Range("combin_oth")
    If Target = .value Then
         Target.Copy: Range(".Offset(-8, 37).value, .Offset(-4, 37).value, .Offset(1, 37).value").PasteSpecial xlPasteFormulas 'error select range??
        Application.CutCopyMode = False
    End If
End With

End Sub

 

[alee001]

Try using union:

Sub a1084949a()
With Range("A1")
Range("A2").Copy: Union(.Offset(0, 1), .Offset(0, 4), .Offset(0, 6)).PasteSpecial xlPasteFormulas
End With
End Sub

The code only select B1, E1 and G1 but nothing paste formula from A1?

 

[Akuini]

The code only select B1, E1 and G1 but nothing paste formula from A1?

It offset from A1 but the formula is in A2, if you want to copy formula from A1 then just change
Range("A2") to Range("A1")

 

[Tim_Excel_]

Private Sub Worksheet_Change(ByVal Target As Range)

With Range("combin_oth")
    If Target = .value Then
         Target.Copy: Range(.Offset(-8, 37).Address(0, 0) & "," & .Offset(-4, 37).Address(0, 0)  & "," & .Offset(1, 37).Address(0, 0)).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
    End If
End With

End Sub

Or

Private Sub Worksheet_Change(ByVal Target As Range)

With Range("combin_oth")
    If Target = .value Then
         Target.Copy: Union(.Offset(-8, 37), .Offset(-4, 37), .Offset(1, 37)).PasteSpecial xlPasteFormulas 'as written by Akuini
        Application.CutCopyMode = False
    End If
End With

End Sub

 

[alee001]

You are right, I am make mistake, Many thanks.

 

[alee001]

Code2 is simpe over code1. Thanks. I learned to use "Union".

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.