1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Creating an Excel macro to select text content of a cell and copy it into ano

Discussion in 'Business Applications' started by JeffSchwartz, Feb 5, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I am trying to create a macro that will select the text contents of a cell andcopy it into another cell. I know recording a macro isn't the best way to go,but I'm still pretty fresh in coding VBA.

    When I record a macro on the following set of data (shown below) I cancreate a row above the first row containing "CASH" and copy the word"CASH" into the cell above it.

    (sample data)

    CASH 83 521.01
    TRES.CARD 11 54.11
    CREDIT2 0 0
    HELP 0 0
    SUBTOTAL 0 0



    (recorded macro)

    ActiveCell.Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "CASH"
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveSheet.Paste

    If I run the macro on the row containing "Tres.Card" it willreplace “TRES.CARD” with "CASH".

    Essentially I am trying the make two rows for each of the existing rows. Iwould add "#" to the label in column 1 on the first line and add"$" to that label on the second line. The first line would thencontain the data from column B and the second line would contain the data fromcolumn C. So that the end result would look like:

    (Example of end result)

    CASH # 83
    CASH $ 521.01
    TRES.CARD # 11
    TRES.CARD $ 54.11
    CREDIT2 # 0
    CREDIT2 $ 0
    HELP # 0
    HELP $ 0
    SUBTOTAL # 0
    SUBTOTAL $ 0

    If anyone has any suggestions of the best way to do this that I'm not thinkingabout or aware of, I would appreciate it.

    Otherwise, does anyone have any suggestions of how to code the macro toselect and copy the text contents of the cell rather than having it use thetext of the when the macro is recorded?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Hi Jeff, seeing your different posts you're quiet busy with something.
    Let's see if I understand you correctly.

    You have a dataset
    CASH
    TRES.CARD
    CREDIT2
    HELP
    SUBTOTAL

    I omitted the colums.

    So what you're looking for add a NEW row above every cell and fill that cell with the contents of the cell in the row directly below?

    Now to get back to the macro bit:
    A macro is nothing more than a collection of instructions that are always carried out one after the other and unless no condition is set (IF ... THEN .... ELSE .... OTHERWISE .... IF AGAIN ... etc. etc.) it just does exactly that.
    When you record a macro it does exactly the same thing, expect that the commands needed are furnished by the VBA processor and when you stop the recording and look at what you have it might come accross like some unknown language or gibberish.

    Good, now you say that everything is replaced with "CASH", of course that is what you selected, or at least the data from that cell is used and reused, the macro doesn't know any better (poor thing)

    Most of us here (and I am certainly one of those) 'created' our own first macro by simply recording it, and then try to decypher it and start playing around. Every macro book suggestes that too as a first lesson and step into VBA coding.
    Every macro book and application starts with the same thing, a simple macro that says 'Hello world!", well welcome to the macro and programming world.

    One thing you really need is time and a lot of patience, if you have a partner, then I hope it's a patient one that finds out that he / she lost you VBA :)

    The problem with recorded macros is that you get references like R1C1 R-1C+2 etc.
    Try to visualize that Execl is all about rows an columns, nothing more and nothing less.

    I did not take into account if you have a header row assuming no empty rows in between:

    The macro process everything from bottom up becasue that way you avoid processing the same row again.

    Code:
    Sub DuplCells()
    Dim xRow As Long
    For xRow = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Len(Trim(Range("A" & xRow).Value)) = 0 Then Exit For
        Range("A" & xRow).Select
        Selection.EntireRow.Insert
        Selection.Value = Selection.Offset(1, 0).Value & " #"
        Selection.Offset(1, 0).Value = Selection.Offset(1, 0).Value & " $"
    Next xRow
    End Sub
    
    Paste this in the dsired sheet's VBA project or iif you need to use if in more sheets in the general VBA project

    Happy coding:)
     
  3. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Thank you!

    That coding does half of what I need to do. I will spend some time in my VBA AND MACROS book trying to figure out why that coding does what it does.

    I other thing I need to do is get the data that is in column B to line up with the descriptors in column A that end in "#." And get the column C to line up, in column B, with the descriptors in column A that end in "$."


    [​IMG]


    (I guess I'm also having trouble figuring out how to insert charts into this message box as well !)

    Can you help me figure out how I can get the data to shift between the column in this way. (It might take me awhile to find the information in my book.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    You give the guy a finger and he takeas the whole hand :) LOL

    I'll see if I can do it and explain it a little too.
    Remember programming is trial and error.

    You have to imagine what you want it to do and then transalte it to instructions.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Why didn't you put the code in the file you attached?
    Okay, I copied it from the site now but what you offer is just an empty sheet with what you have and what you want.

    You could at least have included the VBA code.

    I'm off to bed now here, tomorrow I'll check and look.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    I couldn't go to bed without finishing it

    run the macro that's in Sheet 1 for the tabel in sheet 1.

    sweet dreams :)
     

    Attached Files:

  7. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Thank you Hans,

    You have been extremely helpful and extremely generous! I look forward to studying my book to see why your coding does what it does. In the mean time, my employer can be happy that I have (with your help) accomplish the task at hand.

    I am sure I will be submitting more questions on this forum. Feel free to let someone else help me with my ignorance if you need a break.

    Again, THANK YOU!!
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    No problem, that's why we're here, pass some of acquired knowledge to others.

    You should Google for sites that have samples and explanations,
    I don't have the patience for books.

    Hands-on and trial-and-error.

    That's my moto
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088361

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice