please help me make this excel macro work in openoffice calc

arunm86 · General User Joined: 08 May 2012 Posts: 10

Sub SRPPORT()
prtSRP = ActiveWorkbook.Name
MsgBox "Select Account Details"
FName = Application.GetOpenFilename("Excel Files (*.*),*.*")
If FName <> False Then
Workbooks.Open FName
fname1 = ActiveWorkbook.Name
row_pcr = Workbooks(fname1).Sheets("Account").Range("a65536").End(xlUp).Row

For i = 2 To row_pcr
k = Workbooks(fname1).Sheets("Account").Cells(i, 3)
m = 0
For j = k To Date
If (m = 0) Then
m = DateAdd("m", Workbooks(fname1).Sheets("Account").Cells(i, 2), Workbooks(fname1).Sheets("Account").Cells(i, 3))
Else
m = DateAdd("m", Workbooks(fname1).Sheets("Account").Cells(i, 2), m)
End If
If (m > Date) Then
Workbooks(fname1).Sheets("Account").Cells(i, 4) = Format(m, "DD-MMM-YY")
Exit For
ElseIf (m = Date) Then
Workbooks(fname1).Sheets("Account").Cells(i, 4) = Format(m, "DD-MMM-YY")
Workbooks(fname1).Sheets("Account").Cells(i, 1).Interior.Color = vbGreen
Exit For
End If
Next
Next

End If
End Sub

The functionality is this code asks to open an excel sheet which already has a date value. the months of the value should increment according to the value provided in another column. and if the resultant date is today, it should highlight. please help me. i dont know macros in openoffice
_________________
arun M

arunm86 · General User Joined: 08 May 2012 Posts: 10

the input excel sheet has 2 columns, date and reset period. if date is 3 november 2011 and the reset period is 3, then the resultant date should be 3 February 2012. only the month and year should add on and not the day. if the resultant date is today, then the entire row should highlight.

this will help me in work a lot. please help me with this in openoffice calc as i cannot use MS Excel in my office.
_________________
arun M

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

Hi
All that stuff could be done by simple Formulas, and a conditional Format.

Karo

arunm86 · General User Joined: 08 May 2012 Posts: 10

the condition is the resultant date should not be less than today. the adding should go on a loop and stop only when we arrive at a future date.
example if date is 3 november 2011 and the reset period is 3, then the resultant date should be 3 august 2012.
i thought a script would do it...
_________________
arun M

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

Hi

Again:
You need no Script !

Do the Task with simple Cellformulas and Conditional-Formating

Karo

arunm86 · General User Joined: 08 May 2012 Posts: 10

pls tell me how. it would be really helpful in my work..
_________________
arun M

arunm86 · General User Joined: 08 May 2012 Posts: 10

because i dont know how to make it run in a loop with simple cell formulas.pls help me out
_________________
arun M

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

Hi
We need a Examplefile.ods

Karo

arunm86 · General User Joined: 08 May 2012 Posts: 10

Account No________Reset Period_______A/C Opening Date________Next reset date
100701508111__________3________________5-May-11
100701508112 __________6________________2-Mar-11
100701508113 __________3________________3-Nov-11
100701508114 __________9________________3-Nov-07

This is the sheet. The reset period has to be added with the A/C Opening Date. i ll explain the first row here.
reset period is 3 and the A/C Opening date is 5 may 2011. for this row the next reset day column should have the date 3 August 2012. i.e the date should be a future date. to get this future date, i have to add up 5 may 2011 with 3 i,e the reset period for that particular row. i thought i need to run a loop to get the next reset date which should be a future date. and if the next reset date is today, then the row should highlight which would tell me which all accounts are to be reset today.
_________________
arun M

ken johnson · Posted: Wed May 16, 2012 6:26 pm Post subject:

Question regarding Reset Period 3;6;3;9 in second column...
What do these numbers represent?
A set period perhaps?
I can't see how 5 may 2011 with rest period of 3 becomes 3 August 2012 for the next reset date.
My guess is the final formula will involve the MOD function, however, until more info about the reset period is supplied that is all I can say.

Ken Johnson

arunm86 · General User Joined: 08 May 2012 Posts: 10

the reset period gets added to the date until the date becomes an immediate future date.
5 may 2011 + 3 = 5 june 2011. here 5 june 2011 is not a future date, so add 3 again to 5 june 2011 which ll give you 5 september 2011. this shoud go on until i get 5 august 2012 which is the immediate reset date. the previous date woud be 5 may 2012 which is aready gone.
_________________
arun M

ken johnson · Posted: Wed May 16, 2012 11:57 pm Post subject:

You seem to be saying that the 3 being added to 5 May 2011 is a certain period of time.
Now if 5 May 2011 + 3 = 5 June 2011, then 3 = 5 June 2011 - 5 May 2011 = 31 days.
Also, if 5 June 2011 + 3 = 5 Sept 2011, then 3 = 5 Sept 2011 - 5 June 2011 = 92 days.

How can adding 3 be equivalent to adding 31 days for one calculation and 92 days for another?
There must be something wrong with your explanation.

Ken Johnson

karolus · OOo Advocate Joined: 22 Jun 2011 Posts: 208

Hi
And the Formula is:

arunm86 · General User Joined: 08 May 2012 Posts: 10

hey Ken, sorry with that explanation..

5 may 2011 + 3 = 5 august 2011.
5 august 2011 + 3 = 5 November 2011 and so on...

so finally the resultant date should be a future date and that should be 5 August 2012.
the 3 here is a constant. the day remains the same and the month and year alone have to increment.
_________________
arun M

arunm86 · General User Joined: 08 May 2012 Posts: 10

karo why doesnt the formula work for me..?? am i making some mistakes?? please correct
_________________
arun M