CODEWITHSUNDEEP
vbaexcel
BetaOp9
BetaOp9

Reputation: 71

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

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.

Upvotes: 1

Views: 489

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

Related Questions