Trying to put a variable in the middle of a formula in code

same sort of promblem for me

Sub printoff_in()
'VBA macro for MS Excel
'sheet1 as a list on it A1:A10
'I need to print off sheets with every entry from the list
'this will go onto sheet2
'this will not work
'do anybody know what Im doing wrong (alot)

For x = 1 To 10
Range("sheet2!A1").Select
ActiveCell.FormulaR1C1 = "=CELL(""CONTENTS"",Sheet1!R[x]C)"
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next x
End Sub

Try this :-
ActiveCell.Formula = "=CONCATENATE(""" & first & """,""Bill"",""" & Scond & """)"

Or if you just want the value in the active cell instead of the formula :-
ActiveCell.Value = first & Scond

Celia

Correction

Last part should read :-
ActiveCell.Value = first & "Bill" & Scond

Celia

Sub printoff_in()

If what you're trying to do is print the contents of cells A1:A10 on Sheet1 with one cell per print-out, then try this :-

Sub printoff_in()
Dim cell As Range
Sheets("Sheet1").Activate
For Each cell In Range("A1:A10")
cell.PrintOut Copies:=1
Next
End Sub

Celia

no sorry,
it needs a lookup type thing,
as the info on sheet2 is a sheet to be printed out for stock taking, so there are haeding & the like for date & amount of items left in stock.
these need to be printed off, and filled in later by 'the work men' (a lower lifeform),
Any-O
TopZ

OK. Try this :-

For x = 1 To 10
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A" & x)
Sheets("Sheet2").PrintOut Copies:=1
Next

Celia

Celia

SORTED,
Nice 1
that did the trick,

thank a lot,
TopZ