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

[sindhuja]

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

[L@ja]

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

[sindhuja]

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

[L@ja]

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

[mdmackillop]

Hi L@ja,
You have commented evey line of code. Can you fix this?

[L@ja]

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.

[sindhuja]

hi,

Thanks for the help !

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

-Sindhuja

[L@ja]

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

[sindhuja]

Hi L@ja,

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

-sindhuja

[Simon Lloyd]

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.

[mdmackillop]

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.

[sindhuja]

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

[mdmackillop]

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]

[sindhuja]

Hi MD,

tried out, but showing me the compile error..

"cannot find object or library.."

Is that any refernce i need to add...

-Sindhuja

[mdmackillop]

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.

[sindhuja]

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

[mdmackillop]

Is networkdays working as a function on your spreadsheet?

[sindhuja]

Yes MD, If I worked out on separate sheet to find out the network days it works fine…

[mdmackillop]

I test it tomorrow on another PC

[sindhuja]

Hi,

Please help me out in this...

-Sindhuja