17 May 2012

Rows to Repeat

1. Excel having options for "Rows to Repeat at Top" - Press Alt + P + I, Select the Rows and OK.

2. There is no in-build function for "Rows to Repeat at Bottom", You can try below codes to show it in footer.
 Sub MyFooter()
    Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A20:H20")
    For Each c In Rng
        StrFtr = StrFtr & c & " "
    Next c
    ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub

Avoid Scientific (Exponential) Notation

When we enter or paste the long numbers in cells, it’s automatically changed into scientific mode like 1.23E+10. It can be avoid with the help of below codes,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Target.NumberFormat = "0"
Call Multipaste
End If
End Sub

Sub Multipaste()
Dim mycell As Range
For Each mycell In Selection.Cells
mycell.NumberFormat = "0"
End Sub

13 May 2012

=Sum("Formula", "Tips")

    Alt + Equal(=)
    Alt, H, U, S

   Total  =SUM(A1,A2,A3)
   Total  =SUM(A1:A3,B1:B3)
   Total  =SUM(A:A 1:3)


   Countif =SUM(--(A1:A10="Apple"))
   Character Count =SUM(LEN(A1:A10))
   Sum Unique Values =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,A1:A10,0))
   Count Unique Values =SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10),0))

9 May 2012

=Rept("Formula", "Tips")

Tip 1: The below formula uses to extract the last name from Full Name entries. Assume, Name is in A1.

 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
Tip 2: Recently i have been solved this query on FB with the help of REPT formula. I hope that It will be useful for advanced formula users.



 C4 Formula,

REPT(" ",999)),(1*999))-25,100)),"Colors",""),"")

C5 Formula,

=IFERROR(IF(C4="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C4,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C6 Formula,

=IFERROR(IF(C5="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C5,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C7 Formula,

=IFERROR(IF(C6="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C6,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

 The same formula can be use it for other entries by copy paste method.

Sample file - Download Link

3 May 2012


Listbox ADODB Connection

VBA codes for bring the access database into list-box when userform get initialize.
Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "/DATABASE.mdb"
rst.Open "SELECT DISTINCT [Field_Name] FROM Table_Name ORDER BY [ Field_Name]", _
cnn, adOpenStatic
With Me.ListBox1
.AddItem rst![ Field_Name]
Loop Until rst.EOF
End With
On Error Resume Next
Set rst = Nothing
Set cnn = Nothing
Exit Sub
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Before running this macro you check ADO(Microsoft ActiveX Data Object Library x.x) at the VBEditor Tool → Reference.

(winXP Pro & Excel2000)


Make bold and change the color of the first 5 characters of text in a cell.

Sub FiveChrBoldColor()
Dim mycell As Range
For Each mycell In Selection.Cells
mycell.Characters(Start:=1, Length:=5).Font.FontStyle = "Bold"
mycell.Characters(Start:=1, Length:=5).Font.Color = -16776961
End Sub

1 May 2012

Age Calculation

If your Date of Birth is in A1 then Use below formula to Calculate Age,

0 Year 0 Month 0 Days,
=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "&DATEDIF(A1,TODAY(),"MD")&" Days"

0 Year 0 Month,
=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "

Only Year,
=DATEDIF(A1,TODAY(),"Y")&" Year "