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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi

Remove the brackets from the variables.

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

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

Tony
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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