Posts by GuyGadois

  • Macro changing values of various cells

    Re: Macro changing values of various cells


    "You state that the macro is supposed to only affect only the cells in the active row but your example says the active cell is A20 but you're pulling dat from row 17?????"


    I meant Column 17 - and the row is where MOST of the data change happens. It actually changes a few different cells around. When I start the macro it can be from any row from 1 to 100. Various cells on that row are changed plus a few cells that are fixed (meaning no matter where I start the macro from the fixed cells will always be changed).


    How do I post the workbook?

  • Using if/then to refer to same macro ..?

    I have the following code that looks for a blank cell in a specific set of cells then picks the cell under it and proceeds with a macro. Note that I have multiple if/then statements. I would like to have each result of the if/then to be picking the right blank cell then proceeding with the pastes but I don't want to have to past the guts of the macro under each if/then. Is there a way to make each if/then refer to the guts of macro so I don't have to duplicate the exact macro. Damn I'm confused... Check out my notes.


    Help.


    Guy



  • Macro changing values of various cells

    Let me start off by saying I am trying to self learn VB for excel (read, I am an idiot). Ok, now, I have a spreadsheet where the macro is supposed to change a bunch of values and clear a bunch of cells based on what is entered in a form. Here is the following code I have written (hacked)



    in this area I am trying to take existing numbers and update them from numbers on the form but it isn't working:



    ActiveCell.Offset(ColumnOffset:=16).Select
    ActiveCell = portallocation
    ActiveCell.Offset(ColumnOffset:=7).Select
    ActiveCell = securityvalue
    cashallocation = Range("Q158").Select
    cashvalue = Range("V158").Select


    'Adjusting Cash in Basis
    Range("V158").Value = cashvalue + (securityvalue * TextBox3.Value)


    'Adjusting Portfolio Allocation
    Range("Q53").Value = cashallocation - ((TextBox3.Value / 100) * Range("Q53").Value)


    'Adjusting Cash Allocation
    Range("Q158").Value = portallocation + ((TextBox3.Value / 100) * Range("Q53").Value)



    Note that what I am trying to do is start the macro from any row in column A. This macro can be started from A1 to A100 and it is supposed to effect only cells in that row. So if I started this macro on A20 I would want this code to go over to A17 and make a variable called 'portallocation'=Q17's current value. What am I doing wrong?


    ActiveCell.Offset(ColumnOffset:=16).Select
    ActiveCell = portallocation


    The same with this next code which I want it to go over seven more columns and make the variable 'securityvalue'=X17


    ActiveCell.Offset(ColumnOffset:=7).Select
    ActiveCell = securityvalue


    I think there are many other problems with this macro as it keeps getting jammed.


    Any advice is much appreciated.


    Guy

  • Comparing Dates using if then

    I am looking to add an if/then to the begining of a script I have that will basically look at todays's date and will run the script only if this date is the day after a date that is contained in cell A1 on my spread sheet Worksheet "Mainsheet". How would I right this? Is this possible?


    Much appreciated,


    Guy

  • Inserting Current Date Into VB Form

    Re: Inserting Current Date Into VB Form


    Worked like a charm, thanks!