Posts by sjboyd

    I am trying to create a VBA script that I can run anytime the task needs done. I need it to find one value within a specific range, and replace it with a forumla.


    I thought I'd gotten it to work, but it appears it will only run the first time I execute the macro, even if the next time I execute it more values exist that should be replaced.


    Can you help?


    Here is what I have so far. (hyperlink URL has been changed):


    Code
    1. Sub ReplaceX()
    2. Range("P5:AC39").Replace What:="Y", Replacement:="=HYPERLINK(""http://www.example.com"",""X"")", LookAt:=xlPart, _
    3. SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    4. ReplaceFormat:=False
    5. End Sub

    I am using the stdev() formula in my spreadsheet that looks back over the last 53 periods. This spreadsheet is updated daily and reflect both the last 53 days, last 53 weeks, and last 53 equivalent days (i.e. Tuesdays) over the last 53 weeks. I want the standard deviation of sales during that time. However, we didn't start tracking some of that information an entire 53 weeks ago, so I have a lot of zero values that are skewing the results. Is there a way to get my standard deviation calculation to ignore zeros? They would seem more important if they actually fall within regular data, but prior to the start date of when we began capturing, the zeros are irrelevant. But as this spreadsheet is auto-updated daily, I need the formula to correctly identify when it should ignore zeros or not.


    Any suggestions?

    Re: Reference Cell Based On Button Location


    Now I'm getting a different error: "Run-time error:9. Subcript out of range"


    Re: Reference Cell Based On Button Location


    Something continues to not seem to work though. I used a portion of your code (WITH statement and the TopLeftCell Reference), yet I get a "Run-time error: 424 Object Required" error. I'm not understanding that is wrong.


    Re: Reference Cell Based On Button Location


    To be honest, that really isn't enough to get me going. I need to be able to use code to actually pull the contents from a specific cell. All I got from your posts was a reference to the row. How can I reference the row and column? And I don't need it in a message box.


    Thanks!

    I received this code from an example I found once upon a time that was originally submitted by someone else.


    Right now the code enters the current date in a cell of the same row as the checkbox when it's checked.


    I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro.


    First of all, I don't know how to reference a Forms button in VBA.


    Here is the code I found:



    Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:



    I appreciate any help you can provide.

    Re: Vba Book Suggestion


    Great, thanks guys!


    And just for clarification, most of these lines of books should have one relevant for Excel 2003 (which is what I'm using), correct?

    I am somewhat new to VBA. I have culled some information here and there from this forum. It has given me a general beginning understanding of a few vba functions. However, I would like to purchase a VBA book that can possibly do 2 things:


    1) Walk me through step-by-step how to use and implement some common and helpful VBA functions. (I am a quick learner, so it doesn't have to be too basic, but not too advanced in that it doesn't actually tell me how to implement anything).


    2) Serve well as a reference book (not just a tutorial).


    Anyone have any suggestions?

    Re: Automatically Format Cell Based On Value


    Thanks, AAE. Also, sorry about being labeled as Expert. I don't remember selecting that when I'd signed up, b/c am certainly not an Expert. Still learning a lot about VBA.


    Actually, one more question.


    Is there a variation on the SelectionChange that I can use, so it will only activate when the values in those specific cells actually change? Otherwise everytime I even just click on another cell in my workbook, the screen flashes while the macros rerun. And I am using this same macro on several worksheets within the workbook, so it ends up flashing for quite a while.


    Any ideas?

    I need help writing VBA code (and guidance on where I should put it) that will enable me to change the format of text within a cell when the value of that cell changes. This code will need to look at each cell within a range and make the appropriate chance to EACH cell.


    Note that this value will not be changed by the user. It is a formula based cell and is therefore contingent on the values in other cells to affect it.


    I can't use conditional formatting because i have more than 3 criteria.


    Here is a basic description of my need:


    If any cell within a specified range has the value "A", it should be Bold, Red (255,0,0), and size 13 font.
    If any cell within the same range has a value of "B", it should be red (i don't know the RGB code), and size 10 font.
    If any cell within the same range has a value of "C", the text should be green and size 10 font.
    If any cell within the same range has a value of "C", the text should be Bold,green, and size 13 font.


    Can anyone help?


    Thanks!