Need to reference a second range

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
I got this macro from a board member earlier. It works well

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> String: ws = ActiveSheet.Name<br><SPAN style="color:#00007F">Set</SPAN> rng = Range("C3:E6, C9:E12")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rng<br>Range(cell.Value).Select<br><SPAN style="color:#007F00">'Execute Procedure</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br></FONT>

What I would like to do now is reference a second range in the same macro and have the cell value transferred to the second range, again one cell at a time. Basically, it will go something like this:

1st Range ( list of sheet coordinates)
Gather first coordinate
Go to sheet and cell
Execute procedure
Goto Second Range (another list of coordinates)
Goto sheet and cell
Execute procedure
Go back to first Range
Gather second coordinates
etc..... through all 357 cells in the first range.

I am not sure how to reference the second range and cells.

Thank you,
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Bill

This is not clear. Instead of posting pseudo-code please explain exactly what you want the macro to do.
 
Upvote 0
I need to have the macro above go through to the 'Execute Procedure step. At that point, I need to have the macro look at a second range, select the first cell in that second range and execute a procedure with the data collected in the first range, in the second cell range. Then I need the macro to read the Next Cell line and go back and gather data from the second cell of the first range, execute the procedure... and loop through until the range is exhausted.

Perhaps even more basically, I need to copy the data from the first cell in the first range and paste to the first cell in the second range, then repeat using the second cell in the first range and pasting to the second cell in the second range, etc.... until I have gone through all the cells in the first range. Obviously with over a hundred cells I am trying to not write a macro that has over 100 copy and paste routines.

But I don't know how to reference the second range and cells in it. Does that help, casue I sure need some. Thanks,
 
Last edited:
Upvote 0
I don't understand why you want to do this with individual cells. You can do this wholesale (although you will run into problems with your non-contiguous ranges). Try like this using contiguous blocks:

Code:
Sub test()
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("C3:E6")
Set rng2 = Range("G3:I6")
rng2.Value = rng1.Value
Set rng1 = Range("C9:E12")
Set rng2 = Range("G9:I12")
rng2.Value = rng1.Value
End Sub
 
Upvote 0
The second range is totally noncontiguous and every time you enter the new data, the columns will be the same, but the rows will change. So I have to literally do this one cell at a time. As always, thank you for your help.
 
Upvote 0
Try referring to each contiguous area individually like this

Code:
Sub test()
Dim rng1 As Range, rng2 As Range, i As Integer
Set rng1 = Range("C3:E6")
Set rng2 = Range("G3:I6")
If rng1.Cells.Count <> rng2.Cells.Count Then Exit Sub
For i = 1 To rng1.Cells.Count
    rng2.Cells(i).Value = rng1.Cells(i).Value
Next i
Set rng1 = Range("C9:E12")
Set rng2 = Range("G9:I12")
If rng1.Cells.Count <> rng2.Cells.Count Then Exit Sub
For i = 1 To rng1.Cells.Count
    rng2.Cells(i).Value = rng1.Cells(i).Value
Next i
End Sub
 
Upvote 0
There is onoy one problem left, but I will understand if I am troubling you at this point. Still, if you are willing to entertain me a little more, I tried to set up the second range where the data will go like this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> rng1 <SPAN style="color:#00007F">As</SPAN> Range, rng2 <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Set</SPAN> rng1 = Range("C3:E6")<br><SPAN style="color:#00007F">Set</SPAN> rng2 = Sheets("Sheet2").Range("B2,B4,B6,G3,G6,G8,G12,G15,G18, H1,H11,H15")<br><SPAN style="color:#00007F">If</SPAN> rng1.Cells.Count <> rng2.Cells.Count <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rng1.Cells.Count<br>    rng2.Cells(i).Value = rng1.Cells(i).Value<br><SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">Set</SPAN> rng2 = Sheets("Sheet2").Range("B2,B4,B6,G3,G6,G8,G12,G15,G18, H1,H11,H15")<br><br><SPAN style="color:#00007F">If</SPAN> rng1.Cells.Count <> rng2.Cells.Count <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rng1.Cells.Count<br>    rng2.Cells(i).Value = rng1.Cells(i).Value<br><SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

but instead of finding the data spread out, it simple made a vertical row starting at B2. Is there another way I should refer to the second range since it is not really a range?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,479
Messages
6,113,894
Members
448,530
Latest member
yatong2008

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top