OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

please help me make this excel macro work in openoffice calc
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sat May 12, 2012 5:11 am    Post subject: please help me make this excel macro work in openoffice calc Reply with quote

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
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sat May 12, 2012 5:15 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sat May 12, 2012 6:26 am    Post subject: Reply with quote

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

Karo
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sat May 12, 2012 6:25 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sat May 12, 2012 10:04 pm    Post subject: Reply with quote

Hi

Again:
You need no Script !

Do the Task with simple Cellformulas and Conditional-Formating

Karo
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sat May 12, 2012 11:50 pm    Post subject: Reply with quote

pls tell me how. it would be really helpful in my work..
_________________
arun M
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sat May 12, 2012 11:51 pm    Post subject: Reply with quote

because i dont know how to make it run in a loop with simple cell formulas.pls help me out
_________________
arun M
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sun May 13, 2012 12:12 am    Post subject: Reply with quote

Hi
We need a Examplefile.ods

Karo
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Sun May 13, 2012 4:40 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed May 16, 2012 6:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Wed May 16, 2012 6:42 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed May 16, 2012 11:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Thu May 17, 2012 1:20 am    Post subject: Reply with quote

Hi
And the Formula is:

Code:
=EDATE( C2 ; ( INT( MONTH( C2 ; TODAY() ; 0 ) / B2 )+1) * B2 )


Karo
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Thu May 17, 2012 8:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
arunm86
General User
General User


Joined: 08 May 2012
Posts: 10

PostPosted: Thu May 17, 2012 8:51 am    Post subject: Reply with quote

karo why doesnt the formula work for me..?? am i making some mistakes?? please correct
_________________
arun M
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group