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!

Sub LabelsPlay()
    
    With ActiveSheet.ChartObjects(1).Chart
        Set ValAx = .Axes(xlValue).AxisTitle
        Set CatAx = .Axes(xlCategory).AxisTitle
        Set Leg = .Legend
        Set ChTitle = .ChartTitle
        Set SerLabs = .SeriesCollection(1).DataLabels
    End With
    ValAx.Left = ValAx.Left + 5
    ValAx.Top = ValAx.Top + 5
    CatAx.Left = CatAx.Left + 5
    CatAx.Top = CatAx.Top + 5
    Leg.Top = Leg.Top + 5
    Leg.Left = Leg.Left + 5
    ChTitle.Top = ChTitle.Top + 5
    ChTitle.Left = ChTitle.Left + 5
    'The series data labels have to be dealt with individually
    For Each lbl In SerLabs
        lbl.Left = lbl.Left + 5
        lbl.Top = lbl.Top + 5
    Next lbl
    
End Sub

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: Excel chart data label properties/methods 2003 VBA

Hmmm. According to MS's official documentation webpage, the DataLabel object does have both Top and Left properties. They are defined in relation to the chart area. Perhaps you were looking at the DataLabels collection documentation instead?

http://msdn.microsoft.com/en-us/library/office/aa196429%28v=office.11%29.aspx

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:
=ROUNDUP(MAX(0,(MIN(B2,D2)-MAX(A2,C2)))/30,0)

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: Find a value in a range and return a value in the same row in a different sheet

A combination of MATCH and INDEX functions should do the trick. Try putting this in cell B1 of email sheet and copying down the column:

=IFERROR(INDEX(data!A:A,IFERROR(MATCH(A1,data!V:V,0),MATCH(A1,data!AB:AB,0))),"NO MATCH")

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: Email Address Validation

Gosh, after posting it so long ago, it's nice someone finally got some good use out of 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):

Sub findHello()
    Dim inArr, outArr
    Dim r As Long, c As Long, i As Long, n As Long
    
    inArr = Range("A1:O25")
    ReDim outArr(1 To UBound(inArr), 1 To 15)
    For r = 1 To UBound(inArr)
        For c = 1 To 15
            If InStr(1, inArr(r, c), "hello") > 0 Then
                i = i + 1
                For n = 1 To 15
                    outArr(i, n) = inArr(r, n)
                Next n
                Exit For
            End If
        Next c
    Next r
        
    Sheets("Output").Range("A1:O" & i) = outArr
    
End Sub

Re: Shortening Function

This is a bit shorter:
=VLOOKUP(M2,Customer_Discount,7+MATCH(E7,{0,5,11,25,50,99},1),FALSE)*L7

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.