Help With VBA needed..spreadsheet seeking and deleting 1

[eduards]

I really need some help a the moment .I am fairly new to VBA.
I have bought two books and neither have helped at all. My problem is : I am trying to write some code for excel and i have never done this before ..In my excel spreadsheet i have 4 columns .1= Site 2= Pump number 3= date and time 4= State ( Running or stopped)
The site refers to which pumping station i am dealing with , the pump no. is the actual pump ..there are up to 12 pumps at each station the date and time refers to the time a pump was started or stopped. and the state tells whether it has started or stopped. I have sorted the data according to Site , pump no and then date. The main problem is that the database has not only got the records for when the pump turned on ( running) and then when it stopped ....every so often it has a random recording that says what the state of the pumps are at that time..so i need to sort through the data and starting with a "running" value for a specific site and pump then look for the next Stopped value in the column that corresponds with the same site and pump and is sequential in date ..ie the next stop that corresponds to the Running value..I also want the code to then delete all the records(rows) (only for the same site and pump ) that are inbetween the Running and Stopped value ..these will be running state values. Then I need the code to go to the next running value after the stopped for the same site and pump and do the process again.. I need to do this for all the different sites and pumps for three years worth of data ..so as you can see it is not possible to do manually .. I would send you the code that i have tried to write over the last three days but it would only embarass me .... If anyone can help it would really be appreciated and they can reply here or to my email address...also please dont ask why i am doing this kind of coding when i am not a computer expert .. i have asked myself the same thing thousands of times.I f anyone can help i can send a copy of the spreadsheet ..
Thanks
Eduard

 

[kevinclark]

Took a quick crack at it and came up with the following code for you to try. Not sure it is exactly what you want but maybe worth your looking at.

This code works under following assumptions

Source SpreadSheet name = Sheet1
Column 1 = Site
Column 2 = Pump Number
Column 3 = Date / Time
Column 4 = Status (RUNNING or STOPPED)
Column 5 is Unused and available to this program

This code should work through Sheet 1, writing the values that meet the criteria (as you described) to another worksheet (Sheet 3). It does not delete information from Sheet 1, but rather sorts it all out in the following manner to Sheet 3. It should ignore as you say all records that fall "in between the Running and Stopped value"

Site Pump RUNNING DATE STOPPED DATE
A 1 1/1/2000
A 2 1/2/2000 1/3/2000
B 8 1/4/2000 1/5/2000


Anyway hope that it may be of some help.




Sub SortRecords()
Dim S_WS As String 'Source Worksheet
Dim S_Row As Integer 'Source Row
Dim T_WS As String 'Target Worksheet
Dim T_Row As Integer 'Target Row
Dim S_Site As String 'Source Site
Dim S_Pump As String 'Source Pump
Dim SearchStr As String 'Current Record
Dim S_Date As Date 'Source Date
Dim S_Stat As String 'Source Status
Dim tmp As Date 'Holds Temp value

'Change the following 3 lines
'to equal your worksheet names
S_WS = "Sheet1" 'Set Source Worksheet
T_WS = "Sheet3" 'Set Target WorkSheet
T_Row = 2 'Set Target Row

'Starts at Row Number 2 and loops to Row Number 100
'Change as necessary
For S_Row = 2 To 100
If Sheets(S_WS).Cells(S_Row, 5).Value <> &quot;IGNORE&quot; Then
Sheets(S_WS).Cells(S_Row, 5).Value = &quot;IGNORE&quot;
S_Site = Sheets(S_WS).Cells(S_Row, 1).Value
S_Pump = CStr(Sheets(S_WS).Cells(S_Row, 2).Value)
S_Date = CDate(Sheets(S_WS).Cells(S_Row, 3).Value)
S_Stat = UCase(Sheets(S_WS).Cells(S_Row, 4).Value)
SearchStr = CStr(S_Site & S_Pump)
If SearchStr <> &quot;&quot; Then
Sheets(T_WS).Cells(T_Row, 1).Value = S_Site
Sheets(T_WS).Cells(T_Row, 2).Value = S_Pump
If S_Stat = &quot;RUNNING&quot; Then
Sheets(T_WS).Cells(T_Row, 3).Value = S_Date
tmp = NextStat(S_WS, SearchStr, S_Date, S_Stat, S_Row)
If tmp <> CDate(&quot;1/1/1900&quot Then
Sheets(T_WS).Cells(T_Row, 4).Value = tmp
End If
ElseIf S_Stat = &quot;STOPPED&quot; Then
Sheets(T_WS).Cells(T_Row, 4).Value = S_Date
tmp = NextStat(S_WS, SearchStr, S_Date, S_Stat, S_Row)
If tmp <> CDate(&quot;1/1/1900&quot Then
Sheets(T_WS).Cells(T_Row, 3).Value = tmp
End If
End If
T_Row = T_Row + 1
End If
End If
Next S_Row
End Sub


Function NextStat(S_WS As String, SearchStr As String, S_Date As Date, S_Stat As String, S_Row As Integer) As Date
Dim Site As String
Dim Pump As Integer
For r = S_Row To 100
Site = Sheets(S_WS).Cells(r, 1).Value
Pump = Sheets(S_WS).Cells(r, 2).Value
If CStr(Site & Pump) = SearchStr Then
If UCase(Sheets(S_WS).Cells(r, 4).Value) <> S_Stat And CDate(Sheets(S_WS).Cells(r, 3).Value) >= S_Date Then
Sheets(S_WS).Cells(r, 5).Value = &quot;IGNORE&quot;
NextStat = CDate(Sheets(S_WS).Cells(r, 3).Value)
Exit Function
Else
Sheets(S_WS).Cells(r, 5).Value = &quot;IGNORE&quot;
End If
End If
Next r
NextStat = CDate(&quot;1/1/1900&quot
End Function