Weekend Forum

Eugene, Oregon edition. My flight from Salt Lake to Eugene was canceled. Instead I got to go to Seattle and sit in the airport for three hours before taking a puddle jumper to Eugene. They said the cancellation was due to maintenance on the airplane. I don’t know what that means, but I have the feeling it means they didn’t sell enough seats. Maybe I’m just pessimistic. Instead of getting here at 1PM, I got here at 6PM.

I rented a bike from the hotel and checked out the much-touted Eugene trail system. It did not disappoint. I rode 15 or 20 miles which allows me to eat and drink guilt-free all day.

It rains a lot here.

We took a tour of the University of Oregon athletic facilities. We got to walk out on the field which is made of field turf. I don’t think I’ve ever felt field turf before and it’s pretty cool. Every football field has a crown that helps the water drain off. It’s hard to notice from the stands or on TV, but when you’re down on the field, it seems huge.

This week’s Excel question comes from Timothy:

I have a list of contacts that I need to copy over from Word to Excel. Each contact has 16 headers (first name, last name, email, phone etc.) but when I import it into Excel I only get two columns of data that repeats itself. For example column 1 line I is: First Name and column 2 line 1 is: John etc.
until column 1 line 17 that stats all over again with First Name: and then column 2 is: Mike.

How do I shift the column headers from rows to 16 columns going across the top and transfer the data into the relevant cells.

Have a great weekend.

Posted in Uncategorized

8 thoughts on “Weekend Forum

  1. If I read this right
    Assuming that Column 1 has the Field Name (Name, Address etc) extended repeatedly down in blocks of 16 and
    Column 2 has all the Field values for the Field names in Column 1

    I would manually Copy and Transpose the headers from A1:A16 into C1
    and then in C2 I would use an Offset formula like

    =+OFFSET($A$1,((ROW()-2)*16)+(COLUMN()-COLUMN($C$1)),1,)

    and then copy that across to R2 and then copy C2:R2 down until you have collected all your data

    Hui…

  2. I am a big fan of your books and had been using code I discovered in one of your books to remove a macro from an Excel workbook before distribution in earlier version of Excel.

    I recently tried to use this code in Microsoft Office Excel 2003. I get the message “Programmatic access to Visual Basic Porject is not trusted”. Is there another method for removing modules (macro) before distribution or to make them not accessible after a save to another filename.

    I loved this tool. I am assuming that the security changes are limiting use of these methods.

    Sub RemoveModule()
    Dim iLines As Integer
    Application.DisplayFormulaBar = False
    ActiveWorkbook.Save
    iLines = ThisWorkbook.VBProject.VBComponents(“ThisWorkbook”).CodeModule.CountOfLines
    ThisWorkbook.VBProject.VBComponents(“ThisWorkbook”).CodeModule.DeleteLines 1, iLines

    Set VBP = ActiveWorkbook.VBProject
    With VBP.VBComponents
    .Remove VBP.VBComponents(“Module1?)
    .Remove VBP.VBComponents(“Module2?)
    .Remove VBP.VBComponents(“Module3?)
    .Remove VBP.VBComponents(“dateentry”)
    End With
    ‘Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & ThisWorkbook.Name
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.