Posts by Barb-B

    Re: Coding a simple IF formula in VBA

    I'm sorry - I couldn't follow what you said there. It sounds like you may be trying to force that single cell to perform in ways that should maybe be split into separate cells. The sample formula you show will return either TRUE or FALSE. I don't guess that's what you're trying to achieve.

    Re: Excel chart data label properties/methods 2003 VBA

    Hi Bill,
    If I understand your problem statement correctly, you have a single cell named range, "HIGHEST_POINT". And you want to store it's value in the variable "high_point". Here's the line of code that will do it:

    high_point = Range("HIGHEST_POINT")

    Note: this line only works for a single cell named range. A range with multiple cells would throw an error. For multi-cell ranges, you would need to loop through the individual cells, or refer to them somehow by their relative location within the range.

    The easiest way to see the values of your variables while your code is executing is with the Locals Window. You can open it from the VBE View menu. There are some other excellent tools for evaluation and experimentation in the Immediate Window and the Watch Window. If you're interested in exploring those, a google search using those terms with "VBE" turns up some great articles that would make it silly for me to go into more detail here.

    Lastly, a friendly caution. The moderators on this forum are seriously sticklery about the forum rules. And technically you maybe should have started a new thread for questions unrelated to the topic of your initial question. For next time perhaps. Meanwhile, have fun adventures with your VBA!

    Re: Excel chart data label properties/methods 2003 VBA

    I included some of the other label objects found on a chart as well. Didn't take time to properly dim variables, but recommend it for your finished work. Have fun!

    Re: Run a macro in three or more sheets at the same time

    Hi Aysam,
    Please excuse if anything I'll say here is completely obvious. Not knowing if that's code you wrote or obtained from someone else, I can't estimate your level of knowledge. Anyhow, you would need to dim the "sht" variable as a worksheet object, since you have Option Explicit specified. Also of course you've replaced my dummy sheet names with the ones from your file that you want to work with...

    I tested on my machine without the Option Explicit, and it worked just fine.

    Re: Excel chart data label properties/methods 2003 VBA

    Well maybe I'm too fond of keeping things simple. If it were my project, I don't think I'd mess around figuring out how to make new methods, when there's already a way to get the task done. The Top and Left properties of the DataLabel object can be both read and written to - so can be used to move your labels where you want them. I ran a quick test to confirm that's so.

    Re: Checking for successful save in 2007

    I don't have a sharepoint environment to experiment with to confirm. But I would think you could make use of the Workbook BuiltInDocumentProperties to test the "Last save time" property - perhaps before and after, to determine if the value has changed. e.g.:

    time1 = ActiveWorkbook.BuiltinDocumentProperties("Last save time")
        'doing stuff...
        If ActiveWorkbook.BuiltinDocumentProperties("Last save time") = time1 Then
        'doing more stuff

    Re: Count Number of Months in two overlapping date rages

    Sorry, I should have looked at your attachment before replying. If I correctly understand your sample of manually supplied answers, it is the number of days overlap, divided by 30, then rounded up. For that result, put this formula in cell F2, and copy down:

    Re: Run a macro in three or more sheets at the same time

    Or, if you want to work only with some sheets in your workbook, and not others you could do something like this:

    For Each sht in Array(Sheets("Sheet1"), Sheets("Sheet3"), Sheets("fubar"))
            sht.Range("a1") = "Put something here"
        Next sht

    Re: Count Number of Months in two overlapping date rages

    Spegargi, I think to accurately answer your question, we'd need to know a little more specifically what your criteria would be for counting a "month". Is it a period of 30 days? Or would you count a month in the overlap if there were any days within the calendar month that were included? Examples: Defined the first way, 3/10/2013 to 5/10/2013 would be two months. But defined the second way it would be 3, since it would have days falling in 3 separate calendar months. Further, if it is based on any 30 day span, how would you want to handle rounding?

    Re: Coding a simple IF formula in VBA

    When composing formulas that need to include an empty quote string, I find it easier to use chr(34), rather than trying to remember how many will give me the right result. However, assuming you've gotten the quotes right, you also need to either close your second right-parentheses or get rid of the second left one. The formula will work just fine without enclosing the division operations. I'd do it like this:

    Range("H8").Formula = "=IF(L8=0," & chr(34) & chr(34) & ",L27/L8/L5)"

    Re: Add data from userform to specific cells on worksheet

    When you say you want to "add" to column 13 on the same row, does that mean there is already a value in that cell, and you want to add the textbox input to it? Or are you overwriting whatever was in the cell before. If you are "adding", will it be numeric values to be summed? or text values to be concatenated?

    Also always a consideration when I build forms: can the user(s) of the form be counted on to provide accurate/valid entries? Or is it necessary to build in some testing/scrubbing type code?

    Re: Using Intersect and Target

    First thing is you need to disable events before executing anything that makes further changes. Otherwise you end up in an uncontrollable loop. So, something like this:

    If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Not Intersect(Target, Range("B6:B16")) Is Nothing Then 
            r = Target.Row + 1 
            Range("B" & r & ":" & "B16").ClearContents 
        End If
        Application.EnableEvents = True
    End If

    The test for count of cells in target is to prevent unintended results if users perform a paste operation with more than one cell. But you could take it out and come up with some other way to deal with that circumstance.

    Re: Macro "Range" Modification

    I don't believe you need to loop through the cells at all. Just select the whole column and perform the same text-to-columns operation on it.

    Re: Relating a third column to a countif formula

    If you're familiar with helpfile descriptions of functions and their arguments, you can likely get where you're trying to go by reading the entry on the COUNTIFS function. It's new in XL 2007, which your attachment filetype indicates you have. It is like the COUNTIF function, except that it allows you to specify multiple criteria in different columns.

    Re: Excel 2003 - Array Processing

    Jindon is likely correct that filtering is the optimal solution, as long as a specific value to filter by is within one column. If you need to filter for the value to be in any one of several columns, then an "in" array and and "out" array may work best. Something like this (my example is for only 25 rows, and outputs to a sheet at the end - but it demonstrates the idea anyhow):

    Re: Reference a combobox in a sheet

    Your combo box has a linked cell property. Populate that property, and then make your vlookup formula reference the cell address you've put there. You should be good to go.