Re: Using a String Variable in Formula
Thanks guys
That was just what I was trying to do.
Re: Using a String Variable in Formula
Thanks guys
That was just what I was trying to do.
Hi
Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)?
Im trying to do the following in VBA
mortgageSchedName = acName & "Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"
the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.
Any way round it?
Thanks again
Hi
I seem to be having a problem when creating new sheets in a workbook, only after deleting a few. The sheets are created and deleted through VBA Code.
To give you an example: -
Lets say I run Macro1 which adds 2 sheets and copies 1 sheet & renames it, all sheets have data on them.
Next I run Macro2 which deletes the sheets that I have created and the 1 that was copied.
Now when I run Macro1 again it will create the two sheets but it fails on the thrid sheet, which its meant to copy from another.
It gives the error message : -
"-2147417848 (&H80010108): The object invoked has disconnected from its clients."
Now the temporary solution that works is, After running Macro1 and Macro2, close Excel down and then reopen and run Macro1 again and it works fine.
I think its something to do with Excel reorganising the sheets in the workbook when the workbook is opened.
The code for Macro2 is
Function AddLoanSht()
' the variables below are global String variables
mortgageIntLoanName = acName & "IntLoans"
Sheets("IntLoans").Copy After:=Sheets(1)
Sheets("IntLoans (2)").Select
Sheets("IntLoans (2)").Name = mortgageIntLoanName
End Function
Display More
I dont reckon there is anything wrong with the code as it works whenever Excel is fresh. (then again I might be wrong)
Any help is greatly appreciated.
Many Thanks
Re: ShowUserForm not working in a Macro
Ive attached an example sheet, if you click on cell D21 and then click (top menu) Data --> Form. It will show up the dataform window, click next on the dataform window and it lets you enter numbers into the additional payment column. ALL great!
BUT if you goto the macro's and run MACRO1 it returns an error message! even thought its doing exactly the same thing.
Any Help?
Hi
For some strange reason I cant seem to use the DataForm (Data > Form).
It returns with an error stating it .....cannot determine which row in your list or selection contains column labels.....
Now if I select the same cell and run it manually via Data > Form. It works fine. :confused:
Im using the following code to bring access the data form
Anyone come across this before?
Thanks
Re: Extracting Last Row of every month
Oops, here it is.
I have tried this:
Extract the month from the date using Month() from all transactions on a
using the '=IF' function, determine if the last transaction month is different from this transaction month, if it is then show balance. But I think Ive got the If function wrong.
Also once the if function is working, how can I get Sheet1 to fetch the balance totals for end of each month.
Thanks again.
Re: Extracting Last Row of every month
Ive looked into Pivot Tables but is there any way of achieving what Im trying to do without using pivot tables. Tried messing around with Pivot Tables but cant get it to select the last transaction (row) of every month.
Ive made a much better sample sheet. Sheet1 collects the last row of every month from every account. Ive only populated the "Egg" account, to illustrate what I mean.
The egg transactions are stored in the Egg sheet.
Please please help.
Re: Showing a Range or Sheet on a userform
Thanks alot guys!
Appreciate your help!
Re: Showing a Range or Sheet on a userform
Yepppp that is exactly what I was after.
Thanks alot!
Now Im trying to figure out how I can say reference it to Sheet1 or even better a particular part of Sheet1?
Thanks again
Hi
Is it possible to show a sheet or a range on a userform?
I know it can be done with charts. Where the chart sits on a userform, can the same be done with a Sheet? Ive looked everywhere but cant find anything.
Thanks everyone.
Hi All
Im in need of desperate help with my final year project, its a home finance system and consists of several worksheets (accounts) each have list of transactions including date, description, amount in or out and the Balance for that date.
(I need to consolidate the data from each sheet to a single (planning) sheet)
The planning sheet needs to get the closing Balance (last transaction) of every month from every sheet. (Some of these might be - balances where as some are plus.)
To give you a better idea of what Im talking about, Ive attached a sample of what Im trying to do, (but Ive done it manually)
Anyway of doing this through formulaes or VBA?
Many Many Thanks.
Re: Adding dates/reminders to Calendar in VBA
Hi
Yep Im using it on my sample, Ive attached the sample with the code in it.
When a date is selected on the Calendar, nothing is selected on the list and when a item on the list is selected the Calendar reverts to a odd date in Year 1900.
Its ok, if it cant be done then I'll have to find another way around it.
Thanks for your help
Re: Adding dates/reminders to Calendar in VBA
Sorry to be a nuisance but Ive tried the code and it points to the wrong date on the Calendar in year 1900?
Cant seem to figure out the problem, the dates are all 2006.
:confused:
Re: Adding dates/reminders to Calendar in VBA
Hi
Thanks for the reply, didnt quite follow that code but to make things simlper I have attached the file.
When the form is run, the listbox is populated with data from Sheet1. Which has 2 columns, 1 containing dates and the other containing words.
When the user clicks an item in the listbox, It should goto that date on the Calendar but its going to a wrong date in Year 1900?
Also want it so that when the user clicks on a certain date on the calendar it finds and selects the date on the listbox.
Any help is greatly appreciated.
Thanks
Hi
Im using the Calendar object built into VBA/Excel and currently have a form with a large calendar on it. Wanted to know if its possible to add text on particular dates?
For example lets say my mortgage is due out 15th of every month. I want to somehow add that to the calendar. so it shows "Mortgage" on the 15th of every month.
Or even having a hidden spreadsheet holding the names and dates. Then upon calendar initialising, fetches the names and dates and puts them on correct dates?
Anything like this possible?
Any help would be greatly appreciated.
Re: Loan Amortization in a form
Yeah I guess I can do it that way.
Its nice and easy.
Thanks.
Re: Loan Amortization in a form
Thanks for the reply
Creating the amortization loan wont be a problem. The figures input by the user will be placed at appropriate cells on a hidden worksheet which would then calculate the loan payments etc...
The problem I will face is, how the user will interact with the calculated schedule, without them dealing with the actually spreadsheet directly.
Any way of referencing the range showing the scheduled payments on a userform?
Hi
Im in the process of putting together a home finance system for my university project and one of the key requirements of the system is that the system should avoid the need for the user to interact directly with the spreadsheet. So currently everything it does is through forms.....e.g. Add accounts, add transactions to accounts etc.
Now I need to somehow implement the loan amortization feature and I was wondering if its possible to somehow 'addin' a spreadsheet loan amortization schedule the system creates on to a form which the user can interact with? (instead of directly with the spreadsheet)
If this is not possible anyone got any others ideas on how it might be possible to display the loan schedule?
Many Many Thanks