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

 

[Tim_Excel_]

/

 

[Norie]

Where do you want to offset from?

 

[Tim_Excel_]

Seeing as how you only want to paste to three separate ranges, why not simply use three lines?

    If Target = .value Then
         Target.Copy: .Offset(-8, 37).PasteSpecial xlPasteFormulas
[FONT=Verdana]              Target.Copy: [/FONT].Offset(-4, 37).PasteSpecial xlPasteFormulas
[FONT=Verdana]              Target.Copy: [/FONT].Offset(1, 37).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
    End If

 

[alee001]

I want copy target cell (i.e. Range("combin_oth')) to 3 cells only formula as follow:
Range("combin_oth").offset(-8,37).value
Range("combin_oth").offset(-4,37).value
Range("combin_oth").offset(1,37).value

 

[alee001]

Seeing as how you only want to paste to three separate ranges, why not simply use three lines?

    If Target = .value Then
         Target.Copy: .Offset(-8, 37).PasteSpecial xlPasteFormulas
[FONT=Verdana]              Target.Copy: [/FONT].Offset(-4, 37).PasteSpecial xlPasteFormulas
[FONT=Verdana]              Target.Copy: [/FONT].Offset(1, 37).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
    End If

Can I group in range?

 

[Tim_Excel_]

[COLOR=#333333]Target.Copy: Range(.Offset(-8, 37) & "," & .Offset(-4, 37) [/COLOR][COLOR=#333333]& "," &[/COLOR][COLOR=#333333] .Offset(1, 37)).PasteSpecial xlPasteFormulas[/COLOR]

This ought to do the trick. Tested and works with

Range(Cells(1, 1).Address(0, 0) & ", " & Cells(1, 3).Address(0, 0) & ", " & Cells(1, 5).Address(0, 0)).Select

 

[alee001]

[COLOR=#333333]Target.Copy: Range(.Offset(-8, 37) & "," & .Offset(-4, 37) [/COLOR][COLOR=#333333]& "," &[/COLOR][COLOR=#333333] .Offset(1, 37)).PasteSpecial xlPasteFormulas[/COLOR]

This ought to do the trick. Tested and works with

Range(Cells(1, 1).Address(0, 0) & ", " & Cells(1, 3).Address(0, 0) & ", " & Cells(1, 5).Address(0, 0)).Select

Code1 Not work with error 1004 ['Range' method ('_Worksheet' obiect fail], I had add ActiveWorksheet.Range(..) but not work too.
Code2 I had considered but may be complexed with count cell?

 

[Tim_Excel_]

The second code was, as I mentioned, the code I tried my method on- and does not apply to your question.

Managed to make it work with:

Target.Copy
Range(.Offset(-8, 37).Address(0, 0) & "," & .Offset(-4, 37).Address(0, 0)  & "," & .Offset(1, 37).Address(0, 0)).PasteSpecial xlPasteFormulas

 

[Akuini]

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