Quote: Originally posted by time*treat on Aug 22, 2007
@BP3
I had some old TX3 laying around so you will need to make a change or 3
- I am guessing that column 8 is where the action is
- my sample data with layout headers
- your columns 1-7 are just copied over, don't worry about mine being different.
- You want to change texaspick3 and output to whatever you called your sheets
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
1 | | | | | 1st | 2nd | 3rd | order | before | after |
2 | p3 | 10 | 25 | 93 | 3 | 2 | 9 | 329 | | |
3 | p3 | 10 | 26 | 93 | 8 | 4 | 1 | 841 | | |
4 | p3 | 10 | 27 | 93 | 9 | 6 | 2 | 962 | | |
some of these lines "wrap", look for an _ (underscore).
Option Explicit
Sub p3__query()
'by time*treat'
Dim INpage As Object
Set INpage = Sheets("texaspick3")
Dim OUTpage As Object
Set OUTpage = Sheets("output")
Const blue As Integer = 5
Const green As Integer = 4
Dim sought__num As Integer, before__num As Integer, after__num As Integer
Dim current__line As Integer, hit__counter As Integer, col As Integer
hit__counter = 0
Const top__line As Integer = 2 'first line where numbers appear'
Const grab__col = 8 'column where full number is located'
sought__num = Application.InputBox("Enter a number: 000 to 999", Type:=1)
OUTpage.Select
OUTpage.Cells.Clear
current__line = top__line
While INpage.Cells(current__line, 1).Value <> ""
If INpage.Cells(current__line, grab__col).Value = sought__num Then
hit__counter = hit__counter + 1
before__num = -99
after__num = -99
If current__line - 1 >= top__line Then _
before__num = INpage.Cells(current__line - 1, grab__col).Value
If INpage.Cells(current__line + 1, 1).Value <> "" Then _
after__num = INpage.Cells(current__line + 1, grab__col).Value
OUTpage.Cells(hit__counter, 1).Value = "#" & hit__counter
For col = 1 To grab__col
OUTpage.Cells(hit__counter, col + 1).Value = _
INpage.Cells(current__line, col).Value
Next col
OUTpage.Cells(hit__counter, grab__col + 2).Font.ColorIndex = blue
OUTpage.Cells(hit__counter, grab__col + 3).Font.ColorIndex = green
If before__num <> -99 Then
OUTpage.Cells(hit__counter, grab__col + 2).Value = before__num
OUTpage.Cells(hit__counter, grab__col + 2).NumberFormat = "000"
Else
OUTpage.Cells(hit__counter, grab__col + 2).Value = "---"
End If
If after__num <> -99 Then
OUTpage.Cells(hit__counter, grab__col + 3).Value = after__num
OUTpage.Cells(hit__counter, grab__col + 3).NumberFormat = "000"
Else
OUTpage.Cells(hit__counter, grab__col + 3).Value = "---"
End If
End If
current__line = current__line + 1
Wend
OUTpage.Cells.EntireColumn.AutoFit
End Sub