Allocate Values from a range into columns under corresponding Months

vikrampnz · Jan 7th 2017

Hello

I have a table with Unit Numbers in Column A (24A, 24B, 25A, 25B and so on) , Sale Month in Column B, Price in Column C and Stagewise Price Breakup (10 Stages) in Columns D to M.

Price Breakup is pre-defined (Like monthly installments) based on certain percentage applied to each stage and that payment becomes due when that particular stage is completed. These stagewise payments are not equal.

Time frame for each stage is also pre-defined i.e. if "Stage1" i.e. "Sale" happens in "Month1", then stage2 comes in Month2, Stage3 comes in Month4 and so on. There are 2 month gaps between certain stages. Once the Sale of specific Unit is made, then a specific month (For example Mar-17) will be applied as start month (For stage1) for that unit.

I have another sheet which looks like a running calender, which has Unit Numbers in Column A (same as the previous Data sheet) and Column B onwards have Months in the header row i.e. Feb-17, Mar-17, Apr-17 and so on till year 2020.

I am trying to figure out a way to automatically allocate values in the Data table to the Calender under specific months, in the row for the specific Unit No.

For example, if Unit 25B is sold in Mar-17, then I will enter "start Month" or "Stage1" as Mar-17 and the following stagewise Values (which are already pre-defined in the table) should be allocated in the calendar under correct month i.e. Stage2 value should fall under Apr-17, Stage3 Value should fall under Jun-17 and so on.

Can someone help please ! I would really appreciate it !

Thanks
V

jaslake · Jan 7th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hi V

Try this Code in the attached. Open the File to Sheet "CashFlow". Ctrl + x will file the Code.

Code

Option Explicit
Sub Spread_CashFlow()
   Dim srcWS        As Worksheet
   Dim tgtWS        As Worksheet
   Dim srcLR        As Long
   Dim tgtLR        As Long
   Dim tgtLC        As Long
   Dim rng          As Range
   Dim cel          As Range
   Dim myRow        As Long
   Dim myCol        As Range
   Dim i            As Long
   Dim j            As Long
   Set srcWS = Sheets("PS")
   Set tgtWS = Sheets("CashFlow")
   Application.ScreenUpdating = False
   With tgtWS
      tgtLR = .Range("A" & .Rows.Count).End(xlUp).Row
      tgtLC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious).Column
      .Range(.Cells(4, 2), .Cells(tgtLR, tgtLC)).ClearContents
   End With
   With srcWS
      srcLR = .Range("A" & .Rows.Count).End(xlUp).Row
      Set rng = .Range(.Cells(4, "A"), .Cells(srcLR, "A"))
      For Each cel In rng
         myRow = tgtWS.Columns("A").Find(What:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
         Set myCol = tgtWS.Cells.Find(What:=CDate(cel.Offset(0, 1).Value), After:=tgtWS.Range("A1"), LookIn:=xlFormulas _
                   , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
         j = myCol.Column
         For i = 4 To 13
            tgtWS.Cells(myRow, j).Value = .Cells(cel.Row, i).Value
            j = j + 1
         Next i
      Next cel
   End With
   Application.ScreenUpdating = True
End Sub

Display More

vikrampnz · Jan 9th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hello

Thanks jaslake.....it works brilliantly, however there are two issues.

1) All the production stages do not fall in consecutive months...for example. Stage2 is in Month2, but Stage3 falls in Month4. (there is a 2 month gap between stage2 & stage3). So if Stage2 Falls under Mar-17, then Stage3 will need to fall under May-17.

2) Stage9 and Stage10 , both fall under Month10. So, combined value of Stage9 & stage10 needs to be shown in the same Month i.e. Month10.

I really appreciate your help in this.

Actually, Initially to make it more professional looking & user friendly, I was trying to see if this could also be achieved with the help of Combobox and command button on the worksheet "Cashflow", where Combobox1 will show the list of Units, Combobox2 will show the List of Months (to select the Sale Month or start Month or Month1) and rest of the process will remain the same where the based on the Month1, remaining stage values will fall in corresponding months.

I am not sure how difficult or complicated this could get, but just a thought. If this is too complex and time consuming, then I would appreciate if you could just help me with the above two issues.

Thanks a ton again

Cheers
V

vikrampnz · Jan 9th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hello

