Automating Relative entries into the corresponding cells

vikrampnz · Dec 27th 2016

Hello

I have a spreadsheet that has following:

a) Product Payment Schedule that has Unit Number (20A, 20B, 21A,21B, and so on) in the Header Row and Payment stages in the column A i.e.
Stage 1 - 35% (Of the total cost),
Stage 2 - 30%(Of the total cost),
Stage 3 - 20% (of the total cost),
Stage 4 - 15% (Of the total cost)

Each stage requires specific time gap before it goes to the next stage. i.e. If Stage1 is Month1, then stage 2 will come in Month3, stage3 will come in Month6 and Stage4 in Month8 when the product will be delivered.

Depending upon the the Unit Sale Price entered, it applies the percentage and splits the stagewise payments for each Unit (in their specific columns and in corresponding rows showing the stage number in left column)

I am trying to build a cash flow and hence trying to export this information into another worksheet named "Unitwise Cashflow" where the Header Row contains Month/Year i.e. Feb-17, Mar-17, Apr-17 and so on till Feb-20 (B5:AL5) and column A contains Unit numbers (A6:A29).

I am trying to automate this part by having the Unit numbers displayed in Combobox1 and Month/year list displayed in combobox 2 as the start date for that unit. And the following stage amounts (from other worksheet) should fall under respective months automatically.

For example, If I select Unit "21B" from the Unit List (Combobox) and Select "May-17" from Combobox2 as Start Date for the Unit, then Amount for the Stage1 will fall in a cell that corresponds to the Rows that has 21B in column A and column that has "May-17". As the time duration between each stage is fixed, once I set the starting Month, can I have the corresponding values from the following stages i.e. Stage2, stage3 and Stage4 fall into respective Month/Year Columns.

I would appreciate if someone could help. I am unsure of what this functionality is called and what I should be searching for.

Thanks again

V

I have attached a test file to try and explain what I am trying to achieve., Thanks

bryce · Dec 27th 2016

Re: Automating Relative entries into the corresponding cells

Here is one way you could do this. I created a named range (not dynamic) that has the months from the "Unitwise CashFlow" tab called SaleMonth and populate the combobox with it. After you pick a unit and a month and click "Enter", the values from your "Payment Schedule" are written to the Unitwise tab.

Code

Private Sub cboMonth_GotFocus()
    Dim rng As Range
    
    For Each rng In Me.Range("SaleMonth")
        cboMonth.AddItem rng
    Next rng
End Sub


Private Sub cmdEnter_Click()
    Dim wsPay As Worksheet
    Dim findRow As Range, findMonth As Range, findRateInfo As Range
    
    Application.ScreenUpdating = False
    
    Set wsPay = Sheet3
    
    Set findRow = Me.Range("UnitList").Find(What:=cboUnit.Value, LookAt:=xlWhole)
    Set findMonth = Me.Range("SaleMonth").Find(What:=cboMonth.Value, LookAt:=xlWhole)
    
    Set findRateInfo = wsPay.Range("A1:M1").Find(What:=cboUnit.Value, LookAt:=xlWhole)
        
    ' Clear previous entires
    Me.Range("B" & findRow.Row & ":AL" & findRow.Row).ClearContents
    
    ' Stage 1
    Me.Cells(findRow.Row, findMonth.Column).Value = wsPay.Cells(10, findRateInfo.Column)
    
    ' Stage 2
    Me.Cells(findRow.Row, findMonth.Column + 1).Value = wsPay.Cells(11, findRateInfo.Column)
    
    ' Stage 3
    Me.Cells(findRow.Row, findMonth.Column + 3).Value = wsPay.Cells(12, findRateInfo.Column)
    
    ' Stage 4
    Me.Cells(findRow.Row, findMonth.Column + 4).Value = wsPay.Cells(13, findRateInfo.Column)
    
    ' Stage 5
    Me.Cells(findRow.Row, findMonth.Column + 6).Value = wsPay.Cells(14, findRateInfo.Column)
    
End Sub

Display More

vikrampnz · Jan 9th 2017

Re: Automating Relative entries into the corresponding cells

Hello

Sorry for the late reply.

I tried the sample sheet you have attached. It seems to work, but for some reason it only starts with SaleMonth as July 17. If I select any other month, it is giving error.

Could you please help !

Thanks again for your response.

Cheers
V

bryce · Jan 9th 2017

Re: Automating Relative entries into the corresponding cells

