VBA COde & Formulas

[Khalil Handal]

Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
in cell B5

My question is:
Can this be done using VBA code???

My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
......
.......

For all the cells that has formulas in the sheet.

Khalil

 

[Don Guillett]

you may use a regular formula thusly
range("a1").formula="=yourformula"
leaves the formula which may be changed to a value
range("a1").value=range("a1")

or this idea where you specify the ranges
range("a1").value=range("a2")*range("b1")

 

[Jim Cone]

You are very close.
The internal quote marks have to be doubled and the formula
on the worksheet needs an equal sign...
Range("C1").Formula = "=Mid(CELL(""filename"", A1), Find(""]"", CELL(""filename"", A1)) + 1, 255)"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Khalil Handal" <[email protected]>
wrote in message
Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
in cell B5
My question is:
Can this be done using VBA code???
My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
.......
For all the cells that has formulas in the sheet.
Khalil

 

[Khalil Handal]

Thank you both for the info.
Can't get clear thinking about .value and .formula (the difference between
them).
Khalil

you may use a regular formula thusly
range("a1").formula="=yourformula"
leaves the formula which may be changed to a value
range("a1").value=range("a1")

or this idea where you specify the ranges
range("a1").value=range("a2")*range("b1")
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in cell B5

My question is:
Can this be done using VBA code???

My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
.....
......

For all the cells that has formulas in the sheet.

Khalil

 

[Khalil Handal]

Sorry Jim,
I tried the code in a class module and as module also but It didn't show
anythinng in the cell B5 (insted of C1)?
Where do I put the code or I'am missing something????

Khalil

 

[Jim Cone]

It worked for me so I can only guess...
The code should go in a standard module.
The workbook must have been saved.
The code, as posted, should all be on one line.
You must "run" the code for it to work.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Khalil Handal"
<[email protected]>
wrote in message
Sorry Jim,
I tried the code in a class module and as module also but It didn't show
anythinng in the cell B5 (insted of C1)?
Where do I put the code or I'am missing something????
Khalil

 

[Don Guillett]

Back to your original. For the sheet name just use
range("h5").value=application.sum(range("a2:f3"))
range("b5").value=activesheet.name

 

[Khalil Handal]

Hi,
The last step "run" it asked for a macro name and I assign it a name. It
works when I run the macro and I STILL see the formula in the formula bar as
if I just typed it there.
The idea originaly is to have the value in cell B5 (or C1) being there
without seeing the original formula and avoiding also to protect the sheets.

I hope that this made it clear why I asked to have VBA Code at the begining!

Khalil

It worked for me so I can only guess...
The code should go in a standard module.
The workbook must have been saved.
The code, as posted, should all be on one line.
You must "run" the code for it to work.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Khalil Handal"
<[email protected]>
wrote in message
Sorry Jim,
I tried the code in a class module and as module also but It didn't show
anythinng in the cell B5 (insted of C1)?
Where do I put the code or I'am missing something????
Khalil

You are very close.
The internal quote marks have to be doubled and the formula
on the worksheet needs an equal sign...
Range("C1").Formula = "=Mid(CELL(""filename"", A1), Find(""]"",
CELL(""filename"", A1)) + 1, 255)"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

 

[Khalil Handal]

Hi Don,
Put this code in a standard modle or the sheet (Microsoft Excell objects)
Is it necessary that it be part of a macro?
See discussion with Jim.

Back to your original. For the sheet name just use
range("h5").value=application.sum(range("a2:f3"))
range("b5").value=activesheet.name
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in cell B5

My question is:
Can this be done using VBA code???

My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
.....
......

For all the cells that has formulas in the sheet.

Khalil

 

[Don Guillett]

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi Don,
Put this code in a standard modle or the sheet (Microsoft Excell objects)
Is it necessary that it be part of a macro?
See discussion with Jim.

Back to your original. For the sheet name just use
range("h5").value=application.sum(range("a2:f3"))
range("b5").value=activesheet.name
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in cell
B5

My question is:
Can this be done using VBA code???

My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
.....
......

For all the cells that has formulas in the sheet.

Khalil

 

[Khalil Handal]

Thanks for the tip. I will check it when return from work today.
Thanks again for both of you.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi Don,
Put this code in a standard modle or the sheet (Microsoft Excell objects)
Is it necessary that it be part of a macro?
See discussion with Jim.

Back to your original. For the sheet name just use
range("h5").value=application.sum(range("a2:f3"))
range("b5").value=activesheet.name
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi,
Formulas usualy begins with = followed by ....
ex. : =sum(a2:f3) is in cel h5
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in cell
B5

My question is:
Can this be done using VBA code???

My idea is: (might not be correct)!!
range ("B5").Select
formula=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
.....
......

For all the cells that has formulas in the sheet.

Khalil