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
Range("sheet2!A1").Select
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

Celia

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

Correction

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

Celia


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

Celia

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),
Any-O
TopZ

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
Next

Celia


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

Celia


SORTED,
Nice 1
that did the trick,

thank a lot,
TopZ