Can I get the Formula(s) in any cell as Text using VBA written to another cell(s).!

[e4excel]

Can I get the Formula(s) in any cell as Text using VBA written to another cell(s).!

Dear Forum,

Is it possible to get Formulas in any cell or a Given Range in a Single Row or Row below Column Headings to be written as text to another cell..

Example:

Lets Say Col A - A1 - Sr.No - COlumn heading.
CEll A2 = ROW()-1 is the formula in the cell I need to have this formula written in another cell..

I know I can do it manually but I want this to be done using VBA as there are lot of formulas which are already there and typing them would take a lot of time..

Lets say I need this formula to be written as Text like "=ROW()-1" in cell Z2..

Is this possible, like ti Just give the range and then i get these formulas as TEXT VALUES in different cells..

Warm Regards
e4excel

[TMS]

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Regards, TMS

[e4excel]

Thanks TMShucks, you have been very helpful as I ciuld compare the two REference style which after carefully observing realised that they are different..

But I want the content of the cell Z2 to actually pick the formula in the cell A2 and not explicitly write the formula..
I am wanting to convert all the formulas into VBA code, so just wanted the formula as Text in the cell Z2..

I will applying the same logic of .Value= .Value as you have shown but since there are lot of formulas, I wanted to get this method and keep it handy as some formulas will have to be treated differently especially when they have the {"} quotes in them..

Thnks in advance.

[JosephP]

I reckon you mean

Please Login or Register  to view this content.

[e4excel]

JOseph,

Thanks but can you please attach a sample file so that I can understand that better as I am no good in VBA..

[e4excel]

I think I got it I needed to paste it in the MOdule..

Can I get like for all the selected cells one below the other..

Lets say If I start from the column A :E ofocurse only for the Row()=2 as the rest of the Rows will be the same formula

Thnks in advance

[JosephP]

You need to add that code to a normal module in whatever workbook you want to use it and then in your cells just type
=cellformula(A1)
to get the formula from A1 and so on.

[e4excel]

I dont know how to explain this but I want to keep the Formula the COnditional formatting for every cell and the particular column in such a way that I can convert the entire Formula + Formatting into VBA values..

Like if each Column hs seperate format there are certain Numeric columns then there are Dates and then there are text values which are derived through formulas , so it also possible to get the complete details of the selected cells or range of cells.?

[JosephP]

Gotta say I have no clue what you mean by that. how could you format a formula as a date?

[e4excel]

No No Nah, I did not explain correctly, what I meant was I need to get the Formula , FOrmatting for a cell as Information..

Example the way you gave me a function to give the formula in any cell as TExt can I also get the Fornmatting information for the cell,

Example like if the contents are RED colur with BOLD Italics and Underline and Font " Book ANtiqua"..

Can I get this information below the formula :

Formula = ROW()-1

Formatting:

FontColour = RED
FOntSize =
BOLD =TRUE
UNDERLINE =TRUE
ITALICS = TRUE
Font = "BOOK ANTIQUA"

Is this possible too?

Then also if there's any conditional formatting information?

Warm REgards
e4excel

[JosephP]

yeah you could do that - it's just a load more lines checking every possible formatting option. that's assuming you want it all returned in the same cell. if not, you need more functions or a sub instead of a function. conditional formatting gets way more complicated if you want to list the formatting that is actually applied and not just the rules that are applied to the cell.
the bad news is i don't have time to do either of those for you right now.

[e4excel]

yeah you could do that - it's just a load more lines checking every possible formatting option. that's assuming you want it all returned in the same cell. if not, you need more functions or a sub instead of a function. conditional formatting gets way more complicated if you want to list the formatting that is actually applied and not just the rules that are applied to the cell.
the bad news is i don't have time to do either of those for you right now.

ITs really heartening to know that its possible but also it will be not done as you are busy..

I need this information one below the other and not in one cell.

You are right I want only the ones that are applied...

IF conditional formatting is out of the equation will it be little simpler..

[JosephP]

best i can do for now is a general purpose function

Please Login or Register  to view this content.

but you need to pass it the information you want in vba format - for example:

=cellinfo($A$2, "formula")
=cellinfo($A$2, "font.bold")
=cellinfo($A$2, "font.italic")
=cellinfo($A$2, "interior.color")

and so on. it has to be volatile since formatting changes don't trigger recalculation and that means it will slow things down if you have lots of them. why do you need this anyway?

[e4excel]

I am going to make all the changes in the Formula Sheet and then want it to convert into Fomula VBA Values.. as shown TMShucks..

BUt can I not get without complicating mattes such as passing different format..

Please Login or Register  to view this content.

Like I expect that the answer should like this:

FIrst the formula

Then the Applied FOrmatting lines

Atleast if I get it for one cell it will still be good..

[e4excel]

Is there anything for the number formatt?

[e4excel]

Wow thats great though I have not got it completely I manged to get thenumber format property, but can you tell me how do i just get the Horizontal and Vertical Alignment to pop the Answer RIGHT MIDDLE LEFT as it gave me the answer in Minus..

[JosephP]

You'll have to write specific functions for that since the values are not what you want to see. I reckon you're doing this for clients so I'll leave it as a learning exercise for you. ;-)

[e4excel]

Dear Joseph,

If I could really write VBA code on my own, I would have seldom put it on the forum, yours was a wonderful code which actually gave me an option..
THis is not just for Clients but for me to learn to have some unconventional ways of saving ones Code=Formula..

WHat I was expecting was to get all the information so that I could then immediately get a VBA version for the MAcro..

With your help I id manage to get a few important Asthetic Formats but mostly in the crude format..

Like I got the Vertical ALignment = Negative Value..

I am trying to search this on the net too but with very few options..

ANyways thanks a lot..

[e4excel]

Dear FOrum,

With Joseph's help I did manage to get a lot of information from any cell, but I am still held up with Alignment as when I put HorizontalALignment I get negative values...
Is it possible to get the values such as Center,RIGHT,LEFT,JUSTIFIED or DIstributed?

[e4excel]

Bump No Response

[e4excel]

Dear Forum,

Can someone please guide me on the missing links