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.

Macro

Discussion in 'Business Applications' started by msmigraine, Feb 10, 2003.

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

    msmigraine Thread Starter

    Joined:
    Feb 10, 2003
    Messages:
    3
    :confused:
    I have a file (more than happy to forward upon request) which contains multitudes of cells containing bar codes - I need a Macro (I'm assuming) which will enable me to add an asterk (*) to the beginning and to the end of each bar code within each cell - I'm presently hairless after several failed attempts - very new to Excel - can anyone help me?
     
  2. smOz

    smOz

    Joined:
    Apr 4, 2002
    Messages:
    11
    Hi,

    Open Sheet1 object code module in VBE and copy&paste this code. It will work for A1 cell in Sheet1 so please try writting a value in sheet1 A1 cell. Then you can modify it for any cell you need.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" And Left(Target, 1) <> "*" And Right(Target, 1) <> "*" Then
            Target = "*" & Target & "*"
        End If
    End Sub
    
    I hope this helps.

    Regards
     
  3. msmigraine

    msmigraine Thread Starter

    Joined:
    Feb 10, 2003
    Messages:
    3
    :confused: How do I go about applying the same to a button which will allow me to select multiple cells and then press the button to apply the "asterisk" formatting"? How to make it generic so to speak? Please - and thank you for your assistance!!! My primary problem here is that I have about 1100 cells to modify.
     
  4. smOz

    smOz

    Joined:
    Apr 4, 2002
    Messages:
    11
    Hi,

    Paste this code into a module and then select all those cells on worksheet (you can use multiply selection) then run this macro by Alt+F8.

    Code:
    Sub ApplyAll()
    Dim mycell
        For Each mycell In Selection.Cells
            If Not mycell.Value = "" Then
                mycell.Value = IIf(Left(mycell.Value, 1) = "*", "", "*") & mycell.Value & IIf(Right(mycell.Value, 1) = "*", "", "*")
            End If
        Next mycell
    End Sub
    
    It will skip blank cells.

    I hope this helps.
     
  5. msmigraine

    msmigraine Thread Starter

    Joined:
    Feb 10, 2003
    Messages:
    3
    Thank you - thank you - thank you! It works great, assigned it to a button and boom instant updates to my data - I envy your abilities.:p
     
  6. smOz

    smOz

    Joined:
    Apr 4, 2002
    Messages:
    11
    Nice to hear that it worked for you. :)

    Regards.
     
  7. 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/118083

  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