Hi forum,

So I have a bit of a problem. My programming skills are weak to say the least and Im trying to create a fancy spreadsheet in my computing class. The task is to create a sheet in which you enter the sales data (UserformHPI) and it then prints the info you enter onto a table. I have completed that part and can be seen if you click 'Enter sales data' on the data sheet. The problem I have now is selecting that data [one customers info] and calculating the Required down payment, monthly repayments, outstanding balance and due date of the first repayment for them based on the values in the VBA Userform (UserformHPC). Its hard to explain on here so i have uploaded the spreadsheet to my website for you to grab

http://cyberbeatnz.com/Excel/HPSales.xls.zip

The other thing I am finding difficult is the code to sort the data in the table into an order selected by an optionbox in a userform (Userformsort). Err I wish i could do it myself :P

If anyone is brilliant enough to help me out on this one it would be greatly appreciated!

Thanks heaps in advance for your help!

Nick

So I have a bit of a problem. My programming skills are weak to say the least and Im trying to create a fancy spreadsheet in my computing class. The task is to create a sheet in which you enter the sales data (UserformHPI) and it then prints the info you enter onto a table. I have completed that part and can be seen if you click 'Enter sales data' on the data sheet. The problem I have now is selecting that data [one customers info] and calculating the Required down payment, monthly repayments, outstanding balance and due date of the first repayment for them based on the values in the VBA Userform (UserformHPC). Its hard to explain on here so i have uploaded the spreadsheet to my website for you to grab

http://cyberbeatnz.com/Excel/HPSales.xls.zip

The other thing I am finding difficult is the code to sort the data in the table into an order selected by an optionbox in a userform (Userformsort). Err I wish i could do it myself :P

If anyone is brilliant enough to help me out on this one it would be greatly appreciated!

Thanks heaps in advance for your help!

Nick

- Loan repayment calculation
- Writing a VBA code for Dsum function
- Building a VBA program
- Help in writing a VBA code for calculating percentage
- Calculate Home Loan Repayments
- How to write a VBA code to select certain rows based on certain conditions
- Need help on a VBA macro code..I have stuck.
- Repayment Calculator
- HELP: calculate the frequency of similar strings in a list
- Unique Loan Repayment calculation
- Formula to calculate student loan repayments over certain value
- Need a vba code to do match
- Creating A Tax Calculator In Vba
- First code in VBA
- IMPROVE A VBA CODE
- Calculating minimum loan repayment
- Calling up the Solver function in a VBA macro
- VBA variable length array help needed
- Need a VBA Code for fliping images in a User Form
- A VBA code to select & show one chart from many
- How to change a formula into a VBA code
- VBA coding a nested Vlookup, and a sumif formula
- I need a loan payment calculator/balance sheet w/missed or infrequent payments
- VBA macro using calculated value out as new input in.

Maybe someone out there can help. I have a loan facility for the period 1 Jan 08 to 1 Jan 18, but with variable draw-downs on the facility over the first few years.

So in the first year I may draw 20% of the loan, the second 25% and so on.

Is there a straightforward way of calculating the principal repayment amounts (assuming equal repayments of that principal over the remainder of the loan period) without having to treat each draw down as a separate loan?

It's late here and I think maybe I am missing something obvious!

Thanks!

Can someone help me with writing a VBA code for the Dsum function. I basically want to have a VBA formula which can help me write the criteria in the VBA formula itself rather than as reference to another range as it is currently in the Dsum function. I have attached an excel file where I am showing the Dsum function, however, I want to do the same thing, but using a VBA code. The reason is that I have a huge amount of data and I want to avoid writing the Criteria in a separate range of cells and then refer to the range in the Dsum function. Many thanks for your help.

I need a VBA code to do the following.Would appreciate your help in this regard

For every Bill to party in column A(the bill to party number is unique)

1.In column “Count(Sales Doc)”, sum the number of 1s and display the sum in the results row

2. In column “Count(Diff)”, sum the number of 0s and display the total number of 0s in the “results “ row(the yellow row)

