Looping with ranges not allowing Range(Cells,Cells) only Range("A:A")

0
votes

Background:

I have an array of 7 x 30 cells to loop through. For each of the 30 columns (Dim j), I am looping through rows (Dim i) 1 to see if a value is present and if so start copying the range of rows 2 through 7.

In attempting to use (e.g.):

Sheets("NAME").Range(Cells(i+1,1),Cells(i+7,1)).Copy

I am getting a 1004, Application-defined or object-defined error, that I have been able to alleviate using: 

Sheets("NAME").Range("A" & i+1 & ":A" & i+7 & ").Copy

Error on line from my code: 

Sheets("RM").Range(Cells(i + 6, 2), Cells(i + 13, 2)).Copy

Issue:

This has worked for items with known columns, but I am unsure how to proceed for a variable column, as well. I know that this doesn't work:

Sheets("NAME").Range(Cells(i+1,j),Cells(i+7,j)).Copy

I would need to find a way to fit this Range model without Cells to work for j, a variable column.

Question:

Is there a way to make this work using Range without using Cells?

My only guess is the following, which I believe uses incorrect syntax:

Sheets("NAME").Range(Columns(j) & i+1 & ":" & Columns(j) & i+1).Copy

Any help would be appreciated!

Edit: added the error 1004 name to this Issues section.

1
vbaexcel
What error are you getting? – Scott Craner
@ScottCraner Sorry, I missed that I only listed "an error" not that it was a 1004 error on that line. Updated the post, and added the exact line I received the error for (not a re-hash for the post). – Cyril
Then as the answer below shows you need to add the same parent to the Cells() as you did the Range(): Sheets("NAME").Range(Sheets("NAME").Cells(i+1,1),Sheets("NAME").Cells(i+7,1)).Copy – Scott Craner

1 Answers

0
votes

Try prefacing your Cells( call with the sheet i.e. Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(2, 2))

When you omit the sheet in a call to Cells or Range then it will default to a reference to the currently selected sheet, so if you're calling Sheets(Unselected Sheet).Range(Cells(1, 1)) it gets confused between which sheet you're referring to.