Posts

Showing posts from September, 2012

Column A range break to multiple ranges

Sub data_filter()
Dim k As Integer
For k = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row Step 40
Sheet1.Range(Sheet1.Cells(k, "A"), Sheet1.Cells(k + 39, "A")).Copy Sheet1.Range("XFD1").End(xlToLeft).Offset(0, 1)
Next
End Sub

Example :-

datadata     44,305      46,119      45,540      44,305      49,819      43,784      49,313      49,819      41,860      44,472      44,973      41,860      46,119      43,784      44,472      45,540      49,313      44,973

Year, Month, Day of a Date

Dim exampleDate As Date
exampleDate = DateValue("Jun 19, 2010")
MsgBox Year(exampleDate)


DateAdd



Dim firstDate As Date, secondDate As Date

firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)

MsgBox secondDate

Current Date & Time
MsgBox Now
Hour, Minute and Second
MsgBox Hour(Now)
TimeValue
MsgBox TimeValue("9:20:01 am")



Loop in EXCEL VBA

MaxMinAverageDate20163/1/200122173/2/200127213/3/200129233/4/200125203/5/200125213/6/200124163/7/200123173/8/200122203/9/200121263/10/200127213/11/200120163/12/200122173/13/200126203/14/200123183/15/200129233/16/200127213/17/200128223/18/200125203/19/2001

'===========================================================
http://jeetexltips.blogspot.in/
'===========================================================
Sub DO_Until()
' This loop runs until there is nothing in the next column
    Do
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Sub Do_While()
' This loop runs as long as there is something in the next column
    Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Sub Do_While_Not()
' This loop runs as long as there is something in the next column
    Do While No…

An Example of Looping Over Worksheets

Image
Imagine that (somewhat egotistically) you decide to name all of your worksheets in a workbook after your company name (for us it's Wise Owl).  Sub RenameWorksheets() 'a reference to each worksheet in the active workbook Dim ws As Worksheet 'the index number to use Dim SheetNumber AsInteger SheetNumber = 0 ForEach ws In Worksheets 'for each worksheet, rename it SheetNumber = SheetNumber + 1 ws.Name = "Wise Owl " & SheetNumber Next ws EndSub

Delete Worksheet

Sub dlt_Sheet()
    Dim ws As Worksheet
         On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0   
End Sub