hai buddies,
I am having a problem with the arrays in VBA.plz help me.
the code i use is like this.

Code:
--------------------


Dim st(20) As Variant 'Integer
Dim et(20) As Variant 'Integer
Dim dtdate As Date
Dim irng1 As Integer
Dim idtcnt1 As Integer

For rng = 4 To 15 Step 1 ' main For Loop
dtdate = ActiveWorkbook.ActiveSheet.Cells(rng, 1).Value
idtcnt1 = 1
iarraycnt = 0
irng1 = 4
Do While irng1 < 16
If ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value < "00:00:00" Then
If (dtdate = ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) And idtcnt1 = 1 Then
idtcnt1 = idtcnt1 + 1
st(irng1 - 4) = CInt(ActiveWorkbook.ActiveSheet.Cells(irng1, 11).Value)
et(irng1 - 4) = CInt(ActiveWorkbook.ActiveSheet.Cells(irng1, 12).Value)
'MsgBox st(irng1 - 4) & " " & et(irng1 - 4)
ElseIf (dtdate < ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) And (idtcnt1 = 1) Then
ElseIf (dtdate < ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) Then Exit Do
End If
ElseIf ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value = "00:00:00" Then
Exit Do
End If
irng1 = irng1 + 1
Loop
End If
MsgBox "Total no. of times for the date " & dtdate & " is " & idtcnt1 - 1

iarraycnt = idtcnt1 - 1

' check for clash

For j = 0 To idtcnt1 - 1 Step 1
If st(j + 1) < "" And et(j + 1) < "" Then
If (st(j) < et(j)) Then
If (et(j) <= st(j + 1)) Then
If st(j + 1) < et(j + 1) Then
Else
MsgBox "Clash for date " & dtdate
End If
Else
MsgBox "Clash for date " & dtdate
End If
Else
MsgBox "Clash for date " & dtdate
Exit Sub
End If
Else
Exit For
End If
Next
' end of check for clash
rng = irng1 - 1

Next

--------------------


I will tell u what the above code does before i narrate my problem.
my excel sheet is having 4 columns. (date,start time, end
time,activity)
one date can have many rows but the time should not clash. it should be
like this.
ex: 1/1/2004 0930 1030 study
1/1/2004 1045 1200 sleep
the following is wrong
1/1/2004 0930 1030
1/1/2004 1000 1200 (becoz for 1st activity the time is spent upto 1030
and the next activity should start from 1030 only.)
In the do while loop i am storing the time values and later after the
do wile loop i am checking for the clash comparing the values in the
array st() and et().
this is working fine for the first main For Loop iteration and from the
second iteration of For Loop the clash check code compares the same old
values (of the first iteration.) but not the values which is recently
stored in array st() and et().
i think this is related to scope.
thanks & regards,
Mahesh


--
itsmaheshp
------------------------------------------------------------------------
itsmaheshp's Profile: http://www.excelforum.com/member.php...o&userid=15859
View this thread: http://www.excelforum.com/showthread...hreadid=278219

Scope would only come into play if you were working in multiple procedures
or referencing variables declared outside you procedure. This doesn't
appear to be the case.

I would look at where your values are being assigned in the first part of
the array. If you are satisfied they are being assigned correctly, then
perhaps you need to clear the arrays each time you go to a new date -
otherwise you might be dragging forward some values from a previous date.

--
Regards,
Tom Ogilvy

"itsmaheshp" wrote in message
...

hai buddies,
I am having a problem with the arrays in VBA.plz help me.
the code i use is like this.

Code:
--------------------


Dim st(20) As Variant 'Integer
Dim et(20) As Variant 'Integer
Dim dtdate As Date
Dim irng1 As Integer
Dim idtcnt1 As Integer

For rng = 4 To 15 Step 1 ' main For Loop
dtdate = ActiveWorkbook.ActiveSheet.Cells(rng, 1).Value
idtcnt1 = 1
iarraycnt = 0
irng1 = 4
Do While irng1 < 16
If ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value < "00:00:00" Then
If (dtdate = ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) And
idtcnt1 = 1 Then
idtcnt1 = idtcnt1 + 1
st(irng1 - 4) = CInt(ActiveWorkbook.ActiveSheet.Cells(irng1, 11).Value)
et(irng1 - 4) = CInt(ActiveWorkbook.ActiveSheet.Cells(irng1, 12).Value)
'MsgBox st(irng1 - 4) & " " & et(irng1 - 4)
ElseIf (dtdate < ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) And
(idtcnt1 = 1) Then
ElseIf (dtdate < ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value) Then
Exit Do
End If
ElseIf ActiveWorkbook.ActiveSheet.Cells(irng1, 1).Value = "00:00:00"
Then
Exit Do
End If
irng1 = irng1 + 1
Loop
End If
MsgBox "Total no. of times for the date " & dtdate & " is " &
idtcnt1 - 1

iarraycnt = idtcnt1 - 1

' check for clash

For j = 0 To idtcnt1 - 1 Step 1
If st(j + 1) < "" And et(j + 1) < "" Then
If (st(j) < et(j)) Then
If (et(j) <= st(j + 1)) Then
If st(j + 1) < et(j + 1) Then
Else
MsgBox "Clash for date " & dtdate
End If
Else
MsgBox "Clash for date " & dtdate
End If
Else
MsgBox "Clash for date " & dtdate
Exit Sub
End If
Else
Exit For
End If
Next
' end of check for clash
rng = irng1 - 1

Next

--------------------


I will tell u what the above code does before i narrate my problem.
my excel sheet is having 4 columns. (date,start time, end
time,activity)
one date can have many rows but the time should not clash. it should be
like this.
ex: 1/1/2004 0930 1030 study
1/1/2004 1045 1200 sleep
the following is wrong
1/1/2004 0930 1030
1/1/2004 1000 1200 (becoz for 1st activity the time is spent upto 1030
and the next activity should start from 1030 only.)
In the do while loop i am storing the time values and later after the
do wile loop i am checking for the clash comparing the values in the
array st() and et().
this is working fine for the first main For Loop iteration and from the
second iteration of For Loop the clash check code compares the same old
values (of the first iteration.) but not the values which is recently
stored in array st() and et().
i think this is related to scope.
thanks & regards,
Mahesh


--
itsmaheshp
------------------------------------------------------------------------
itsmaheshp's Profile:
http://www.excelforum.com/member.php...o&userid=15859
View this thread: http://www.excelforum.com/showthread...hreadid=278219