Need Excel VBA Guidance

Question

The issue I am looking to solve: I want to look at one column which is automatically populated with a due date. My organization wants to enforce an In-House due date of two business days (M-Th) prior. To automatically populate the In-House due date with the 2 days prior according to day in the week the actual due date falls on, I have written this Sub:

Sub In_House()

Dim In_HouseDue
Dim CurrentCell

CurrentCell = Range("D3").Value

Select Case CurrentCell

Case Weekday(CurrentCell) = 1
In_HouseDue.Value = DateAdd("d", 5, CurrentCell)
     ActiveCell.Offset(1, 0).Select

Case Weekday(CurrentCell) = 2
   In_HouseDue.Value = DateAdd("d", 5, CurrentCell)
 ActiveCell.Offset(1, 0).Select

Case Weekday(CurrentCell) = 3
In_HouseDue.Value = DateAdd("d", 2, CurrentCell)
 ActiveCell.Offset(1, 0).Select
Case Weekday(CurrentCell) = 4
In_HouseDue.Value = DateAdd("d", 2, CurrentCell)
 ActiveCell.Offset(1, 0).Select
Case Weekday(CurrentCell) = 5
In_HouseDue.Value = DateAdd("d", 2, CurrentCell)
 ActiveCell.Offset(1, 0).Select
Case Weekday(CurrentCell) = 6
In_HouseDue.Value = DateAdd("d", 3, CurrentCell)
 ActiveCell.Offset(1, 0).Select
Case Weekday(CurrentCell) = 7
In_HouseDue.Value = DateAdd("d", 4, CurrentCell)
 ActiveCell.Offset(1, 0).Select

In_HouseDue = Range("E3").Value

End Select

End Sub

It runs without any error, but it does not actually populate the In-House due date column that starts with Cell "E3"

Any help would be great!

This really runs without error? YOu haven't Set In_HouseDue as a range variable, and Variant types do not have a .Value property. — David Zemens
– David Zemens, Commented May 22, 2013 at 19:42
@David Zemens - note that each Case expression resolves to a Boolean - hence none of the In_HouseDue.Value are evaluated at run time. — rskar
– rskar, Commented May 22, 2013 at 19:48
@rskar yep, I eventually did notice that, hence my answer below :) good catch! — David Zemens
– David Zemens, Commented May 22, 2013 at 20:00

rskar · Accepted Answer · 2013-05-22 19:41:14Z

2

In addition to what PowerUser says, redo your Select Case block, like so:

Select Case Weekday(CurrentCell)

Case 1
In_HouseDue = DateAdd("d", 5, CurrentCell)
     ActiveCell.Offset(1, 0).Select

Case 2
   In_HouseDue = DateAdd("d", 5, CurrentCell)
 ActiveCell.Offset(1, 0).Select

etc.

answered May 22, 2013 at 19:41

rskar

4,67727 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PowerUser · Accepted Answer · 2013-05-22 19:32:28Z

1

I think that last line should be the other way around. Try

Range("E3").Value=In_HouseDue

answered May 22, 2013 at 19:32

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

3 Comments

rskar Over a year ago

It should also be after the End Select. And actually, the Select Case block is all wrong.

PowerUser Over a year ago

@rskar, I agree. Missed that.

David Zemens Over a year ago

Also, what is In_HouseDue? It's declared as Variant type, but syntax is refering to it like a Range (e.g., InHouse_Due.Value), yet I don't see it assigned with a Set InHouse_Due anywhere in this code.

David Zemens · Accepted Answer · 2013-05-22 19:46:14Z

This will work. Your Select Case statement was wrong, also your variables were improperly declared, which will often cause errors, or otherwise undesired results.

Sub In_House()

Dim In_HouseDue As Date
Dim CurrentCell As Date

CurrentCell = Range("D3").Value

    Select Case Weekday(CurrentCell)

        Case 1
            In_HouseDue = DateAdd("d", 5, CurrentCell)
             ActiveCell.Offset(1, 0).Select

        Case 2
           In_HouseDue = DateAdd("d", 5, CurrentCell)
            ActiveCell.Offset(1, 0).Select

        Case 3
            In_HouseDue = DateAdd("d", 2, CurrentCell)
             ActiveCell.Offset(1, 0).Select
        Case 4
            In_HouseDue = DateAdd("d", 2, CurrentCell)
             ActiveCell.Offset(1, 0).Select
        Case 5
            In_HouseDue = DateAdd("d", 2, CurrentCell)
            ActiveCell.Offset(1, 0).Select
        Case 6
            In_HouseDue = DateAdd("d", 3, CurrentCell)
             ActiveCell.Offset(1, 0).Select
        Case 7
            In_HouseDue = DateAdd("d", 4, CurrentCell)
             ActiveCell.Offset(1, 0).Select

    End Select

    Range("E3").Value = In_HouseDue

End Sub

Collectives™ on Stack Overflow

Need Excel VBA Guidance

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related