Welcome Guest
Log In | Register )
You last visited December 10, 2016, 7:35 am
All times shown are
Eastern Time (GMT-5:00)

For Excel Wizards Only!

Topic closed. 17 replies. Last post 9 years ago by BevsPicks2.

Page 2 of 2
PrintE-mailLink
Avatar
EAST COAST, USA
United States
Member #49428
January 31, 2007
553 Posts
Offline
Posted: August 22, 2007, 3:17 pm - IP Logged

@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 
 10 
 1    1st2nd 3rd  orderbefore  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
 

time*treat, Thanks a bunch! That is awesome! What language did you say that was?

    time*treat's avatar - radar

    United States
    Member #13130
    March 30, 2005
    2171 Posts
    Offline
    Posted: August 22, 2007, 3:33 pm - IP Logged

    VBA = Visual Basic for Applications. It is VB macro language inside excel and other M$ Office apps.

    I also want to add that sometimes the code indentations show up right in the preview, but don't post correctly. I hope you can follow what I intended. 

      Avatar
      EAST COAST, USA
      United States
      Member #49428
      January 31, 2007
      553 Posts
      Offline
      Posted: August 22, 2007, 4:01 pm - IP Logged

      VBA = Visual Basic for Applications. It is VB macro language inside excel and other M$ Office apps.

      I also want to add that sometimes the code indentations show up right in the preview, but don't post correctly. I hope you can follow what I intended. 

      Thanks a bunch time*treat.  I'm going to try this this weekend.  I'll keep you posted!