Looping Problem

Shnoogan

New Member
Joined
Feb 17, 2004
Messages
3
Hello. I am having problems with a small loop I wrote. The loop is designed to look at a worksheet and loop through it until it reaches the last row. It is supposed to take the row number and add it to the formula and insert it into another cell. The code I am using is

**************************************************
Sub PrintAll()

Dim lnLast As Long
lnLast = Range("A4").End(xlDown).Row

For x = 3 To lnLast
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Data!A" & (x)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next x
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Data!A" & (lnLast)

End Sub
**************************************************

Each time it hits the piece of code: "=Data!A" & (x) it displays the correct data in the VB editor watch window. The correct value for the first time in the loop would be =Data!A3, but instead it puts =Data!'A3' into the selected cells value. The quotation mark in the formula is screwing it up but I can't figure out how to pass the value without the quotation mark. Any ideas? Thanks.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Remove the brackets from the variables.

ActiveCell.FormulaR1C1 = "=Data!A" & x

ActiveCell.FormulaR1C1 = "=Data!A" & lnLast

Tony
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
try:
newcell=data!a & (inlast)

ActiveCell.FormulaR1C1 = newcell
 

Shnoogan

New Member
Joined
Feb 17, 2004
Messages
3
That still did not work for me. I was able to get "Data!A4" into the cell but everytime I add "=" into the formula excel adds in the single quotes to the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,317
Messages
5,600,915
Members
414,416
Latest member
Nobu

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
Top