CODEWITHSUNDEEP
excelvba
user3062472
user3062472

Reputation: 1

Copy a Range using variables as arguments

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:

  1. user inputs value into cell D5 on Sheet2
  2. User activates code with button
  3. searches "configs" sheet for value
  4. copies corresponding range after locating value
  5. 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

Upvotes: 0

Views: 887

Answers (1)

simpLE MAn
simpLE MAn

Reputation: 1622

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

Upvotes: 1

Related Questions