How to trigger MsgBox for this event?
There's no loop in this code, so I presume this is a routine repetitively
called. If so you need a module level variable (or a Static variable in this
routine, see the VBA Help)
Dim/Static RoutineCounter As Long
In the routine, increment on entry
RoutineCounter=RoutineCounter+1
If RoutineCounter Mod 6=0 Then MsgBox "This a multiple of 6"
NickHK
"TimN" wrote in message
...
I have the following code which copies a range from one worksheet to
another,
both within the same workbook. If there is a duplicate date it copies
over
the date, if new date it copies to the next available cell.
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
.Worksheets("Data").Columns("B").Find(What:=(.Work sheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
.Worksheets("Data").Range("A65536").End(xlUp).Offs et(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B17:Q37").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal < False Then
ThisWorkbook.SaveAs RetVal & "xls"
End If
This copies two weeks of data at a time. on the 12th week or 6th
iteration,
a maximum is reached. I want at that point to cause a MsgBox to pop up
indicating can't use the form go to form blah, blah.... and on OK the user
form is closed.
I think I know how to do the MsgBox itself. My question is how do I
trigger
the MsgBox to appear? I know the cell in the worksheet where data is
being
copied to that would represent the maximum. Would that be the place that
triggers the MsgBox? If so, how do I accomplish that?
As always thanks so much for your help!!!!
|