weekday loop macro

[xelhelp]

Hi,
i have a bunch of dates in column B that are populated from an external feed into excel. the dates are populated starting at cell B5 and going down X number of rows...X because the dates could go down 5 rows or 50 depending on what the external feed populates into excel, but never over 200

here is my code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowCount As Double
RowCount = Application.WorksheetFunction.CountA(Range("B5:B200"))

Dim Counter As Double
Counter = 0

Do Until Counter = RowCount

If Weekday(Range("B5").Offset(Counter, 0)) = 1 Then Range("B5").Offset(Counter, 3) = Range("B5").Offset(Counter, 0) + 1
If Weekday(Range("B5").Offset(Counter, 0)) = 7 Then Range("B5").Offset(Counter, 3) = Range("B5").Offset(Counter, 0) + 2
If Weekday(Range("B5").Offset(Counter, 0)) > 1 And Weekday(Range("B5").Offset(Counter, 0)) < 7 Then Range("B5").Offset(Counter, 3) = Range("B5").Offset(Counter, 0)
Counter = Counter + 1
Loop


End Sub

when i change a cell in the sheet it seems like it just keeps running the macro, i have to force it to shut down by pressing the Esc key....

this usually ends up with excel crashing

any input would be appreciated

thanks

 

[Blade Hunter]

Do Until Counter = RowCount

Change it to

Do Until Counter > RowCount-1

Your code has the potential for Counter to increase by 2 giving it the opportunity to skip Counter and thus not equal it.

Also you need to deactivate the events:

Application.enableevents = false at the start and back to true at the end.

Otherwise everytime the code makes a change it refires the macro

 

[xelhelp]

thanks for the quick response, really appreciate it!

 

[xelhelp]

i have other code that i want to be executed on this same sheet if any value on the sheet is changed....do i just continue writing code above the "End Sub" statement or do i start a new

Private Sub Worksheet_Change(ByVal Target As Range) ?

when using the worksheet change event handler, is this basically my Sub procedure? i.e. everything i want excel to do on a cell change on this particular sheet goes under this Private Sub Worksheet_Change(ByVal Target As Range) ?

i apologize for my ignorance on this matter...i just picked up a book yesterday and am starting to learn..

thanks again

 

[Blade Hunter]

You will need to put some logic in to detect the columns for example:

If target.column = 1 then 'Column A
    'Do stuff
Elseif target.column = 2 then
    'Do other stuff
end if

Or you could do a case (I personally like Case statements):

Select case target.column
Case 1
    'Do stuff
Case 2
    'Do other stuff
End select

But yes, to answer the question this event will be used for all change level macro code, if there are large volumes it may be worth creating seperate macros on a module and simple call them from the change event.

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
    Application.EnableEvents = False
    On Error GoTo Enabler   [COLOR="Green"]' Re- eneble events if an error occurs[/COLOR]
    
   [COLOR="Green"] ' Check if any change occured in B5:B200[/COLOR]
    If Not Intersect(Range("B5:B200"), Target) Is Nothing Then
        
       [COLOR="Green"] 'Loop for each changed cell in B5:B200[/COLOR]
        For Each cell In Intersect(Range("B5:B200"), Target)

            If IsDate(cell) Then

                Select Case Weekday(cell)
                    Case 1
                        cell.Offset(Counter, 3) = cell.Value + 1
                    Case 2, 3, 4, 5, 6
                        cell.Offset(Counter, 3) = cell.Value
                    Case 7
                        cell.Offset(Counter, 3) = cell.Value + 2
                End Select

            End If

        Next cell

    End If
    

[COLOR="Green"]' Example: check if there was a chenge in column G[/COLOR]
    If Not Intersect(Range("G:G"), Target) Is Nothing Then
        'There was a chnage in column G then do something here
    End If
    
[COLOR="Green"]' Re-enable events[/COLOR]
Enabler:
        Application.EnableEvents = True

End Sub

 

[xelhelp]

could you please clarify what u mean by "need to put some logic in to detect the columns" ?

i have data in other columns like in column C and D, also starting at row 5. i have values that are updating from an external feed and i want the sum of these values in column G starting at row 5 these values

would the code be similar to the weekday code? would i use the same variable "Counter" for the offsets

so instead of B5 it will be C5 + D5

so the code would start with
Application.EnableEvents = False

and end with
Application.EnableEvents = True


thanks

 

[AlphaFrog]

Instead of the Worksheet_Change code for the dates in column B, you could put this formula in E5 and copy it down to E200 and it would do the same thing.

=IF(B5="","",B5+IF(WEEKDAY(B5)=1,1,IF(WEEKDAY(B5)=7,2,0)))