1.In Column J under “ Percentage” I want to use the following formula and display it only for the results row:

Count(Diff)

---------------- x 100

Count(Sales Doc)

Hope the above is clear .Let me know if its not.

Thanks for the help in advance

Raja

I was wondering if anyone can help in what formulas to use to calculate home loan repayments. I have used the PMT function but this gives me the total to pay per month.

I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.

So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.

Is this possible or is this too complicated.

Regards,

George

calculations on rows that meeting certain requirements, but I do not

know how to established that in VBA. I know that if I use the

worksheet itself, I can do something like SUMIF() function. But, when

I put the same function in the VBA editor, it returns error. A simple

version of my data:

Group No Member No Amount spent

1 1 10

1 2 3

1 3 4

2 1 6

2 2 1

3 1 12

4 1 3

4 2 1

I am trying to calculate the total amount spent based on the group no.

FOr example, how much does group no.1 spent, how much does group no.2

spent, etc.

I appreciate anybody who can help me with this. Thank you very much.

It's based on an online calculator, and the figures in the spreadsheet have to match the online results. The trouble is I can't match it exactly and need help with this.

The calculator works out 2 things;

1. What the loan balance will be after 25 years (300 months) using a fixed monthly payment.

2. What the loan balance will be after 25 years (300 month) using an increasing payment based on an increasing salary.

I'll try and explain what it does at the moment;

The inputs are Salary (A), Loan amount (B), Annual Interest Rate (C), Salary Increase rate (D), Fixed Monthly Payment (E), Increasing Monthly Payment (F1-F25) i.e. one for each year.

I can work out what the fixed monthly repayment amount is based on the salary. And what the increasing monthly payment will be based on an increasing salary. The formula to work out the increasing salary is A*(1+D)^1, and then from that I work out the monthly payment. This part works ok.

The part that I can't get to match is the decreasing Loan balance e.g. what the balance would be after deducting the monthly payment, and adding the interest for that month.

The formula I'm using is;

(B-E)+((B-E)*(C/12))

This is worked out for each month, and the Loan amount for the 2nd month is the balance at the end of the previous month, and so on.

For increasing payments, the Formula would be;

(B-F1)+((B-F1)*(C/12)) for all of year 1, then F would increase for year 2, 3 and so on.

I am doing this right, is there a more accurate way? Or is there a way I can improve this method to get it to match what's getting done online.

The formulas in the spreadsheet are the same as what the code of the online calculator do.

thanks

the kicker:

-there is no predefined number of strings

-there is no predefined list of strings to reference

so each time a new data set is entered the total number of entries may change, and the order and composition of the list of strings may change.

I appreciate any help that you may be able to give.

Thanks!

I am an absolute novice with Excel (only used templates, mostly...) so please excuse if there is a simple answer to my issue which I have overlooked.

I am borrowing $2500 from a friend. I will be repaying different amounts at different times. We have agreed that I will repay the $2500 plus the interest she would have earned from her savings account on the amount loaned, 1.5% annually (which may change if rates improve before I have fully repaid her) calculated daily and paid monthly.

She is loaning me the money to help me purchase equipment needed to start a small business (a new camera for my photography business), I will be paying her back with any profit I earn until I have repaid the amount including the interest.

I am not sure if I am overthinking the situation and all it is is a simple interest calculation or if this is a more complicated issue. I am looking for a way to be able to input the amounts I have repaid and calculate the interest on the amount still outstanding until I reach a zero balance owed.

I think it's simply a FV calculation in which I would change the PV each time I make a payment, but I am not sure and would love some experienced feedback.

Any help or guidance in figuring this out is MUCH MUCH appreciated!!!

I want to calculate the amount of money I will pay over a certain value.

i.e. Everything over £15000 of my salary will be charged 9% towards student loan repayments.

I was thinking (for cell N3): =IF (D3>15000=D3*0.9) (but this doesnt work)

"D3" is the cell which has my salary in.

I'd really appreciate any help!!

