Posts by Nutsy524

Re: VBA Date Comparison

Perfect! Genius! Thank you so much!

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?

Re: VBA Date Comparison

Tried it, I get Run time error 13, Type Mismatch on your second 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.

Suggestions?

Re: Assigning Values in Array, VBA

Ok, now I know. I will use the Option Base 1. That makes sense to me.

Thank you everybody for you time, patience, and help.

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?

Re: Assigning Values in Array, VBA

Let's see if I attached a sample file correctly.

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.

Sub Hours()
'
' Project Hours Report from PMIS
'
Dim a, b, c, x, y, z, TotalRows
Dim ReportInfo() As Variant
x = 6
TotalRows = 0
Do While Cells(x, 2) <> ""
TotalRows = TotalRows + 1
x = x + 1
Loop
TotalRows = TotalRows - 1
ReDim ReportInfo(TotalRows, 3)


y = 1


For x = 6 To TotalRows + 6
    ReportInfo(y, 1) = Cells(x, 10) ' Activity
    ReportInfo(y, 2) = Cells(x, 2) ' Name
    ReportInfo(y, 3) = Cells(x, 6) ' Hours
    y = y + 1
Next x
'
End Sub

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.