Visual Basic Applications in Excel

Reply #19

lottaloot — Tue, 03 Jan 2006 07:29:20 GMT

Helpful tip If you want to copy paste down a lot of information1 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.

Reply #18

lottaloot — Tue, 03 Jan 2006 07:02:13 GMT

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.C... [ More ]

Reply #17

lottaloot — Tue, 03 Jan 2006 06:50:28 GMT

Next question Found this on http://www.ozgrid.com/forum/showthread.php?t=44383Giving the person problems due to his not specifying the required sheet as opposed to using the active sheetanywho, thought it would be cool to have the macro automatically pull the draws from the LPIf I only knew how to Change the URL thingy. 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... [ More ]

Reply #16

lottaloot — Sun, 01 Jan 2006 05:14:20 GMT

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 HyperC2 would be lookup valueX3:X12 would be lookup vectory3:y12 would be result vector IF so, that now makes sense. =LOOKUP(lookup_value, lookup_vector, [result_vector

Reply #15

hypersoniq — Sun, 01 Jan 2006 00:10:18 GMT

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,

Reply #14

paurths — Sun, 01 Jan 2006 00:02:49 GMT

Hi Lottaloot,i'm not an excel expert, but i guess this explanation will do... (?)A B67 135 232 336 445 536 648 723 822 966 1088 11I have selected these two columns and named it Draws .This goes for Vertical lookup: =VERT.ZOEKEN(66;Draws;2)I guess VERT.ZOEKEN is the same as VLookupWhat it does: look for the value 66 in Draws , when it finds this, it returns the value of the second column, on the same row, which in this case is 10.Does i... [ More ]

Reply #13

lottaloot — Sat, 31 Dec 2005 23:46:15 GMT

Lookup function in excelOk guys, here's the deal. I am seriously wanting to locate the DATE that an overdue combo was last seen in my spreadsheet. I read about vlookup hlookup and I am pretty sure that this is what I want to use. What I need is an interpretation of this Hlookup= address(row_num,column,abs_num,a1,sheet_textVlookup= (lookup_value_table_array,col,index_num,...)This is what I do not get (what exactly goes in each of these little boxes) This is why I can not go any further with... [ More ]

Reply #12

hypersoniq — Sat, 31 Dec 2005 18:16:58 GMT

Sub AddRows() ' a subroutine that can be referenced by the name AddRowsDim sh As Worksheet ' dim=dimension, used to declare a variable (sh) AS a data-type/object (Worksheet)Dim i As Long 'declare variable (i) as long data type (long = Long Integer, used when Integer is too small, values of Long data type are 32 bits where INT holds 16 bits

Reply #11

lottaloot — Sat, 31 Dec 2005 18:14:56 GMT

So dim sh as worksheet is saying choose the entire worksheet??? dim i as long is saying that that the interger should be long (or expecting a bigger number than usual

Reply #10

truecritic — Sat, 31 Dec 2005 17:59:36 GMT

DIM is Dimension. Used for a variable as an array. Would assume the same applies to Excel. I don't program Excel, so I'll let the experts provide more details.

Reply #9

lottaloot — Sat, 31 Dec 2005 17:35:12 GMT

Does anyone know what this is actually saying???I know you are adding rows here. But what does the dimming mean?? Sub AddRows()Dim sh As WorksheetDim i As Long

Reply #8

truecritic — Fri, 30 Dec 2005 12:25:21 GMT

Problem is...2 yrs later and my comments don't even make sense to me.

Reply #7

hypersoniq — Fri, 30 Dec 2005 10:58:30 GMT

Sub DeleteBlankRows1()Dim i As Long .Calculation = xlCalculationManual .ScreenUpdating = False For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End WithEnd SubThe first version is easier to understand because of the comments, even though both accomplish the same task... commenting is just a good program... [ More ]

Reply #6

lottaloot — Fri, 30 Dec 2005 03:18:18 GMT

I suppose that is a good idea

Reply #5

hareless — Fri, 30 Dec 2005 02:55:07 GMT

So you don't execute the wrong per...Peo...I mean code.

Reply #4

paurths — Fri, 30 Dec 2005 02:51:48 GMT

Why was that again

Reply #3

hypersoniq — Fri, 30 Dec 2005 02:10:12 GMT

not a good idea to delete commentsactually you should add your own comment lines so in 2 years you remember WHY you used a particular piece of code.trust me on that...

Reply #2

paurths — Fri, 30 Dec 2005 01:53:15 GMT

anything starting with ' is considered as comment in VB, and will not be executed. (probably the wrong word... translation from dutch... sorry

Reply #1

lottaloot — Fri, 30 Dec 2005 00:06:37 GMT

You can actually delete the we statements as well as the statement below the sub procedure starting with delete

Visual Basic Applications in Excel

lottaloot — Fri, 30 Dec 2005 00:04:57 GMT

Found this interesting excel VBA at http://www.ozgrid.com/VBA/VBACode.htm Thought someone might find a use for it. Sub DeleteBlankRows1()'Deletes the entire row within the selection if the ENTIRE row contains no data.'We use Long in case they have over 32,767 rows selected.Dim i As Long 'We turn off calculation and screenupdating to speed up the macro. With Application .Calculation = xlCalculationManual .ScreenUpdating = False 'We work backwards because we are dele... [ More ]