• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating 12 new dates based on Previous 13 dates

R

ramakriishna

New Member
hi

I am Creating A Monthly Report For That I Have To Create 12 Dates Based On Previous

13 Dates

My Created 12 Dates look Like This 13-mar,13-aPr,------,14-feb

13 is 2013,14 is 2014

If I Am Creating Report For Next Date My Dates Look Like This 13-apr,-----,13-Mar

These 12 dates are 12 columns

My Data Contains Columns Like this

Product Code,13-feb,13-mar,13-apr,13-may,-----,14-jan,14-feb,OTD,VRF

Based on OTD Column Max Value The Dates Should Move Forward And Multiply With Vrf

Ex:Otd Max Value Is 3

Then The Current Month March should move three columns left and multiply with VRF Colum

To Create New March Date

Ex:OTd Is 0 For March Then New March Date=March*Vrf

Otd is 1 For March Then New March Date=Feb*Vrf

Otd is 2 For March Then New March Date=0 Because it should not go beyond feb

Otd is 3 For March Then New March Date=0 Because it should not go beyond feb

Now We Look For May


Ex:OTd Is 0 For May Then New May Date=May*Vrf

Otd is 1 For May Then New May Date=apr*Vrf

Otd is 2 For May Then New May Date=March*Vrf

Otd is 3 For May Then New May Date=Feb*VRF

I am Posting My Vba Code Here

(

[pre]
Code: 
Sub AdjustedOpenOrders()

'define variables
Dim n As Double
Dim a As Integer 'rows
Dim b As Integer 'columns
Dim c As Integer '
Dim rng As Integer
Dim wsdata As Worksheet
Dim lastrow As Long
rng = WorksheetFunction.Max(Worksheets("12M_OpenOrders").Range("O:O"))
Set wsdata = Worksheets("12M_OpenOrders")
Sheets("12M_OpenOrders").Select
lastrow = wsdata.Cells(Rows.Count, "A").End(xlUp).Row
wsdata.Range("C1:N1").Select
Selection.Copy
wsdata.Range("Q1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "yy-mmm"

For a = 2 To lastrow  'counter loop for the rows
For b = 3 To 14  'counter loop for the columns
For c = 0 To rng
If Range("O" & a).Value = c Then
On Error Resume Next
n = Cells(a, b).Offset(0, "-" & c) * Range("P" & a).Value
On Error GoTo 0
wsdata.Cells(a, 14 + b).Value = n

End If
Next c

Next b

Next a
End Sub
[/pre]
)

This code Has Two Problems One It Is Slow

Tow If I Skip On Error Stements Form The Code It IS Giving Type MisMatch Error

Because For March If Otd Is 3 it is going Beyond Column A

Even Otd is 2 For March It Going To PC(i.e Column a)

How To Stop At Column B i.e Month Feb
 
Hi ramakriishna,


Welcome to the forum and we glad to have you here.


Could you please upload your sample workbook here?

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik
 
hi

I am Creating A Monthly Report For That I Have To Create 12 Dates Based On Previous

13 Dates

My Created 12 Dates look Like This 13-mar,13-aPr,------,14-feb

13 is 2013,14 is 2014

If I Am Creating Report For Next Date My Dates Look Like This 13-apr,-----,13-Mar

These 12 dates are 12 columns

My Data Contains Columns Like this

Product Code,13-feb,13-mar,13-apr,13-may,-----,14-jan,14-feb,OTD,VRF

Based on OTD Column Max Value The Dates Should Move Forward And Multiply With Vrf

Ex:Otd Max Value Is 3

Then The Current Month March should move three columns left and multiply with VRF Colum

To Create New March Date

Ex:OTd Is 0 For March Then New March Date=March*Vrf

Otd is 1 For March Then New March Date=Feb*Vrf

Otd is 2 For March Then New March Date=0 Because it should not go beyond feb

Otd is 3 For March Then New March Date=0 Because it should not go beyond feb

Now We Look For May


Ex:OTd Is 0 For May Then New May Date=May*Vrf

Otd is 1 For May Then New May Date=apr*Vrf

Otd is 2 For May Then New May Date=March*Vrf

Otd is 3 For May Then New May Date=Feb*VRF

I am Posting My Vba Code Here

[pre]
Code: 
Sub AdjustedOpenOrders()

'define variables
Dim n As Double
Dim a As Integer 'rows
Dim b As Integer 'columns
Dim c As Integer '
Dim rng As Integer
Dim wsdata As Worksheet
Dim lastrow As Long
rng = WorksheetFunction.Max(Worksheets("12M_OpenOrders").Range("O:O"))
Set wsdata = Worksheets("12M_OpenOrders")
Sheets("12M_OpenOrders").Select
lastrow = wsdata.Cells(Rows.Count, "A").End(xlUp).Row
wsdata.Range("C1:N1").Select
Selection.Copy
wsdata.Range("Q1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "yy-mmm"

For a = 2 To lastrow  'counter loop for the rows
For b = 3 To 14  'counter loop for the columns
For c = 0 To rng
If Range("O" & a).Value = c Then
On Error Resume Next
n = Cells(a, b).Offset(0, "-" & c) * Range("P" & a).Value
On Error GoTo 0
wsdata.Cells(a, 14 + b).Value = n

End If
Next c

Next b

Next a
End Sub
[/pre]
This code Has Two Problems One It Is Slow

Tow If I Skip On Error Stements Form The Code It IS Giving Type MisMatch Error

Because For March If Otd Is 3 it is going Beyond Column A

Even Otd is 2 For March It Going To PC(i.e Column a)

How To Stop At Column B i.e Month Feb


http://www.2shared.com/file/H_q3H--N/Openorders.html
 
Hi Ramakriishna ,


You are wanting members of this forum to troubleshoot your macro ; the file you have uploaded is a .xlsx workbook , which clearly does not contain the macro.


Do you want that those who are interested in providing you a solution should copy + paste the macro from your post into the workbook ?


I did that , and found that the worksheet name in the macro is 12M_OpenOrders , which is not present in the uploaded workbook ; does this mean that I now have to change the name of the sheet from Sheet1 to 12M_OpenOrders ?


If you think this forum has been created so that people who are interested in providing solutions get a platform , please rethink ; this forum has been created so that people who have problems , and are interested in getting solutions , get a platform.


Please do all that you can , so that we can do all that we will.


Narayan
 
You must log in or register to reply here.
Back
Top