Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Insert and Column delete (Office XP )

    Hi Hans and all,

    Refer to my earlier post which state that :

    Can we write code for the auto insert of empty cells with reference to another cell? ie if some of column B's cells are blank and I would like to input Abbr. or a 2-characters eg. CA into them by looking at the content in the cells of column D , eg California.? an Example in the file would be Belgium in Column D (Country) and Abbr ( column A) would return BE and so on.

    Is there another way to delete column automatically instead of using the recording marco as it is static and does not change if the new worksheet change its content to another column?

    I have attach a sample for your reference. The column to be delete are column B (address) and Delivery date ( column E). It would be delete before insert the Abbr. describe above.

    Your help and suggestions is very much appreciate.

    btw, if I program it in my home PC which installed Office XP, can I use the program in my office which run Excel 2000?

    Thanks , kun

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    I would use a separate table holding country names and abbreviations, and VLOOKUP formulas in column A. In the attached version, the table is in columns A:B on Sheet2. The formula in B2 is

    =VLOOKUP(F2,Sheet2!$A:$B,2,FALSE)

    and this is filled down as far as needed.

    BTW: wouldn't it be easier to enter the abbreviation and let the formula fill in the complete name?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    To delete a column with a specific heading, you can use this procedure:

    Sub DeleteColumn(ColumnHeading As String)
    Dim rng As Range
    Set rng = Range("1:1").Find(What:=ColumnHeading, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.EntireColumn.Delete
    End If
    End Sub

    To delete the column with heading "Address", regardless of whether this is column B or column J or column DE, use DeleteColumn "Address".

    The following macro will delete the columns with heading "Address" and "Delivery Date".

    Sub DeleteSomeColumns()
    DeleteColumn "Address"
    DeleteColumn "Delivery Date"
    End Sub

    Since this is standard code, it should work on any version of Excel from 97 to 2003, without modification. You have to be careful not to use new language elements introduced in Excel 2002 (XP); you can find which they are in the What's New section of the online help for Excel VBA.

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Hans,
    Thank for replying, I am given the complete name and I want the abbreviation.
    Can't download your file, will look at it tonite.

    thanks, kun

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Thanks alot Hans, it is excellent!

    Can the formula be protected or better invisible, some user might accidentaly delete it. Can you also explain what is the HLOOKUP and DLOOKUP for and when I should be using it..

    Thanks Always, kun

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Insert and Column delete (Office XP )

    Cell are "locked" by default. Goto format - cells -protection (tab) and check "Hidden" to make them hidden.
    The select any cells that you want to be "editable" by the user:
    Goto format - cells -protection (tab) and uncheck "Locked"
    Now Protect the worksheet
    Tools - protection - protect sheet (set a password if desired)

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    You can protect cells and hide formulas, but only as part of a protected worksheet. The procedure is as follows:
    - Select the cells that the user should be able to edit (NOT the cells you want to protect!)
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the check box labeled "Locked".
    - Click OK.
    - Select the cells whose formulas you want to hide.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Tick the check box labeled "Hidden".
    - If necessary, repeat for other blocks of cells.
    - Select Tools | Protection | Protect worksheet...
    - Tick the check boxes for the features that should be available to the user.
    - Specify a password if you like (you can leave it blank if you don't want a password)
    - Click OK.

    You can get information about functions such as VLOOKUP in the online help. In short:

    =VLOOKUP(F2,Sheet2!$A:$B,2,FALSE)

    This formula takes the value in cell F2 on the current sheet, and looks up this value in the first column of the range A:B (columns A and [img]/forums/images/smilies/cool.gif[/img] on Sheet2. If it finds the value, it then takes the value from the same row in the second column (since the 3rd argument is 2) and returns that. the last argument FALSE specifies that you're looking for an exact match, not an approximation.

    HLOOKUP is similar, but it looks for a value in the first row of a range, instead of in the first column, and returns a value from the same column but from the row specified in the 3rd argument.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Insert and Column delete (Office XP )

    Here is a primer on Excel -- Worksheet Functions -- VLookup by MS MVP Debra Dagliesh.

    HLOOKUP is nearly identical but instead of looking through a column to find a row that matches, it looks thru a row until it finds a column to match.

    Steve

  9. #9
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Hans, it's fantastic !!

    I never know code is just a two lines ......I thought it is suppose to be long....

    Is there anyway to find a specific 'text' in a worksheet and insert 1 at its column E?

    Thanks, kun

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    Where do you want to look for the text? In any column?

  11. #11
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Hi Hans,

    It normally would appear in column D or C, the text maybe term or terms and upon finding such word, I would like to insert in it's column which is column F a number 1, so that these entries won't be delete off.

    thank you for your advise,
    kun

  12. #12
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Hi Steve,

    Thank you for the explanation and the info, I would definitely look at it and try learn something from there.

    Appreciate your advice.
    kun

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    Try this macro:

    Sub MarkRows()
    Dim strWord As String
    Dim rng As Range
    Dim strAddress As String
    strWord = InputBox("Enter the term to look for")
    If strWord = "" Then
    Beep
    Exit Sub
    End If
    With Range("C")
    Set rng = .Find(What:=strWord, LookIn:=xlValues, _
    LookAt:=xlPart, MatchCase:=False)
    If Not rng Is Nothing Then
    strAddress = rng.Address
    Do
    Range("F" & rng.Row) = 1
    Set rng = .FindNext(After:=rng)
    Loop While Not rng Is Nothing And Not rng.Address = strAddress
    End If
    End With
    Set rng = Nothing
    End Sub

  14. #14
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Insert and Column delete (Office XP )

    Hans,

    The code given don't seem to work, it just pop up a dialog box. I copied and paste it directly into the module. I also try paste into the commandbuttion1 of UserForm1, ie right click the commandbuttion1 and select view code, I then copied and paste directly into the code window. It showed Compile error : Expected End Sub and the Private Sub CommandButton1_Click() was highlighted.

    Private Sub CommandButton1_Click()
    Sub MarkRows()
    Dim strWord As StringSub MarkRows()
    Dim strWord As String

    Pls advise.

    thanks, kun

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Insert and Column delete (Office XP )

    You cannot have two lines with Sub ... following each other. Each Sub ... must be followed by lines of code, then End Sub before the next Sub ...

    You can call MarkRows from CommandButton1_Click:

    Private Sub CommandButton1_Click()
    Call MarkRows
    End Sub

    The code itself works. I have attached an example (it uses a command button from the Forms toolbar, it runs the MarkRows macro directly. You can right-click the button to view its properties and to see which macro is assigned to it.

Page 1 of 2 12 LastLast

Posting Permissions

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