What error are you getting? All of the months in the drop down are the result of your headers. The various finds look for that month as well as the item to find and since all are there it should not cause an error. But please let me know where the error is and I will sort it.

vikrampnz · Jan 9th 2017

Re: Automating Relative entries into the corresponding cells

Hi Bryce

Irrespective of what Month I select from the drop down, it seems to insert the Stage1 Value under Jul-17. Its picking the correct values for the selected Unit no, but just placing them under wrong Months.

I really appreciate your help !

Cheers
V

bryce · Jan 9th 2017

Re: Automating Relative entries into the corresponding cells

I cant duplicate the issue. The report works as expected on my end. Are you using the file I posted or are you using a different file? Please post the file you are working with.

vikrampnz · Jan 10th 2017

Re: Automating Relative entries into the corresponding cells

Hi Bryce....I have downloaded your file and was trying the same. I have uploaded the same here again. (which is your file)

What I notice is that 1) after every entry, the Month dropdown defaults to Jul-17, 2) For some months it is giving Run-time error "91" ...."Object Variable not set"

I am not sure why you are unable to replicate the problem at your end. In the attached file, when I enter a sale month, it only falls under Jul-17. It is picking correct values, entering them at correct intervals......but only starts from Jul-17.

Thanks a lot for your help !

bryce · Jan 10th 2017

Re: Automating Relative entries into the corresponding cells

I am sorry I just cant recreate the problem. Literally, if it always enters data to July then you are not actually choosing another month. That is the only reason your scenario would be true.

vikrampnz · Jan 10th 2017

Re: Automating Relative entries into the corresponding cells

Does the file I uploaded work fine at your end? I will try copy the code into another workbook tomo and try again...will let you know

bryce · Jan 10th 2017

Re: Automating Relative entries into the corresponding cells

Yep, it works:)

vikrampnz · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

Hi Bryce.....I wonder if this has anything to do with the version of excel I am using? I am using Excel 2007. I pasted the code in another sheet and its giving me "Run-time error 91". It says "findMonth.Column= <Obejct Variable or With Block Variable not set>" ...... Could you please help....I have attached the screenshots for your reference...thanks

vikrampnz · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

When hover the mouse over the code, on "Dim findRow As Range, findMonth As Range, findRateInfo As Range", it shows correct values from the combobox selection on "findrow As Range" and "findRateInfo as Range", but when I hover over "findMonth As Range", it says "findMonth = Nothing" ...... does this help identify the possible problem at my end?

I tried reading through Google search on runtime error 91, some people are talking about Registry cleaning for solving this problem......do you think it could be this by any chance?

Thanks a lot for your patience and help Bryce.....and sorry for too many questions....but I am really desperate to make this work.

Awaiting your feedback..

bryce · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

In that second picture it looks like the date in the date combo box is 1/4/2017. There is no 1/4/2017 in the header row (row 5) which is why you are getting an error. Essentially, you have made changes to the workbook that were not present when I gave you the sample.

The original file I gave you had a named range that referenced the header row. That named range was used to populate the date combo box. The point of this was to ensure that there would ALWAYS be a match since it would be impossible for you to choose a date from the combo box that was not in the header row.

However, it appears that you are using some other method to populate the date combo box that is not consistent with the code or the structure that I provided.

So...how are you populating your date combo box?

vikrampnz · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

Hello.... I have the same name range "SaleMonth" that is created in the header row displaying months.... That is populated in the combobox using the code you gave earlier....

The months are displayed in header row in "mmm-yy" format, but when I select a header cell it shows the entire date i.e. normally a cell would show Jun-17, however when selected it shows 01/06/2017 in the upper information bar .... I am not sure how to get it to look like month-year format in the upper cell content display space...

At my end, Even in your original file (which you sent), dates are displayed in the same format in drop down (01/06/2017).....

Is there a way I can change date format?

Thanks for your help and patience

bryce · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

Dude....look at the picture you provided. The headers begin with Feb but you have a Jan date....there is no Jan date in the header at least not a Jan date in 2017. Please post the most recent iteration of the file you are working with. I will give it one last go and if you cant make it work I would just encourage you to look for another solution.

vikrampnz · Jan 11th 2017

bryce · Jan 11th 2017

Re: Automating Relative entries into the corresponding cells

Your using a European date format. I get it now. Ok then yes, the find method does have issues with date formats. Check out the link below. The easy solution is just to change the format

http://www.ozgrid.com/VBA/find-dates.htm

Automating Relative entries into the corresponding cells

Participate now!

Share