PDA

View Full Version : More showed up and Repeated Data!



websailor
06-29-2007, 08:23 PM
I have 22 students
I create 12 cells to show the names of these students who have high degree
how I can auto export the 12 cells to text file and but them in pop message every 30 minutes?

This question solved at this link

http://vbaexpress.com/forum/showthread.php?p=104406#post104406


I want a VBA code to pop up a message to alert me If the name of any student repeated during 60 minutes more than 2 times

any help......

websailor
06-30-2007, 07:30 AM
:(

websailor
06-30-2007, 03:19 PM
waiting the experts......

:think::dunno:help

websailor
07-01-2007, 02:41 PM
:hi: any budy here.....

JimmyTheHand
07-02-2007, 12:34 AM
Hi

this one is not clear for me:

If the name of any student repeated during 60 minutes more than 2 times Where are they repeated? In the popup messages? In the 22 cells? Or?

If it's in the popup messages they are repeated, which are displayed every 30 seconds, then you will have to work real fast with data input, to avoid data remaining there for too long. Also you can't have coffee breaks, because the timer will not wait for you to drink the coffee...

Looking at it this way, I have a strong feeling that you are going in the wrong direction. This thing you ask looks very circumstantial to do, at least with my knowledge, and the solution will be haphazard, at the best.
I'm sure there must be a much more simple and stable solution, if only we knew what it was you really wanted.

Jimmy

websailor
07-02-2007, 06:35 PM
Hi

this one is not clear for me:
Where are they repeated? In the popup messages? In the 22 cells? Or?

If it's in the popup messages they are repeated, which are displayed every 30 seconds, then you will have to work real fast with data input, to avoid data remaining there for too long. Also you can't have coffee breaks, because the timer will not wait for you to drink the coffee...

Looking at it this way, I have a strong feeling that you are going in the wrong direction. This thing you ask looks very circumstantial to do, at least with my knowledge, and the solution will be haphazard, at the best.
I'm sure there must be a much more simple and stable solution, if only we knew what it was you really wanted.

Jimmy

repeated in the 22 cells

and i'm understand what i'm doning

rbrhodes
07-02-2007, 08:10 PM
The 30 second interval was for testing purposes (by me). The actual interval requested was 30 minutes.

The msgbox gets it's data from the 12 cells.


The question now is are any of the names repeated twice (or more?) in 60 minutes?

Cheers,

dr

rbrhodes
07-02-2007, 11:06 PM
Hi again Websailor,

This code uses Sheet1, A100:A113, A110:A113 and !20:A123 to store old info. It needs to store 3 times.

When it starts it store the Info for interval 0. Then at interval 30 it displays current info and saves that at Info 30.

At interval 60 it writes Info 60 to third position, compares Info 60 with Info 0 and displays any that match. Now it moves info 30 to Info 0 position and Info 60 to Info 30 position. At next Info 60 interval it writes, compares and moves Info again...

HTH,

Cheers,

dr

websailor
07-03-2007, 06:39 AM
Hi again Websailor,

This code uses Sheet1, A100:A113, A110:A113 and !20:A123 to store old info. It needs to store 3 times.

When it starts it store the Info for interval 0. Then at interval 30 it displays current info and saves that at Info 30.

At interval 60 it writes Info 60 to third position, compares Info 60 with Info 0 and displays any that match. Now it moves info 30 to Info 0 position and Info 60 to Info 30 position. At next Info 60 interval it writes, compares and moves Info again...

HTH,

Cheers,

dr

YOU ARE AMAZING...:clap::clap::clap:

WHAT IF THE RANGE BETWEEN E11:H14 , THE CODE WILL CHANGE OR NOT?

AND I WANT ALL CODE IN SHEET1.....:hi:

rbrhodes
07-03-2007, 11:07 PM
Here's an example of how to change it. (Untested!!) Post a new example of your sheet maybe?

Cheers,

dr


Sub ShowStuff()
Dim i As Long
Dim j As Long
Dim m As Long


'change to 4*4 from 3*4)
'Dim stuff(12)
Dim stuff(16)


Dim countr As Long
Dim oldrng As String
Dim namelist As String
Dim repeatlist As String
'screen
Application.ScreenUpdating = False
'set time as 30 seconds
' Change to 00:30:00 for 30 minutes
dTime = Now + TimeValue("00:00:30")
'set up to run again
Application.OnTime dTime, "ShowStuff"

'init counter
countr = 0
'get where we are
oldrng = ActiveCell.Address


'get stuff from rows 25 to 28. Old:
'For i = 25 To 28
'get stuff from Cols A(1) to C(3). Old:
'For j= 1 to 3
'CHANGE TO E11 TO H14
'
' CHANGE ROWS TO 11 TO 14 (4 rows not 3). New:
For i = 11 To 14
' CHANGE COLS TO E(5) to H(8) (4 cols not 3) New:

For j = 5 To 8
If Sheets(1).Cells(i, j) <> "analysis" Then
stuff(countr) = Sheets(1).Cells(i, j)
countr = countr + 1
End If
Next j
Next i
'build Message of students
namelist = stuff(0)
For i = 1 To UBound(stuff)
If stuff(i) <> "" Then
namelist = namelist & ", " & stuff(i)
End If
Next i

'display message box with "Stuff"
If namelist <> "" Then
m = MsgBox(namelist, , Title:="High Degree")
End If
'Copy student list
With Sheets(1)
If .Range("A99") = 2 Then
'put new data in 120
.Range("A25:C28").Copy
.Range("A120").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'compare 60 data, build list of repeats
For i = 100 To 103
For j = 1 To 3
If .Cells(i, j) <> "analysis" Then
If .Cells(i, j) = .Cells(i + 20, j) Then
If repeatlist = "" Then
repeatlist = .Cells(i, j)
Else
repeatlist = repeatlist & ", " & .Cells(i, j)
End If
End If
End If
Next j
Next i
'then move old data down one
.Range("A110:C113").Copy .Range("A100")
.Range("A120:C123").Copy .Range("A110")
ElseIf .Range("A99") = 1 Then
'first time (30)
.Range("A99") = 2
'put data in 110
.Range("A25:C28").Copy
.Range("A110").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Application.CutCopyMode = False
End With
'display message box with "Repeats"
If repeatlist <> "" Then
m = MsgBox(repeatlist, , Title:="Repeated")
End If
'go home
Range(oldrng).Select

'screen
Application.ScreenUpdating = True

End Sub