Date Issue in excel vba

[phong.lee]

Hello all,

i'm having an issue with a date variable in excel vba that i can't
figure out. I hope someone can tell me what's wrong and how come it's
happening.

On one of my spread sheet i have 2 dates. A start date and a end
date. I did some code by creating a module in vb.

DIM Dt1 as date
DIM Dt2 as date

The problem is that when i look at what the value of dt1 and dt2 in
worksheet1, it only shows 12:00am. is there some way that i need to
specified to show the exact date on my sheet?

 

[JW]

You are just declaring the variable. You need to assign a value to
the variable.
Sub foo2()
Dim Dt1 As Date
Dim Dt2 As Date
Dt1 = Date
Dt2 = Date + 5
MsgBox Dt1 & Chr(10) & Dt2
End Sub

 

[phong.lee]

You are just declaring the variable. You need to assign a value to
the variable.
Sub foo2()
Dim Dt1 As Date
Dim Dt2 As Date
Dt1 = Date
Dt2 = Date + 5
MsgBox Dt1 & Chr(10) & Dt2
End Sub









- Show quoted text -

What if i have 2 dates already in another sheet and wanted to get the
value from the sheet?

 

[JW]

Sub foo2()
Dim Dt1 As String
Dim Dt2 As String
Dt1 = Sheets("Sheet1").Range("C15").Text
Dt2 = Sheets("Sheet1").Range("C16").Text
MsgBox Dt1 & Chr(10) & Dt2
End Sub

 

[phong.lee]

Sub foo2()
Dim Dt1 As String
Dim Dt2 As String
Dt1 = Sheets("Sheet1").Range("C15").Text
Dt2 = Sheets("Sheet1").Range("C16").Text
MsgBox Dt1 & Chr(10) & Dt2
End Sub






- Show quoted text -

Thanks JW.

Can you explain the following 2 lines? I'm looking at a process that
was written by someone else and didn't know what these 2 line was
doing.

res = Application.Match(CLng(dt1), r, 0)
res1 = Application.Match(CLng(dt2), r, 0)

res variable returns a 2042 error.

 

[JW]

Without seeing the whole code and what the variables are assigned,
it's tough to determiine what the variables should return.
The res variables are using a Match function to return the position
within the r array where the dt1 variable first occurs. I know that
sounds a little complex, but that's what it is doing. If you are
receiving an error, it's probably because the Match function can not
find a match to dt1 within the r array.
Application.Match(CLng(dt1), r, 0)

 

[phong.lee]

Without seeing the whole code and what the variables are assigned,
it's tough to determiine what the variables should return.
The res variables are using a Match function to return the position
within the r array where the dt1 variable first occurs. I know that
sounds a little complex, but that's what it is doing. If you are
receiving an error, it's probably because the Match function can not
find a match to dt1 within the r array.
Application.Match(CLng(dt1), r, 0)









- Show quoted text -

Sub BuildColors()
Dim sh As Worksheet, r As range
Dim rng As range, cell As range
Dim dt1 As Date, dt2 As Date
Dim stphase As String
Dim stgroup As String
Dim stdescr As String
Dim res, res1


Set sh = Worksheets("Sheet2")
'sh.Cells.Interior.ColorIndex = xlNone
Set r = sh.range(sh.range("A1"), sh.range("B1").End(xlToRight))
With Worksheets("sheet1")
Set rng = .range(.range("A2"), .range("A2").End(xlDown))
For Each cell In rng
dt1 = .Cells(cell.Row, 1)
dt2 = .Cells(cell.Row, 2)
res = Application.Match(CLng(dt1), r, 0)
res1 = Application.Match(CLng(dt2), r, 0)
If Not IsError(res) And Not IsError(res1) Then
sh.range(sh.Cells(cell.Row, res), sh.Cells(cell.Row, res1)) _
.Interior.ColorIndex = 45
sh.Cells(cell.Row, 1).Value = cell.Offset(0, 2).Value _
& " (" & cell.Offset(0, 3).Value & ")"
End If
Next cell
End With


End Sub

Here is the sample of the code.

 

[JW]

Nothing immediately jumps out at me. To really give suggestions, I
would need to see the spreadsheet itself and need to know what the
expected macro return is.

 

[phong.lee]

Nothing immediately jumps out at me. To really give suggestions, I
would need to see the spreadsheet itself and need to know what the
expected macro return is.









- Show quoted text -

Here's what my spreadsheet looks like.

Sheet 1 contains 5 columns
Column A- Start Date
Column B- End Date
Column C- Description
Column D- Group
Column E- Phase

Sheet 2 contains 4 columns
Column A - Week 1
Column B - Week 2
Column C - Week 3
Column D - Week 4

So what should happen is:

If start date and end date is 9/3/07 and end date is 9/7/07 and phase
is blue, then in sheet 2 should fill the color blue because that's the
first week in september. If say the dates are 9/3/07-9/17/07 and the
phase is blue, then sheet 2 should fill all of week1 and week2 and a
little bit of week 3 because it's 2 weeks and a day.

Does that make sense to you?

 

[phong.lee]

Nothing immediately jumps out at me. To really give suggestions, I
would need to see the spreadsheet itself and need to know what the
expected macro return is.









- Show quoted text -

Can you assist me ? THanks.