I am trying to insert another set of exactly identical tables for expense side in Worksheet "Cashflow" and Worksheet "PS". I mean exactly identical table from rows 32 to 53 with similar headings in Worksheet "PS" and rows 34 to 57 in worksheet "Cashflow". These second set of tables have expense / cost amount for each stage instead of the billing amount.

So, I am trying to build something where when I enter sale month for a Unit number, then it will add billing values (from first table) under specific months and will also enter the expense value in second table from expense table for that unit.

I tried copy - pasting your formula in another worksheet, but It gives me error highlighting "j = myCol.Column" ..... I am obviously doing something wrong while replicating the formula..., but am unable to identify it

Can you please help ! Thanks a lot.

jaslake · Jan 9th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hi V

I'll need to ponder these two issues...I'll get back to you.

vikrampnz · Jan 9th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hi jaslake.......Ok...Thanks for letting me know. Will wait for your suggestions.

jaslake · Jan 9th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hi V

This Code in the attached appears to resolve this...

Quote

1) All the production stages do not fall in consecutive months...for example. Stage2 is in Month2, but Stage3 falls in Month4. (there is a 2 month gap between stage2 & stage3). So if Stage2 Falls under Mar-17, then Stage3 will need to fall under May-17.

2) Stage9 and Stage10 , both fall under Month10. So, combined value of Stage9 & stage10 needs to be shown in the same Month i.e. Month10.

Code

Option Explicit


Sub Spread_CashFlow()
   Dim srcWS        As Worksheet
   Dim tgtWS        As Worksheet
   Dim srcLR        As Long
   Dim tgtLR        As Long
   Dim tgtLC        As Long
   Dim rng          As Range
   Dim cel          As Range
   Dim myRow        As Long
   Dim myCol        As Range
   Dim i            As Long
   Dim j            As Long




   Set srcWS = Sheets("PS")
   Set tgtWS = Sheets("CashFlow")




   Application.ScreenUpdating = False
   With tgtWS
      tgtLR = .Range("A" & .Rows.Count).End(xlUp).Row
      tgtLC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious).Column
      .Range(.Cells(4, 2), .Cells(tgtLR, tgtLC)).ClearContents
   End With




   With srcWS
      srcLR = .Range("A" & .Rows.Count).End(xlUp).Row
      Set rng = .Range(.Cells(4, "A"), .Cells(srcLR, "A"))
      For Each cel In rng
         myRow = tgtWS.Columns("A").Find(What:=cel.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
         Set myCol = tgtWS.Cells.Find(What:=CDate(cel.Offset(0, 1).Value), After:=tgtWS.Range("A1"), LookIn:=xlFormulas _
                   , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
         j = myCol.Column




         For i = 4 To 13
            Select Case i




            Case 4, 5
               tgtWS.Cells(myRow, j).Value = .Cells(cel.Row, i).Value




            Case 6, 7, 8, 9, 10, 11
               tgtWS.Cells(myRow, j + 1).Value = .Cells(cel.Row, i).Value




            Case 12
               tgtWS.Cells(myRow, j + 1).Value = .Cells(cel.Row, i).Value + .Cells(cel.Row, i + 1).Value
               
            End Select
            j = j + 1




         Next i
      Next cel
   End With
   Application.ScreenUpdating = True
End Sub

Display More

The other "Wants" should probably be the subject of a new Thread. You may PM a Link to your new Thread if you wish.

vikrampnz · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Thanks a ton jaslake ! It works like a dream ! Thank you so much for your help !

vikrampnz · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hello jaslake, sorry to bother you again.

Without changing anything (structure or setup) I tried to link the "Price" column in sheet "PS" to another sheet price calculation sheet I added and its giving me error now. Its giving "Run-time error 91" .

Could you please help ! I would really appreciate.

I am attaching the modified file for your reference alongwith the screenshots for the error.

Thanks again !

jaslake · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hi V

See this? Needs fixed...

[ATTACH=CONFIG]71057[/ATTACH]

vikrampnz · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Hello.....I tried this again on another copy of the file and the above mentioned last problem seems to have gone away.....Sorry for the trouble....Thanks again

vikrampnz · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Oh...k....so it was the date format in the header that was causing the problem?

vikrampnz · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

Got it....the Sale date format in PS sheet....I fixed it in the same old sheet and that worked too....thanks a lot for all your help and patience.

jaslake · Jan 10th 2017

Re: Allocate Values from a range into columns under corresponding Months

You're welcome. Thanks for the Rep.

Allocate Values from a range into columns under corresponding Months

Participate now!

Share