Quickly Transpose

robfo0 · Apr 8th, 2002, 05:45 PM

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 · Apr 8th, 2002, 06:03 PM

Quote:

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 · Apr 8th, 2002, 06:58 PM

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 · Apr 10th, 2002, 02:24 PM

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 · Apr 29th, 2002, 06:29 PM

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).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 05:45 PM	#1
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	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 ]

Apr 8th, 2002, 06:58 PM	#3
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	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

Apr 10th, 2002, 02:24 PM	#4
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	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

Apr 29th, 2002, 06:29 PM	#5
Corni Board Regular Join Date: Mar 2002 Location: Brampton Posts: 324	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).