Thank You!

I need a vba code that will work in the following way.

I have 2 sheets in a workbook Sheet("Movement Box") and sheet("B1 Movements").

In sheet ("Movement Box"), data starts from the 1st row. Column "C" has reference numbers for example "44332211" which are unique. Column "S" has a names filled in certain rows for example "Paul" and the remaining cells in column "S" are mostly blank cells.

In Sheet ("B1 Movements"), data starts from the 3rd row. Column "C" has reference numbers from "C3" for example "44332211" which are unique.

I need the code to do the following:

In sheet ("Movement Box") loop down column "S" from "S3" until column "C" from "C3" has reference numbers, and only for each notBlank cell (meaning only if there is a name) lookup the respective reference number in column "C" in the same row, find the matching reference number in Sheet("B1 Movements") and update column "M" in Sheet(B1 Movements") in the respective row with the name.

Hope someone out there can help me out. Thanks in advance.

I have just started coding in VBA but just got stuck.

I am writing a module which have a different test based on for each of the previous 2 column cells.

It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i nned to use a inputbox for entering the first date for monday of the month each time i use the worksheet.

When i try using the module for each cell of the column it display the input box for each cell.

is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again

Please help.

Thanks

Ken

.

Hello Masters,

I have a VBA code that creates new sheets.

The code:

* Sorts columns niv1 and niv2 of Sheet "consolidado".

* Using an Advanced Filter, it creates new sheets based on column niv1 of Sheet "consolidado".

* Copy in each new sheet the registers of Sheets "consolidado", "informacion" and "indice".

Now, here starts my problem.

I need the code does the same operation with column niv2, and next with column niv3 of Sheet "consolidado".

In short, I need to create new sheets for C.1, C.2, N.1,.......etc., of column niv2; and for U.2.1, U.2.2, .....etc., of column niv3 of Sheet "consolidado".

If columns niv4, niv5,...., niv10 would have data, the code should create new sheets for those columns too.

Please Masters, help me to include to the VBA code the above explained.

I enclose my file.

I will highly appreciate your kind assistance.

kindest and best regards,

Pedro

.

.

I have a £4K loan for 7 months, the repayment I need to make is 2% of the outstanding balance can anyone think of a good formula to calculate the total payment to be made in 7 months.

The best I have come up with is:

=(4000*0.02)+((4000-(4000*0.02))*0.02)+4000-(4000-((4000-((4000-(4000*0.02))*0.02))-(4000*0.02))*0.02) ect

that is just for 3 months so you can see it would get very big very quickly also not very quick to work out either.

Thanks Danny

macros that might be conflicting as it was a new workbook.

Do other macros work OK, eg just doing basic stuff. Is this peculiar to just

Solver?

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

"Wayne Stewart" > wrote in message

...

> Thanks for the response Ken.

>

> I just loaded up a new excel workbook and inserted your

> ExampleSolv macro in a module within this new workbook. I

> established the Solver reference as instructed since the

> module is new (this was done already on my original macro).

>

> When I run the macro nothing happens (just like my

> original macro). The screen seems to blink for a

> microsecond, as if something is being calculated in the

> background. However, cell A2 doesn't change from 5 to 1

> like it's supposed to.

>

> This is a very basic macro test and solver doesn't

> respond. Is the problem with Excel?

>

> For you reference, I am running Windows 2000, Excel 2000

> SP-3

>

> Any other thoughts?

>

> Thanks.

> -Wayne

>

> >-----Original Message-----

> >Do you mean you still have to hit OK on the dialog box

> for it to take the value?

> >Have you referenced the Solver addin from your project

> (tools / References /

> >Check 'Solver')

> >

> >Does your code look something like this:-

> >

> >Sub ExampleSolv()

> > SolverOk SetCell:="$A$3", MaxMinVal:=3,

> ValueOf:="10", ByChange:="$A$2"

> > SolverSolve (True)

> >End Sub

> >

> >Note you need the parameter True set on the SolverSolve

> bit if you want it to

> >accept the value within the code without a dialog box.

> >

> >Does the above code work for you if you try that with say

> 10 in A1, 5 in A2 and

> >=A1*A2 in A3?

> >

> >--

> >Regards

> > Ken....................... Microsoft MVP -

> Excel

> > Sys Spec - Win XP Pro / XL 00/02/03

> >

> >----------------------------------------------------------

> ------------------

> >It's easier to beg forgiveness than ask permission :-)

> >----------------------------------------------------------

> ------------------

> >

> >

> >

> >"Wayne Stewart" > wrote in message

> ...

> >> I am trying to automate the Solver routine (Under the

> >> Tools pull-down menu) using a VBA macro. I have

> succeeded

> >> at setting the constraints and the various options using

> >> the "SolverAdd" and "SolverOptions" commands. However,

> >> the problem seems to be with the "SolverOK" command,

> where

> >> the "Set Target Cell", "MaxMinValue", and "By Changing

> >> Cells" fields are set. Solver does not seem to register

> >> these values when the "SolveOK" command is used. Also,

> >> when I use the "SolverReset" command followed

> >> by "SolverAdd", and "SolverOptions" commands, no

> >> parameters are passed to solver either.

> >>

> >> The only way I can get the "By Changing Cells" cells

> >> actually changing on the excel worksheet is to first

> >> invoke solver manually (outside the macro), get the

> >> solution by clicking "solve", then deleting the fields

> in

> >> the solver dialog box, closing solver, and then running

> >> the macro!

> >>

> >> Is there a glitch with Excel? Is there a patch that can

> >> be downloaded to fix this problem? I'm positive that my

> >> syntax is correct (the help examples are very straight

> >> forward).

> >>

> >> I don't get any error messages when I run the macro. It

> >> simply just doesn't present the solution on the

> worksheet.

> >>

> >> Hope someone can help me!

> >

> >

> >---

> >Outgoing mail is certified Virus Free.

> >Checked by AVG anti-virus system (http://www.grisoft.com).

> >Version: 6.0.567 / Virus Database: 358 - Release Date:

> 24/01/2004

> >

> >

> >.

> >

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system (http://www.grisoft.com).

Version: 6.0.567 / Virus Database: 358 - Release Date: 24/01/2004

I am a vba self taught hack, and nota very good one at that, and i need some help with variable length arrays. Below is the code I have which works perfectly but does not use arrays.

I have 4 columns. In Column A is a list of names. There are always atleast 2 or more of the same name and the same names are grouped together one under each other.

Then in Column B is a list of numbers representing qty of an item sold by that person. Then in column C is the time in number of days that it took that person to sell that qty.

Nowhere in the VBA code do i refer to column C, but Columd D is Column C multiplied by 5.

This function allows me enter a persons name and a random number of days that does not neccessarily correspond to any exact number of days related to that persons sales up to that period allowing for interpolation and extrapoltion, spewing out an interpolated or extrapolated number

Below is the code I have which works perfectly.

Function QtySold(PersonName, Days) 'First I Find the row number for start of Range in column NameRange = Range("A1:A5000") Startrow = Application.Match(PersonName, NameRange, 0) 'now I find the rownumber for end of range in column Endrow = Startrow + 1 TestforName = Cells(Endrow, 1) While TestforName = PersonName Endrow = Endrow + 1 TestforName = Cells(Endrow, 1) Wend Endrow = Endrow - 1 Set TimeRange = Range("C" & Startrow & ":C" & Endrow) Set CalcRange = Range("D" & Startrow & ":D" & Endrow) nn = Endrow - Startrow + 1 'number of variables in range QtySold = PLI(TimeRange, CalcRange, Days, nn) End FunctionThis function above calls on the following Interpolation extrapolation function.

Function PLI(Xvalues, Yvalues, x, n) xPos = Application.Match(x, Xvalues, 1) If Application.IsNA(xPos) Then i1 = 1 i2 = 2 ElseIf xPos = n Then i1 = n - 1 i2 = n Else i1 = xPos i2 = xPos + 1 End If x1 = Xvalues(i1) x2 = Xvalues(i2) y1 = Yvalues(i1) y2 = Yvalues(i2) PLI = y1 + (x - x1) / (x2 - x1) * (y2 - y1) End FunctionThe above two functions work perfectly, however CalcRange (Column D) is a function of column C multiplied by 5 and what I want to dois create an array of numbers called CalcRange in VBA and use that array when my function calls on the Interpolation function ..QtySold = PLI(TimeRange, CalcRange, Days, nn) and not have to calculate it seperately in excel like i have done in Columd D.

Below attempt is a disgrace i know...but if anyone can help me it would be much appreciated

Function QtySoldAttempt(PersonName, Days) 'First I Find the row number for start of Range in column Dim CalcRange As String NameRange = Range("A1:A5000") Startrow = Application.Match(PersonName, NameRange, 0) 'now I find the rownumber for end of range in column Endrow = Startrow + 1 TestforName = Cells(Endrow, 1) While TestforName = PersonName Endrow = Endrow + 1 TestforName = Cells(Endrow, 1) Wend Endrow = Endrow - 1 Set TimeRange = Range("C" & Startrow & ":C" & Endrow) nn = Endrow - Startrow + 1 'calcualtes the number of variables in the array 'now instead of setting the Calcrange to column D as before, i want to create an array with 'the numbers of the column C numbers multiplied by 5 For icount = 1 To nn rnum = Startrow CalcRange(icount) = Cells(rnum, 2) * 5 Next icount QtySoldAttempt = PLI(TimeRange, CalcRange, Days, nn) End FunctionI hope i have explaind my request. If it is not understood it is no doubt my bad explanation.

Thanks to all those people in adavnce who look to help me.

To overcome this sluggishness, I need a VBA code which sets the formula for the image ( forcing the chart to change) and deletes the formula from the image again to leave it ' static'.

I've noticed that when I manually delete the formula, the selected chart stays and Excel sluggishness goes away completely, but without the formula no other chart can be selected. A sample workbook is attached

Any help will be greatly appreciated.

This formula is in cell L14 and i will need to duplicate it accross cells in the range of L8: Q44, but i'm figuring i might be able to copy and paste after i get it in a VBA format and just change my values.

=IF(ISNA(INDEX('Weekly Total'!$B$8:$H$2000,MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=L7),0),7)),"",INDEX('Weekly Total'!$B$8:$H$2000,MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=L7),0),7))

