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

    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

    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

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

    Any help on the right command to use?


    Re: Programming dynamic sumproduct formula in VBA

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

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


    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


    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


    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?


    Sub TIR()
        SolverOk SetCell:="$M$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$44"
    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?


    Re: Count Rows in Selection

    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

    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


    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

    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)

    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?

    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