Help please!

J

Joe Bannister

Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input and e-
mailed. The receiver checks various details and alters as
necessary. The receiver also needs to input comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting strings to
formulae?

Any help greatly appreciated.

Cheers

Joe
 
F

Frank Kabel

Hi
have a look at the formula or formulaR1C1 property of the
range object. This allows you to insert formulas
programmatically
 
J

Joe Bannister

Hi Frank,

Thanks but can you put that in laymans terms?

Cheers

Joe
-----Original Message-----
Hi
have a look at the formula or formulaR1C1 property of the
range object. This allows you to insert formulas
programmatically
-----Original Message-----
Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input and e-
mailed. The receiver checks various details and alters as
necessary. The receiver also needs to input comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting strings to
formulae?

Any help greatly appreciated.

Cheers

Joe

.
Click to expand...
.
Click to expand...
 
F

Frank Kabel

Hi Joe
the solution depens on what you're trying to achieve. To
just inerst a formula into a cell you could use the
following statemebnt in VBA

sub foo()
activesheet.range("A1").formula="=SUM(B1:B100)"
end sub

There're some examples in the VBA help for formula and
formulaR1C1

-----Original Message-----
Hi Frank,

Thanks but can you put that in laymans terms?

Cheers

Joe
-----Original Message-----
Hi
have a look at the formula or formulaR1C1 property of the
range object. This allows you to insert formulas
programmatically
-----Original Message-----
Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input and e-
mailed. The receiver checks various details and alters as
necessary. The receiver also needs to input comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting strings to
formulae?

Any help greatly appreciated.

Cheers

Joe

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
J

Joe Bannister

Hi Frank,

Thanks, that has helped alot, i now know that i can do
what i want using the following code:

ActiveWorkbook.Save
Sheets("abc").Select

Dim comparative7 As String
comparative7 = "C:\archive\["
comparative7 = comparative7 & CStr(Range("D3").Text)
comparative7 = comparative7 & "_"
comparative7 = comparative7 & CStr(Range("E4").Text)
comparative7 = comparative7 & "_food.xls]food
collation'!O7"
Range("T7").Formula = "='" & comparative7

The next problem is that there are 34 comparatives i need
to enter so is there any way of looping the "O7"
reference in the formula?

If not, i will have to repeat the above code 34 times for
O8, O9, O10 etc

Cheers

Joe
-----Original Message-----
Hi Joe
the solution depens on what you're trying to achieve. To
just inerst a formula into a cell you could use the
following statemebnt in VBA

sub foo()
activesheet.range("A1").formula="=SUM(B1:B100)"
end sub

There're some examples in the VBA help for formula and
formulaR1C1

-----Original Message-----
Hi Frank,

Thanks but can you put that in laymans terms?

Cheers

Joe
-----Original Message-----
Hi
have a look at the formula or formulaR1C1 property of the
range object. This allows you to insert formulas
programmatically
-----Original Message-----
Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input and e-
mailed. The receiver checks various details and
Click to expand...
alters
as
necessary. The receiver also needs to input comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting
Click to expand...
strings
to
formulae?

Any help greatly appreciated.

Cheers

Joe

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
F

Frank Kabel

Hi Joe
and in which cell should the formula go?. That is how do
you want through the target cells? (starting in T7)
-----Original Message-----
Hi Frank,

Thanks, that has helped alot, i now know that i can do
what i want using the following code:

ActiveWorkbook.Save
Sheets("abc").Select

Dim comparative7 As String
comparative7 = "C:\archive\["
comparative7 = comparative7 & CStr(Range("D3").Text)
comparative7 = comparative7 & "_"
comparative7 = comparative7 & CStr(Range("E4").Text)
comparative7 = comparative7 & "_food.xls]food
collation'!O7"
Range("T7").Formula = "='" & comparative7

The next problem is that there are 34 comparatives i need
to enter so is there any way of looping the "O7"
reference in the formula?

If not, i will have to repeat the above code 34 times for
O8, O9, O10 etc

Cheers

Joe
-----Original Message-----
Hi Joe
the solution depens on what you're trying to achieve. To
just inerst a formula into a cell you could use the
following statemebnt in VBA

sub foo()
activesheet.range("A1").formula="=SUM(B1:B100)"
end sub

There're some examples in the VBA help for formula and
formulaR1C1

-----Original Message-----
Hi Frank,

Thanks but can you put that in laymans terms?

Cheers

Joe

-----Original Message-----
Hi
have a look at the formula or formulaR1C1 property of
the
range object. This allows you to insert formulas
programmatically
-----Original Message-----
Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input and e-
mailed. The receiver checks various details and alters
as
necessary. The receiver also needs to input
comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting strings
to
formulae?

Any help greatly appreciated.

Cheers

Joe

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
J

Joe Bannister

Hi Frank,

Thanks for all your help.
I think i've sorted it out as follows:

ActiveWorkbook.Save
Sheets("abc").Select

For x = 7 To 18
Dim comparative As String
comparative = "C:\archive\["
comparative = comparative & CStr(Range("D3").Text)
comparative = comparative & "_"
comparative = comparative & CStr(Range("E4").Text)
comparative = comparative & "_food.xls]food
collation'!O"
comparative = comparative & x
Cells(x, 20).Select
ActiveCell.Formula = "='" & comparative
Next x
-----Original Message-----
Hi Joe
and in which cell should the formula go?. That is how do
you want through the target cells? (starting in T7)
-----Original Message-----
Hi Frank,

Thanks, that has helped alot, i now know that i can do
what i want using the following code:

ActiveWorkbook.Save
Sheets("abc").Select

Dim comparative7 As String
comparative7 = "C:\archive\["
comparative7 = comparative7 & CStr(Range("D3").Text)
comparative7 = comparative7 & "_"
comparative7 = comparative7 & CStr(Range("E4").Text)
comparative7 = comparative7 & "_food.xls]food
collation'!O7"
Range("T7").Formula = "='" & comparative7

The next problem is that there are 34 comparatives i need
to enter so is there any way of looping the "O7"
reference in the formula?

If not, i will have to repeat the above code 34 times for
O8, O9, O10 etc

Cheers

Joe
-----Original Message-----
Hi Joe
the solution depens on what you're trying to achieve. To
just inerst a formula into a cell you could use the
following statemebnt in VBA

sub foo()
activesheet.range("A1").formula="=SUM(B1:B100)"
end sub

There're some examples in the VBA help for formula and
formulaR1C1


-----Original Message-----
Hi Frank,

Thanks but can you put that in laymans terms?

Cheers

Joe

-----Original Message-----
Hi
have a look at the formula or formulaR1C1 property of
the
range object. This allows you to insert formulas
programmatically
-----Original Message-----
Hello all,

Can anyone help please?

I have a spreadsheet which is used for data input
Click to expand...
and
e-
mailed. The receiver checks various details and alters
as
necessary. The receiver also needs to input
comparatives
from the previous year. The comparatives are on an
identical spreadsheet,albeit with a different name, in
lets say C:\archive. The archived file is 01_04_abc.xls
and the current file 01_05_abc.xls

Now the receivers are not too good with excel and so
cannot be trusted to enter formulae or links to the
archived spreadsheets. Moreover there is too much data
for them to manually input.

So, my question is, is there a way of getting formulae
into cells using vba? Again the formula needs to be eg
='C:\archive\[01_04_abc.xls]sheet1'!$A$1

Can it be done using input boxes or converting strings
to
formulae?

Any help greatly appreciated.

Cheers

Joe

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Top