Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 41

Thread: Find for a specific word and extract details based on the found value

  1. #1
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location

    Exclamation Find for a specific word and extract details based on the found value

    Hi All,

    Here is my requirement..!

    I generally download a .txt file (large file) from where i need specific informations.

    I find for a specific word and in the next tab there willl be count. If count is 0 we need not do anything.

    If it is >=1 then we need to check for the payment date and if date is =< 8 networdays days then the fund , account, trade details needs to be copied in a seperate work sheet.

    Am not sure whether this can be done using macros.
    if possible do we use excel or txt file itself as i am new to VBA.

    Please help me out in this and this is really a large data and it takes long time to finish off the task....

    Any suggessions will be highly appreciated !

    - Sindhuja

  2. #2
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    Hi,
    pls attach a txt+xls file, what is the input,and accepted output.
    I think it is solvable by excel vba, but I don't see exactly what you have...

    bye
    L@ja

  3. #3
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Thanks for the heads up !

    I have attached the sample txt file and the excel file for the workings.
    Let me again brief my requirements.

    1) search for the word "PAID & WAIT TOTAL". If the tab next to this is 0 need not do anything.. if >=1 then we need need to check for the payment date.

    2) If date of payment date is <= 8 networkdays from the date report was generated then we need to get details like fund #, account #, order #.

    3) Similary all data needs to be copied. ex if it 2, we need to check for the 2 payment dates.

    File contains large volmes and we need to check the whole...

    Any quick response will be highly appreciated.

    - Sindhuja

  4. #4
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    Hi,
    I think this is the vector
    try to make it

    [VBA]Sub convertxt()
    'select preformatted *txt and insert into sheet raw
    'you can record it like macrorecorder and correct it manually
    End Sub
    Sub analize()
    'define REPORT DATE
    'find last rawsheet row (lastrow)
    'find where we will write row(actwriterow)
    'cycle start from actrow=2 to lastrow
    'if found "PAID & WAIT TOTAL" then check the next cell
    'if biggest than 0 then
    'select the cells(actrow-2,PAYMT_date_col)
    'check date diff
    'if needed, then read more details like before select
    'write datas like
    'sheets("Paid & Wait").cells(actwriterow,1)=fnd
    'sheets("Paid & Wait").cells(actwriterow,2)=typ
    'sheets("Paid & Wait").cells(actwriterow,3)=paydate
    '...etc
    'increase actwriterow
    'actwriterow = actwriterow + 1
    'end if
    'cycle End from actrow (2 to lastrow)
    'msgbox "analize done"
    End Sub[/VBA]

    if you need more help, yust write
    L@ja

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi L@ja,
    You have commented evey line of code. Can you fix this?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    Hi,
    I just would like to help with the logic.
    (first time)
    after if it's not enought i will write some working code.

    My idea to solve problems to write exactly the problems.
    if it's done probably the problem solved (80%)
    if Sindhuja can't continue solve the problem than I'll help too.

    sorry if i confuse anybody.
    L@ja

  7. #7
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    hi,

    Thanks for the help !

    To be frank i am not good in coding ! but am trying...
    you help will be highly appreciated...

    -Sindhuja

  8. #8
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    Hi,
    some code attached,
    but it is still not enought :-)

    [VBA]
    Sub analize()
    'define REPORT DATE
    repdate=format(now,"dd.MM.yyyy")
    'find last rawsheet row (lastrow)
    lastrow=sheets("raw").cells(65500,1).end(xlup).row
    'find where we will write row(actwriterow)
    actwriterow=sheets("Paid & Wait").cells(5,1).end(xldown).row
    if actwriterow>65500 then actwriterow=6
    'cycle start from actrow=2 to lastrow
    with sheets("raw")
    for actrow=2 to lastrow
    'if found "PAID & WAIT TOTAL" then check the next cell
    if .cells(actrow,2)="PAID & WAIT TOTAL" and .cells(actrow,3)>0 then
    'select the cells(actrow-2,PAYMT_date_col)
    xdate=.cells(actrow-2,1)
    if (repdate-xdate)>8 then
    'check date diff
    'if needed, then read more details like before select
    fnd=.cells(actrow-1111(?),1) 'u have to found it
    'because you have a offset points
    '...etc
    'write datas like
    'sheets("Paid & Wait").cells(actwriterow,1)=fnd
    'sheets("Paid & Wait").cells(actwriterow,2)=typ
    'sheets("Paid & Wait").cells(actwriterow,3)=paydate
    '...etc
    'increase actwriterow
    actwriterow = actwriterow + 1
    end if
    end if
    'cycle End from actrow (2 to lastrow)
    next actrow
    end with
    'msgbox "analize done"
    End Sub [/VBA]

    I'll come back later
    bye
    L@ja

  9. #9
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi L@ja,

    Tried out the coding... its not giving me the expected results...

    -sindhuja

  10. #10
    Site Admin VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,008
    Location
    sindhuja, l@ja has been trying to help you help yourself rather than just give you a complete solution, in his attempt to help you learn what is happening he has provided code with some lines commented out these lines if uncommented (by removing the ') will not necessarily work but gives you a push in the right direction as to what you are looking to achieve, his first post showed you the thought process for trying to reach your goal.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi,
    I've used Data Import to put the data into Sheet1.
    Can you please highlight, add comments, fields to be checked etc. on the first 3 items you want to be imported; and also show items which should be ignored, and why.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  12. #12
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location

    Red face

    Hi,

    Thanks for the help !

    Attached the file with the comments and the datas to be filled in the Paid & Wait sheet highlighted.

    Also have hidden the rows from 6-25 to show the corresponding headings for easy reference.

    Comments given in the corresponding fields which date to be taken and which not to...

    Hope this will be clear !
    Am new to VBA started learning now only...

    Thanks for the help in advance !!!

    -Sindhuja

  13. #13
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Try this
    [vba]
    Option Explicit
    Option Compare Text

    Sub Populate()
    Dim rFund As Range, PayDate As Range
    Dim Fund As Long
    Dim rTot As Range
    Dim FirstAddress As String
    With Sheets("Sheet1").Columns(1)
    'Find first Paid & Wait (P&W)
    Set rTot = .Find(What:="PAID & WAIT TOTAL", _
    LookIn:=xlValues, lookat:=xlPart)
    FirstAddress = rTot.Address
    Do
    'If no P&W value then find next
    If Not rTot Is Nothing And rTot.Offset(, 1) < 1 Then
    Do
    Set rTot = .FindNext(rTot)
    Loop Until Not rTot.Offset(, 1) < 1
    'With P&W value, find Fund value
    Set rFund = .Find(What:="FUND #:", LookIn:=xlValues, _
    lookat:=xlPart, After:=rTot)
    Fund = Mid(rFund, 9, 4)
    'Check PayDate and infill data
    Set PayDate = rTot.End(xlUp)
    'MsgBox PayDate
    If Application.NetworkDays(PayDate, Date) <= 8 Then
    Call GetData(rTot, PayDate, Fund)
    End If
    End If
    'Find new P&W value
    Set rTot = .Find(What:="PAID & WAIT TOTAL", _
    LookIn:=xlValues, lookat:=xlPart, After:=rTot)
    Loop While Not rTot Is Nothing And rTot.Address <> FirstAddress
    End With
    End Sub

    Sub GetData(rTot As Range, PayDate As Range, Fund As Long)
    Dim tgt As Range
    Set tgt = Sheets("Paid & Wait").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 10)
    tgt(1) = Fund
    tgt(2) = Split(rTot)(0)
    tgt(3) = PayDate
    tgt(4) = Split(PayDate.Offset(-1))(2)
    tgt(5) = Split(PayDate.Offset(-1))(3)
    tgt(6) = PayDate.Offset(-1, 1)
    tgt(7) = PayDate.Offset(-1, 2)
    tgt(8) = PayDate.Offset(-1, 5)
    tgt(9) = PayDate.Offset(-1, 6)
    End Sub

    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  14. #14
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi MD,

    tried out, but showing me the compile error..

    "cannot find object or library.."

    Is that any refernce i need to add...

    -Sindhuja

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    1. First check that you have no references marked "Missing"; if so, remove the check next to these items

    2. You may require the Analysis Toolpack add-in installed to use NetWorkDays.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi,

    Run time error "438"
    Object doesn't support this property or method

    Again showing error on below line..
    If Application.NetworkDays(PayDate, Date) <= 8 Then

    help me out !!

    -Sindhuja

  17. #17
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Is networkdays working as a function on your spreadsheet?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  18. #18
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Yes MD, If I worked out on separate sheet to find out the network days it works fine…

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    I test it tomorrow on another PC
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  20. #20
    VBAX Mentor
    Joined
    Sep 2007
    Posts
    405
    Location
    Hi,

    Please help me out in this...

    -Sindhuja

Posting Permissions

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