Posts by Nutsy524

    Re: VBA Date Comparison

    Yes, X is valid, the problem is with the information in either of the date columns - At times people will enter TBD instead of a date.

    Re: VBA Date Comparison

    Ok, so it errors out when there are blanks or if somebody puts in something like TBD. Would On error resume next work to force it to the next line?

    Multiple users update a workbook that contains various information, two of the items are "Original Completion Date" and "Expected Completion Date".

    One of the functions of the code I am writing is to compare these two dates, and depending on the difference set a trend indicator in a new cell. For instance, if the Expected Completion Date is within 2 weeks of Original Completion Date it would turn the cell green. If it were more than 4 weeks it would turn it red.

    This is the code that I have:

    If (Format(Cells(x, 19), "ww") - Format(Cells(x, 22), "ww")) <= 2 Then 
        Cells(x, 16) = "G"
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If

    The dates are entered in the DD-Mmm-YY format. I figured that converting the dates to the week number and finding their difference would allow the comparison. But I get a type mismatch on the If command line.


    Re: Assigning Values in Array, VBA

    It works, and thank you.

    But I hate zero based - it makes things like figuring out row and column numbers difficult. Why can't the y=1 remain and the ReportInfo(0,0) just be empty?

    So, in other words, I redimed the array to TotalRows, but that number includes the 0 row. But I'm not using that, I'm starting at row 1 and going TotalRows more, which is out of it's range, thus the error. So what I really need to do is redim to TotalRows+1 then the y+1 could stay. Correct?

    First time posting so I will apologize up front for not following conventions or doing something incorrectly.

    The below code is to look at a table exported from another program and load the information that it needs into an array for reformatting/use later.

    But when I try to execute I get the subscript out of range error the first time I try to load in a value (ReportInfo(y, 1) = Cells(x, 10) ' Activity). I don't understand why as I've done this in other macros and it has worked. Am I just missing something?

    I'd like to understand why it is happening so I don't need to ask again, as well as help with the code. But please keep something in mind - I'm not a programmer (as I am entirely self taught); I've seen some pretty cool programming that does things more efficiently than I ever could, but I don't have the experience/knowledge base to follow. And I'm getting old. So if you could tell me what is wrong with MY code I would appreciate it.