CODEWITHSUNDEEP
vbaexcel
L. L. Long
L. L. Long

Reputation: 23

Use offset to find empty cell and paste range of values

I have a vector of values (C8:AM8) in an excel worksheet (XYZ). Using VBA code, what I need to do is copy and paste those cells starting at C11, BUT if C11 has a value, then I need to offset by 1 row and paste the above values (C8:AM8) in C12, or if C12 has a value, then the next empty row.

Basically, as of now, I have something that looks like this....

Dim CellName As String
Dim CheckCell As Range
Dim RowA As Range

CellName = "C8:AM8"

For Each CheckCell In Sheets("XYZ").Range("C11:C50")
    If Not IsEmpty(CheckCell.Value) Then 
        Set RowC = CheckCell.Offset(1, 0)   
    Else
        Range(CellName).Copy Range("C11" + RowC) 
    End If      
Next CheckCell

Minimally, I know that the issue is here:

Range(CellName).Copy Range("C11" + RowC)

I am new to VBA and so I'm not sure of the proper syntax to use. I've tried multiple permutations with the RowC inside the parentheses, outside the parentheses, in quotes, outside of quotes, etc. and I can't seem to nail it. I think this issue is very simple, but I am clearly missing something critical and can't seem to find what I need online.

Any help would be much appreciated.

Thank you!

Upvotes: 2

Views: 969

Answers (3)

A.S.H
A.S.H

Reputation: 29352

The following line copies your range after the last non-empty cell in row C:

Worksheets("XYZ").Range("C8:AM8").Copy Worksheets("XYZ").Range("C1000000").End(xlUp).Offset(1)

Upvotes: 1

IR_IR
IR_IR

Reputation: 184

You need to put this line of code:

Range(CellName).Copy Range("C11" + RowC)

Like this:

Dim my_row as integer
my_row=11
Range(CellName).Copy Range("C" & (my_row  + RowC))

Upvotes: 0

user3598756
user3598756

Reputation: 29421

I'd go with:

With Sheets("XYZ")
    .Range("C8:AM8").Copy .Range("C" & WorksheetFunction.Max(11, .Cells(.Rows.count, "C").End(xlUp).Offset(1).Row))
End With

Upvotes: 1

Related Questions