Quickly Transpose
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Quickly Transpose

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com