Excel VBA - Using range variable to dynamically set range a set # of cells

Question

I currently have my code looking down a column in a specific worksheet for a certain value in a cell. If it finds that value in a cell, it uses that cell as the anchor location for the rest of the subroutine. Here is the relevant code.

With DailyWS
    Set DailyTable = .Range("C7:Q21")
    Set Week = .Range("F4")
End With 'DailyWS

Set rngY = BackupWS.Range("B1:B10000").Find(Replace(Week.Value, " Week", ""), lookat:=xlPart)

If rngY Is Nothing Then
    Set rngY = BackupWS.Range("B1").Offset(LastRow, 0)
End If

With BackupWS
    Set BackupTable = rngY.Offset(0, 2)
End With 'BackupWS

I need to take the information in the DailyTable range and copy it to the BackupTable range. As it's currently coded, it only copies one cell because rngY only returns one cell [for other parts of the subroutine I still need rngY to be this one cell].

So I need is for it to copy DailyTable starting at the rngY cell. For example, if rngY returns as C1, then I would need to set BackupTable to range C1:Q15 then perform the .Offset(LastRow, 0) to that.

I'm unsure how to successfully manipulate this to do that. If you need clarification, please ask.

user3598756 · Accepted Answer · 2017-02-21 07:53:39Z

2

from your request:

if rngY returns as C1, then I would need to set BackupTable to range C1:Q15 then perform the .Offset(LastRow, 0) to that.

change:

If rngY Is Nothing Then
    Set rngY = BackupWS.Range("B1").Offset(LastRow, 0)
End If

With BackupWS
    Set BackupTable = rngY.Offset(0, 2)
End With 'BackupWS

to:

With BackupWS
    Set rngY = .Range("B1:B10000").Find(Replace(Week.Value, " Week", ""), lookat:=xlPart)

    If rngY Is Nothing Then Set rngY = .Range("B1").Offset(LastRow, 0)

    Set BackupTable = .Range(rngY, .Range("Q15")).Offset(LastRow, 0)
End With 'BackupWS

but you may want to add more details to your actual goal

answered Feb 21, 2017 at 7:53

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BetaOp9 Over a year ago

Perfect! I was able to make the adjustments need to the change you made to get it to work exactly how I wanted. Thanks!

Collectives™ on Stack Overflow

Excel VBA - Using range variable to dynamically set range a set # of cells

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related