I have this formula:

And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for worksheets, but it gives me #Value! at row 100.Sub Run_Data() Dim iLastRow As Long Dim i As Long With Sheets("Info1") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = 3 To iLastRow 'iLastRow to 1 Step -2 .Cells(i, "K").Value = Evaluate("=IF(ISERROR(VLOOKUP(IF(C" & i & "="""",VLOOKUP(" & _ "D" & i & ",Data!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" & "C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i & "& ""C0MISCELLANEOUS"",H" & i & _ "&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i & ",Data!B:O,F3+2,0),IF(" & "D" & i & "="""",VLOOKUP(C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))") Next i End With End Sub

And how would I write a VBA Code to run from F7 to DN71 for the formula below:

I could have used a code similar to the code for the vlookup one above, but I'd have to do that code for too many column. I want a more flexible formula.

I cross-posted or posted-multi posted because the code didn't give me the desired result and I needed a response quickly.

And I think I got into a little bit of trouble. Well, that's what I get for not reading the rules.

Thanks

Xrull

http://groups.google.com/group/micro...&q=dingo&pli=1

http://www.mrexcel.com/forum/showthr...ighlight=xrull

http://www.microsoft.com/office/comm...a-bced830f0d47

Thanks,

Xrull

I am VERY new to Excel and not familar with a whole lot yet.

Any help and patience is very much appreciated.

Thanks for your time!!

Lisae

I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.

I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.

Thanks any help is appreciated.

No luck finding an answer? You could always try Google.