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?

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
wrote:
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?

On Sep 10, 2:01 pm, JW wrote:
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



wrote:
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?- Hide quoted text -

- Show quoted text -

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

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
wrote:
On Sep 10, 2:01 pm, JW wrote:
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



wrote:
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?- Hide quoted text -

- Show quoted text -

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

On Sep 10, 2:14 pm, JW wrote:
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



wrote:
On Sep 10, 2:01 pm, JW wrote:
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

wrote:
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?- Hide quoted text -

- Show quoted text -

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

- 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.

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)
wrote:
On Sep 10, 2:14 pm, JW wrote:
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



wrote:
On Sep 10, 2:01 pm, JW wrote:
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

wrote:
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?- Hide quoted text -

- Show quoted text -

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

- 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.

On Sep 10, 2:22 pm, JW wrote:
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)



wrote:
On Sep 10, 2:14 pm, JW wrote:
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

wrote:
On Sep 10, 2:01 pm, JW wrote:
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

wrote:
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?- Hide quoted text -

- Show quoted text -

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

- 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.- Hide quoted text -

- 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.

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.
wrote:
On Sep 10, 2:22 pm, JW wrote:
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)



wrote:
On Sep 10, 2:14 pm, JW wrote:
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

wrote:
On Sep 10, 2:01 pm, JW wrote:
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

wrote:
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?- Hide quoted text -

- Show quoted text -

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

- 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.- Hide quoted text -

- 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.

On Sep 10, 2:46 pm, JW wrote:
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.



wrote:
On Sep 10, 2:22 pm, JW wrote:
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)

wrote:
On Sep 10, 2:14 pm, JW wrote:
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

wrote:
On Sep 10, 2:01 pm, JW wrote:
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

wrote:
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?- Hide quoted text -

- Show quoted text -

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

- 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.- Hide quoted text -

- 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.- Hide quoted text -

- 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?

On Sep 10, 2:46 pm, JW wrote:
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.



wrote:
On Sep 10, 2:22 pm, JW wrote:
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)

wrote:
On Sep 10, 2:14 pm, JW wrote:
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

wrote:
On Sep 10, 2:01 pm, JW wrote:
You are just declaring the variable. You need to assign a value to
the variable.
Sub foo2()
Dim Dt1 AsDate
Dim Dt2 AsDate
Dt1 =Date
Dt2 =Date+ 5
MsgBox Dt1 & Chr(10) & Dt2
End Sub

wrote:
Hello all,

i'm having an issue with adatevariable 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 startdateand a end
date. I did some code by creating a module in vb.

DIM Dt1 asdate
DIM Dt2 asdate

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 exactdateon my sheet?- Hide quoted text -

- Show quoted text -

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

- 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.- Hide quoted text -

- Show quoted text -

Sub BuildColors()
Dim sh As Worksheet, r As range
Dim rng As range, cell As range
Dim dt1 AsDate, dt2 AsDate
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.- Hide quoted text -

- Show quoted text -

Can you assist me ? THanks.