Welcome Guest
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
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

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.

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

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

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

Next question

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.   thought it would be easy; but it didn't work when I changed it.

' Macro recorded 12/24/2005 by
'

'
, Destination:=Range("H1"))
.Name = _
.FieldNames = True
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SaveData = 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

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

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

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

 Page 2 of 2