Posts by Nacho top

Hi there, it's been a while without posting or helping. I have tried may ways to solve this but it defeated me.

I am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has.
Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be
Cell B1: "sum(A1:A5)"
Cell B2: "sum(A2:A5)"
Cell B3: "sum(A3:A5)"... and so forth

Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client.

My proposal is (adding from the last installment to the first one):

months = 4 'an example
    For j = 0 To months - 1
        initial_row = ActiveCell.Row  'because I run this several times. In this example I am in row 18
        ActiveCell.FormulaR1C1 = "=SUM(AJ" & initial_row & ":AJ" & initial_row + j & ")" 'I expect to have SUM(AJ18:AJ18), SUM(AJ17:AJ18), SUM(AJ16:AJ18)...
        ActiveCell.Offset(-1, 0).Select
    Next

For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.

Could anyone tell me what may be wrong in this code. If you have an alterative solution, it is more than welcome

Re: Change line width in chart

Ha ha thanks Rob
Eventhough I was thinking in a more sophisticated and elegant solution, this one works just perfect, it is all what I was looking for (and it is also programable in VBA).

Thanks and regards
Nacho

Hi
Excel 2007 default line chart has lines with 2,25 points width and I normally use 1.0 points width.

is it possible to change this?. I have two ideas.

1) change a default setting (which I think it's complicated)
2) run a macro that modifies every line width. I tried the following

ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select

but then I don't know how to change the line width.

Any help on the right command to use?

Thanks
Nacho

Re: Convert all pivot tables to values

I did that in a primitive way once.
I just copied the entire table a pasted it as value and then format.
It may not be what you were looking for... but it works

Regards
Nacho

Re: Programming dynamic sumproduct formula in VBA

Barb-B
I can't thank you enough for your really useful help. It worked just perfect and I learned a new tool, thanks.

Mikerickson
Thanks for your attempt. It is long to explain but trust me, I need it to by dynamic. Thanks anyway

Nacho

Hi
I am trying to write a dynamic sumproduct formula so the routine I program, should be able to write this formula in Excel every time I need it, using the correct amount of arguments.
I will read the amount of arguments every time because they change daily.
I tried the “record macro” and I got this:

ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R[-1]C[-9]:R[-1]C[-1],RC[-9]:RC[-1])"

What I need is that “9” to be a dynamic value.
I tried

columns = 9
	ActiveCell.FormulaR1C1 = "=SUMPRODUCT(R[-1]C[-columns]:R[-1]C[-1],RC[-columns]:RC[-1])"

Also tried

base = Selection.Address
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(base,RC[-9]:RC[-1])"

Any suggestion how to solve this?
Thanks in advance

Nacho

Re: Solver Macro Not Running

Hello Dave
this is a set of equations that needs solver. I recordered the macro setting the problem for the solver. I put the variables, boundary conditions, everything, and then I stoped recording the macro.
After doing that, when I run "solver" again, my equations are ready to run, I run solver and I find the solution. I can do that over and over. But if I go to Visual Basic, and press F8 to run the code step by step, it appears a problem. Exactly which is the problem, I am not sure. Actually my friend did it and I am helping him, but I saw it and it said something like "... problem, debug now?" and the step by step does not run.
we are doing this because we want to link the macro to a button, to run it everytime we need for a set of Excel sheet we have.
Hope this information helps

Best Regards

Saludos
Nacho

Hi
I have a proyect evaluation. I use solver to find a value, which works. I recorded a macro for this situation (which repeats all the time), but when I run it, it does not work. The problem is not the answer, it is that the macro does not run
any suggestion?

Regards
Nacho

Sub TIR()
    SolverOk SetCell:="$M$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$44"
    SolverSolve
    Range("E44").Select
End Sub

Hello Y'all
In need to daily update a file using a macro that read data from the web. How can I set up excel to update every monday through friday at 8:45 AM?

Saludos
Nacho

Re: Count Rows in Selection

Hello
maybe "a lot of my thread" is too much. probably you are talking about the last one where I put a space in the word "how". Obviously I will ready the rules again, because I wanna stay here asking and helping as often as I can, and I don't wanna bother you or other members
Saludos
Ignacio

Re: H ow many rows?

It worker just perfect
thank you
Saludos
Nacho

Hello y'all
I need to know how many rows are there in a selection, because I want to do a loop at every one of them

I tried selection.rows, selection.row, selection.height, selection.length
and guess what, it didn't work

Saludos
Nacho

Re: to write formula

That's exactly what I needed, Thank you so much
Saludos
Ignacio

Re: to write formula

Sorry, you're right. What I didn't mention is that I want to program it because me and two workmates have to do it from a list of at least 50 prices, twice a day, every day. That's why I'm trying to program a macro where I just run it, and automatically does it for me
Saludos
Nacho

Re: to write formula

Yes because it is a currency change, and the person who will receive the file, needs to know which was the original amount, and which is the change rate
(that's why I need to write the formula rather than only write the result)
Saludos
Nacho

Hello y'all
In a cell I have a value, and I need to write a formula with it, not rowrite only the result (i.e. if I have 150, and my formula is 2 times that value, I need to write =150*2, not just 300). ¿any suggestion?

PD: I added a file to example it clearly

Saludos
Nacho

Re: at which column am I?

this forum is just awesom, thankyou spog00, it worked just perfect
Nacho

Hello y'all
is it possible to select the current column?. I mean, if I am placed at cell K34, is there any way to say Column("K:K").select , or something like columns(activecolumn).select?
Regards
Nacho

Re: Sum ( offset )

Thank you, it is not exactly what I wanted but it is a good solution which I will addopt
Nacho

Hello guys
I have a cost problem. I have a lot of different "families". Each family has their own materials (different amount from one family to another). I would like to write a function in excel to sum, for each family, their material cost. I prefer excel rather than VB because in VB I should run the macro any time I had to refresh my data. Could anyone help me?
I added an example to show you clearly what I need. The example is a thousand times easiest than understand it by reading it
Saludos a todos
Nacho