+ Reply to Thread
Results 1 to 7 of 7

if cell in column B has data autofill cell in column A with a fixed text

  1. #1
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    if cell in column B has data autofill cell in column A with a fixed text

    How do I achieve this?
    I am a newbie trying to work things out learning as I go and not sure if this can be achieved with simple function or requires a macro.

    I am given a list of data for which the number of rows can vary, this data is pasted into column B.
    Column A has a fixed text which is repeated for each cell of column B that contains data.
    Column C has a fixed text which is repeated for each cell of column B that contains data.
    Column D is the final text consisting of A+B+C and should also autofill or complete.

    I used
    Please Login or Register  to view this content.
    for column D but still have to manually double click for autofill the column

    I tried
    Please Login or Register  to view this content.
    for column A but still have to manually double click for autofill the column

    I tried
    Please Login or Register  to view this content.
    for column C but still have to manually double click for autofill the column

    I would like to be able to paste into Column B have Columns A and C automatically fill with the same fixed text and column D to automatically fill with A+B+C

    So I just paste into Column B and then Go and Copy Column D

    As this is using an active event does this require a macro with a
    for i statement
    with an if not null statement
    or maybe a while statement

    As you can see a newbie

    I have been looking at tigeravatar's post here which seems to point somewhere in the right direction for me
    http://www.excelforum.com/showthread...=1#post2659503
    Last edited by wolfen; 02-23-2012 at 11:22 PM. Reason: additional information

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: if cell in column B has data autofill cell in column A with a fixed text

    Put this macro into the SHEET module where you're going to paste into column B:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    Thumbs up Re: if cell in column B has data autofill cell in column A with a fixed text

    Quote Originally Posted by JBeaucaire View Post
    Put this macro into the SHEET module where you're going to paste into column B:

    Please Login or Register  to view this content.
    First I need to thank you for taking time to reply.
    I will need to get some sleep as I am so tired I cannot think straight which does not help when you are trying to do something as a complete newbie.
    If it were php coding I would be done by now.
    I need to learn some excel first, maybe just lack of sleep as not even sure about this bit.
    Put this macro into the SHEET module where you're going to paste into column B:
    I will be able to check and test it once I have had sleep.
    I think I can follow what your code is doing.

    I will check to see if there are any further reply posts after sleep and will look back at your reply above with a clear thinking head.
    Last edited by wolfen; 02-23-2012 at 07:57 PM.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: if cell in column B has data autofill cell in column A with a fixed text

    Let me know how your testing goes. The macro occurs in realtime, if you clear column B the rest of the cells clear. If you paste values into column B the rest of the cells around it will fill in.

  5. #5
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    Re: if cell in column B has data autofill cell in column A with a fixed text

    Thank you JBeaucaire

    Ok had a snooze and restarted my machine as well as my brain and yes the code works fine I have changed it slightly for other sheets.

    I am now thinking that this could be simplified further as I do not really need the data posted to Column A and C but just want to input Column B and output Column D but with the additional text that would have been in columns A and C thus B becomes A and D becomes B.

    So using your code from above I have
    Please Login or Register  to view this content.
    I am still learning so how would I construct this part
    Please Login or Register  to view this content.
    so that it has the text
    Please Login or Register  to view this content.
    + the RC1 + the
    Please Login or Register  to view this content.
    Is the code
    Please Login or Register  to view this content.
    basically saying make the output of next column equal due to the Offset(row,column) column being 1 to the right
    Please Login or Register  to view this content.
    starting from row 1 cell 1 which in this case would be starting from B1
    Last edited by wolfen; 02-23-2012 at 08:25 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: if cell in column B has data autofill cell in column A with a fixed text

    I was going to do all that, then thought it was easier to just replicate you original process.

    If you want to see the correct syntax, turn on the macro recorder and enter the formula you want into B1, then turn off the recorder.

    ="/sbin/iptables -A INPUT -p udp -s "" & A1 & " + " -j DROP"


    That will show the syntax, adapt that. I bet you can figure it out from there.
    Last edited by JBeaucaire; 02-24-2012 at 01:14 AM.

  7. #7
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    Re: if cell in column B has data autofill cell in column A with a fixed text

    Thanks again I now have it solved.
    Your pointers give a clear and concise heads up I have now learned a good bit in a very short time.
    Keep up the good work.
    Last edited by wolfen; 02-24-2012 at 10:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1