Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Search Column (VBA - Excel 2003)

    Hi,

    Im having trouble putting a macro together that searchs a column , from a csv file, for two particular codes 'POSO' and 'POSO R'. If these are found then I need to cut the contents of those rows to a new workbook.

    The end plan is that when the user opens the csv, they press a button to remove and save elsewhere the contents of any rows with the particular codes.

    Any pointers will be grately appreciated.

    Many Thanks.
    Lee

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

    Re: Search Column (VBA - Excel 2003)

    Try the following macro.

    Sub CutCodes()
    Dim wbkCur As Workbook
    Dim wbkNew As Workbook
    Dim wshCur As Worksheet
    Dim wshNew As Worksheet
    Dim lngCurRow As Long
    Dim lngMaxRow As Long
    Dim lngNewRow As Long
    Dim strVal As String

    Set wbkCur = ActiveWorkbook
    Set wshCur = wbkCur.Worksheets(1)
    Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshNew = wbkNew.Worksheets(1)

    lngMaxRow = wshCur.Range("C65536").End(xlUp).Row
    For lngCurRow = lngMaxRow To 1 Step -1
    strVal = UCase(wshCur.Range("C" & lngCurRow))
    If strVal = "POSO" Or strVal = "POSO R" Then
    lngNewRow = lngNewRow + 1
    wshCur.Rows(lngCurRow).Cut _
    Destination:=wshNew.Rows(lngNewRow)
    End If
    Next lngCurRow
    End Sub

    The macro assumes that the csv file has already been opened.
    You could add code at the beginning that lets the user select a .csv file to open, and at the end to save the .csv file and/or the new workbook, if desired.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Search Column (VBA - Excel 2003)

    Hi Hans,

    Thanks for the response, unfortunately I cant seemed to get your code to work as it should. It opens up a new worksheet but doesnt paste anything in it, it also doesnt appear to cut the relevant line from the worksheet its searching on.

    If possible are you able to say where I might be going wrong?

    Cheers.
    Lee

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

    Re: Search Column (VBA - Excel 2003)

    The code I posted looks for cells whose content is exactly "POSO" or "POSO R" (without the quotes). It will not catch a value such as "POSO LOCO" or "POSO " or " POSO" (note the trailing/leading space in the last two examples)

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Search Column (VBA - Excel 2003)

    Great !!

    Thanks Hans, that works very well.....It seems there was a space after POSO, which is why it didnt work first time round.

    Thanks again.
    Lee

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Search Column (VBA - Excel 2003)

    Hi Hans,

    Sorry to bother you again on this one, but I need to transfer the headings along row one. Ive tried to amended the script you provided earlier by picking up a heading column name as something that is being searched for in addition to the 'POSO' and 'POSO R'. This does work, but puts the headings underneath the data, i.e. the heading could be on row two and the data on row one, at the destination worksheet.

    Is there a way to switch it so the headings are transferred to row one and the data underneath?

    Regards.
    Lee

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

    Re: Search Column (VBA - Excel 2003)

    Changes indicated in bold

    Sub CutCodes()
    Dim wbkCur As Workbook
    Dim wbkNew As Workbook
    Dim wshCur As Worksheet
    Dim wshNew As Worksheet
    Dim lngCurRow As Long
    Dim lngMaxRow As Long
    Dim lngNewRow As Long
    Dim strVal As String

    Set wbkCur = ActiveWorkbook
    Set wshCur = wbkCur.Worksheets(1)
    Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
    Set wshNew = wbkNew.Worksheets(1)

    wshCur.Rows(1).Copy Destination:=wshNew.Rows(1)
    lngNewRow = 1

    lngMaxRow = wshCur.Range("C65536").End(xlUp).Row
    ' Loop down to 2 instead of 1
    For lngCurRow = lngMaxRow To 2 Step -1
    strVal = UCase(wshCur.Range("C" & lngCurRow))
    If strVal = "POSO" Or strVal = "POSO R" Then
    lngNewRow = lngNewRow + 1
    wshCur.Rows(lngCurRow).Cut _
    Destination:=wshNew.Rows(lngNewRow)
    End If
    Next lngCurRow
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Search Column (VBA - Excel 2003)

    That's great, many thanks for your time and help Hans.

    Best Regards.
    Lee

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Column (VBA - Excel 2003)

    Hello Hans
    <hr>Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)<hr>
    Can you help me understand the naming convention used in VBA?

    xlWBATWorksheet
    <UL><LI>xl: part of the Excel library
    <LI> WB : Workbook
    <LI> AT : ???
    <LI> Worksheet : Worksheet[/list]T.I.A.
    Regards
    Don

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

    Re: Search Column (VBA - Excel 2003)

    xlWBATWorksheet is a built-in constant, if you look up the help for Workbooks.Add you'll see which other constants are available.
    You can't make up your own values - if you'd try xlWBATPivot, for example, you'd get an error message.
    The T stands for Template, as you can see if you search for xlWBATWorksheet in the Object Browser (press F2 in the Visual Basic Editor to activate the Object Browser). What the A stands for is not made explicit, but my guess would be that WBAT = WorkBooks Add Template.

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Column (VBA - Excel 2003)

    Thank you Hans
    Regards
    Don

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Search Column (VBA - Excel 2003)

    The object browser (press F2 in the VBE) sometimes helps clarify which constants are part of which family and go with which function. In some cases, though, it can run you around in circles without answering your questions, so the help files (F1) are definitely still necessary.

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Column (VBA - Excel 2003)

    Hi Jefferson
    <hr>so the help files (F1) are definitely still necessary.<hr>
    They most certainly are. I frequ8ently slap my wrists, for using the lounge when a little more patience with the help files would have clarified the situation for me.

    However there are many times when the same thought phrased differently penetrates this old noggin in a much more lucid manner I consider the Lounge one tremendous asset.

    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    Thanks again.
    Regards
    Don

Posting Permissions

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