excel

Create excel ranges using column numbers in vba?

by

To reference range of cells you can use Range(Cell1,Cell2), sample: Sub RangeTest() Dim testRange As Range Dim targetWorksheet As Worksheet Set targetWorksheet = Worksheets(“MySheetName”) With targetWorksheet .Cells(5, 10).Select ‘selects cell J5 on targetWorksheet Set testRange = .Range(.Cells(5, 5), .Cells(10, 10)) End With testRange.Select ‘selects range of cells E5:J10 on targetWorksheet End Sub

Properly Handling Errors in VBA (Excel)

by

You’ve got one truly marvelous answer from ray023, but your comment that it’s probably overkill is apt. For a “lighter” version…. Block 1 is, IMHO, bad practice. As already pointed out by osknows, mixing error-handling with normal-path code is Not Good. For one thing, if a new error is thrown while there’s an Error condition … Read more

Sum up a column from a specific row down

by

=Sum(C:C)-Sum(C1:C5) Sum everything then remove the sum of the values in the cells you don’t want, no Volatile Offset’s, Indirect’s, or Array’s needed. Just for fun if you don’t like that method you could also use: =SUM($C$6:INDEX($C:$C,MATCH(9.99999999999999E+307,$C:$C)) The above formula will Sum only from C6 through the last cell in C:C where a match of … Read more

How to randomize Excel rows

by

Perhaps the whole column full of random numbers is not the best way to do it, but it seems like probably the most practical as @mariusnn mentioned. On that note, this stomped me for a while with Office 2010, and while generally answers like the one in lifehacker work,I just wanted to share an extra … Read more

Convert milliseconds to date (in Excel)

by

Converting your value in milliseconds to days is simply (MsValue / 86,400,000) We can get 1/1/1970 as numeric value by DATE(1970,1,1) = (MsValueCellReference / 86400000) + DATE(1970,1,1) Using your value of 1271664970687 and formatting it as dd/mm/yyyy hh:mm:ss gives me a date and time of 19/04/2010 08:16:11

How to add a button programmatically in VBA next to some sheet cell data?

by

I think this is enough to get you on a nice path: Sub a() Dim btn As Button Application.ScreenUpdating = False ActiveSheet.Buttons.Delete Dim t As Range For i = 2 To 6 Step 2 Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3)) Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height) With btn .OnAction = “btnS” .Caption = … Read more