Copy a Range using variables as arguments

Question

I'm new to VBA and am having issues with Range syntax and what are acceptable arguments.

the purpose of this code is as follows:

user inputs value into cell D5 on Sheet2
User activates code with button
searches "configs" sheet for value
copies corresponding range after locating value
pastes range back into Sheet2

the range I am attempting to copy paste is a block that starts with the selected cell (D5) on sheet "Configs", and continues until an empty cell is found.

Sub search()

    Dim GCell As Range,
    Dim box As Integer
    Dim Avio As String
    Dim Sheet2 As Worksheet, Configs As Worksheet
    Dim rw1 As String, rw2 As String

    Set Configs = ActiveWorkbook.Sheets("Configs")
    Set Sheet2 = ActiveWorkbook.Sheets("Sheet2")

    Avio = Range("D5").Value

    Set GCell = Configs.Cells.Find(Avio)

    box = 0

    LoopX:

    box = box + 1
    If GCell.Offset(box, 0).Value = "" Then

    rw1 = GCell.Offset(1, -1).Address
    rw2 = GCell.Offset(box, 2).Address

    Configs.Range("rw1:rw2").Copy <-- this syntax doesnt seem to work...
    Sheet2.Range("Avio.Offset(1,0)").Paste <-- I know this is wrong, but I would like the range to be pasted just below the selected cell on Sheet2 

    Else: GoTo LoopX
    End If

End Sub

So where is it giving an error? Have you stepped through the code looking at watch values? — Alan Waage

simpLE MAn simpLE MAn · Accepted Answer · 2013-12-04T20:59:16

Is this helping?

Sub search()

    Dim GCell As Range
    Dim box As Integer
    Dim Sheet2 As Worksheet, Configs As Worksheet
    Dim rw1 As String, rw2 As String

    Set Configs = ActiveWorkbook.Sheets("Configs")
    Set Sheet2 = ActiveWorkbook.Sheets("Sheet2")

    Dim rngAvio As Range
    Set rngAvio = Sheet2.Range("D5")

    Set GCell = Configs.Cells.Find(rngAvio.Value)

    box = 0
    Do While (GCell.Offset(box, 0).Value <> "")
        box = box + 1

        rw1 = GCell.Offset(1, -1).Address
        rw2 = GCell.Offset(box, 2).Address
        Configs.Range(rw1 & ":" & rw2).Copy rngAvio.Offset(1, 0)
    Loop

End Sub

Copy a Range using variables as arguments

1 Answers