MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Posted by Adasm on November 30, 2000 12:15 PM

Example of what i am trying to do (unsuccessful!!)

first = Range("a1").Value
scond = Range("a3").Value
ActiveCell.Formula = "=CONCATENATE first,""bill"",scond)"

but it doesn't work. any help please

Posted by topz on November 30, 2000 2:22 PM

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
ActiveCell.FormulaR1C1 = "=CELL(""CONTENTS"",Sheet1!R[x]C)"
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next x
End Sub

Posted by Celia on November 30, 2000 4:30 PM

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


Posted by Celia on November 30, 2000 4:32 PM


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


Posted by Celia on November 30, 2000 4:52 PM

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
For Each cell In Range("A1:A10")
cell.PrintOut Copies:=1
End Sub


Posted by TopZ on December 01, 2000 12:42 PM

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),

Posted by Celia on December 01, 2000 3:44 PM

OK. Try this :-

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


Posted by TOPZ on December 02, 2000 1:44 AM


Nice 1
that did the trick,

thank a lot,