Quickly Transpose

[robfo0]

Hi everyone....

Could someone help me with a macro that would do the following:

I have code with formulas that look like the following:
Sheets("Application").[AMT_FINAN] = Sheets("DataSheet").[AMT_FINAN].Value

There are about 200 formulas in my code. What I need is to transpose the formula to look like this:
Sheets("DataSheet").[AMT_FINAN].Value = Sheets("Application").[AMT_FINAN]
on all 200!

The formulas all vary in length. Maybe i could copy all the formulas to a text file and do it from there? also, how would i do it?

Thanks
This message was edited by robfo0 on 2002-04-08 16:46

 

[Russell Hauf]

On 2002-04-08 16:45, robfo0 wrote:
Hi everyone....

Could someone help me with a macro that would do the following:

I have code with formulas that look like the following:
Sheets("Application").[AMT_FINAN] = Sheets("DataSheet").[AMT_FINAN].Value

There are about 200 formulas in my code. What I need is to transpose the formula to look like this:
Sheets("DataSheet").[AMT_FINAN].Value = Sheets("Application").[AMT_FINAN]
on all 200!

The formulas all vary in length. Maybe i could copy all the formulas to a text file and do it from there? also, how would i do it?

Thanks
This message was edited by robfo0 on 2002-04-08 16:46

Not sure exactly what you're asking, but if you want to copy the formulas from one sheet to another, you can use the .Formula property instead of .Value

Hope this helps,

Russell

 

[robfo0]

No, I'm trying to transpose the 2 values on each side of the "=". If you notice in my example, they are switched. I have code with about 200 formulas, and i need to do the transposing for each. I could copy the end of each formula then paste it to the front, but doing this 200+ times would be a little insane

 

[Russell Hauf]

Ok, I think I understand now. Can you post part of your code that needs to be changed? Please post some of the code above and below (even though you don't want it changed). I don't need all 200 formulas, just a few. I think that I (or someone else) will definitely be able to help you if you still need it.

-rh

 

[Corni]

In this sort of situations I use Edit/Replace tool. It works handy whenever you have to replace a high volume of code patterns. For ex. in this situation after highlighting the section of code with all this formulas, proceed in two stages using Replace:
1)Find What: "Application"
Replace With: "DataSheet"
Click Replace All
2)Find What: Sheets("DataSheet").[AMT_FINAN].Value
Replace With: Sheets("Application").[AMT_FINAN]
If the code block you have to select to include all formulas contains for ex. "Application" text outside your formulas, then this is not applicable. As a workaround solution, copy the code in an Excel sheet (not Word) and then select non-contiguous code blocks including only your formulas and apply Replace tool. You cannot use Word because there is no functionality in Word to select non-contiguous text (strange, isn't it).