Loop - how to address cells in a worksheet?

LesF

New Member
Joined
Aug 28, 2006
Messages
2
I am trying to use loop to copy data from another worksheet
using subscripts in VB:

i = 12
j = 100
Cells(12, 107) = Cells(i, j)
Cells(13, 108) = "=Input1!R5C5"

Loop works fine when I use it within current worksheet
line 3. In line 4 I would like to use subscripts i,j as well,
but I don't know how to do that. Could anyone, please,
show me how to do it? I have rather limited programming
skills as you see, so please make it as simple as possible.

Les
 

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
Can you post all of your code and the end result you're looking for?

@TAL: I think "subscript" means a "Sub vba script".
 
Upvote 0
Sorry for the confusion.

What I really want to do is very simple. The code below should make it clear:
For r_1 = 1 to 6000
For c_1 = 1 to 3
cells(r_1,c_1+110) = cells (r_1, c_1)
cells(r_1,c_1+115) = Input1!cells (r_1, c_1)
Next c_1
Next r_1

I am writing a macro using VB.
Line 3 works fine, but when I try to address cells located in
a worksheet called Input1 (line 4) I get 'compile error syntax
error' message when I try to run it.
 
Upvote 0
Try

Sheets(Input1).Cells (r_1, c_1)

Edit:

Plus, if Input1 is the actual name of the sheet and not a variable then

Sheets("Input1").Cells (r_1, c_1)
 
Upvote 0
Look up the word "Offset" as an offset is exactly what the subscript will do for you. I don't know if "Offset" will work on your line 4, but I believe it will. You can use variables when using Offset, so it allows you to loop through your worksheet as I think you want.

HTH,
 
Upvote 0
Offset, Cells method, potato, potato.

Why can't you just copy/paste the values all at once? If you record a macro you could do this with a couple of steps. You could post your recorded code if you like.
 
Upvote 0

Forum statistics

Threads
1,223,430
Messages
6,172,054
Members
452,444
Latest member
ShaImran193

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