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

Visual Basic Applications in Excel

Topic closed. 19 replies. Last post 11 years ago by lottaloot.

Page 2 of 2
PrintE-mailLink
hypersoniq's avatar - 8ball
Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
Posted: December 31, 2005, 7:10 pm - IP Logged

type hlookup or vlookup in excel help, there are examples there Thumbs Up 

here is an example of a regular lookup...

=LOOKUP(C2,$X$3:$X$12,$Y$3:$Y$12)

from my vtrac102 sheet:

C2 = lookup value... is the number drawn in the first position

$X$3:$X$12 = lookup vector... contain the digits 0 thru 9  (X3=0, X12=9)

$Y$3:$Y$12 = result vector... contain the corresponding vtrac value for each digit (0 and 5 = 1, etc)

I anchor the X and Y cells so when I autofill down, the reference stays valid, and I left C2 relative, that way I could autofill down and over in one step and reference the cells from the draw history

going accross

=LOOKUP(C2,$X$3:$X$12,$Y$3:$Y$12)

=LOOKUP(D2,$X$3:$X$12,$Y$3:$Y$12)

=LOOKUP(E2,$X$3:$X$12,$Y$3:$Y$12)

the form (from excel help file) is

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Playing more than one ticket per game is betting against yourself.

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: January 1, 2006, 12:14 am - IP Logged

    Tried that hyper, but I was still clueless.  Just did not make sense to me. 

    Paurths, you have dummied it down enough for me to finally understand it. 

    'preciate it Big Smile

     

     

     

    Hyper

    C2 would be lookup value

    X3:X12 would be lookup vector

    y3:y12 would be result vector 

    IF so, that now makes sense. 

    =LOOKUP(lookup_value, lookup_vector, [result_vector])

    L ttaL   T

      lottaloot's avatar - AvatarZ56
      Redford/MI
      United States
      Member #3396
      January 18, 2004
      4867 Posts
      Offline
      Posted: January 3, 2006, 1:50 am - IP Logged

      Next question

       Found this on http://www.ozgrid.com/forum/showthread.php?t=44383

      Giving the person problems due to his not specifying the required sheet as opposed to using the active sheet

      anywho, thought it would be cool to have the macro automatically pull the draws from the LP

      If I only knew how to Change the URL thingy.  Disapprove thought it would be easy; but it didn't work when I changed it. 

      Sub download()
         
          ' Macro recorded 12/24/2005 by
       '
         
       '
          With ActiveSheet.QueryTables.Add(Connection:= _
              "URL;http://www40.pinnaclesports.com/vwGuestLines.asp?redirected=yes&sportType=Basketball&sportSubType=NBA&descr=NBA" _
              , Destination:=Range("H1"))
              .Name = _
              "vwGuestLines.asp?redirected=yes&sportType=Basketball&sportSubType=NBA&descr=NBA"
              .FieldNames = True
              .RowNumbers = False
              .FillAdjacentFormulas = False
              .PreserveFormatting = True
              .RefreshOnFileOpen = False
              .BackgroundQuery = True
              .RefreshStyle = xlOverwriteCells
              .SavePassword = False
              .SaveData = True
              .AdjustColumnWidth = True
              .RefreshPeriod = 0
              .WebSelectionType = xlSpecifiedTables
              .WebFormatting = xlWebFormattingNone
              .WebTables = "5"
              .WebPreFormattedTextToColumns = True
              .WebConsecutiveDelimitersAsOne = True
              .WebSingleBlockTextImport = False
              .WebDisableDateRecognition = False
              .WebDisableRedirections = False
              .Refresh BackgroundQuery:=False
          End With
      End Sub

      L ttaL   T

        lottaloot's avatar - AvatarZ56
        Redford/MI
        United States
        Member #3396
        January 18, 2004
        4867 Posts
        Offline
        Posted: January 3, 2006, 2:02 am - IP Logged

        This one also looks really well.  It just has to be changed around to where it copied down what you want it to copy down.  Will have to do some studying on this one  

         

        Sub copydown()
            Dim lastRow As Long, i As Long
            lastRow = Range("G65536").End(xlUp).Row
           
            If ActiveSheet.Cells(1, 8).Value = "" Then
                MsgBox "Cell G7 is empty. Macro will be stopped."
                Exit Sub
            Else
                For i = 2 To lastRow
                    If ActiveSheet.Cells(i, 8).Value = "" Then
                        ActiveSheet.Cells(i, 8).Value = ActiveSheet.Cells(1, 8).Value
                    Else
                    End If
                Next i
            End If
            MsgBox "Macro is executed well."
        End Sub

        L ttaL   T

          lottaloot's avatar - AvatarZ56
          Redford/MI
          United States
          Member #3396
          January 18, 2004
          4867 Posts
          Offline
          Posted: January 3, 2006, 2:29 am - IP Logged

          Helpful tip

           

          If you want to copy & paste down a lot of information

          1 Select and copy cell with formula (does not have to have formulas though)

          2 Select cell where you want to start the paste.

          3 In the name box, to the left of the formula bar type the cell where the paste will finish. Do not press enter.

          4 Hold down shift and press enter.

          5 Goto Edit>Paste.

          L ttaL   T