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"
Else
Call Multipaste
End If
End Sub

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

=Sum("Formula", "Tips")

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

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


Advance,
    =SUM(AVERAGE(A1:A3),MAX(B1:B3))


Array,
   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))

=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.

Question:

Answer:

 C4 Formula,

=IFERROR(SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",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

Diffuser Size Calculator

 Download Link

Resistor Value Calculator

  Download Link

LIFE_TIME_DIARY

Download Link

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
rst.MoveFirst
With Me.ListBox1
.Clear
Do
.AddItem rst![ Field_Name]
rst.MoveNext
Loop Until rst.EOF
End With
UserForm_Initialize_Exit:
On Error Resume Next
cnn.Close
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
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)

Bold/Color

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

Next

End Sub

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 "       

                              or

=DATEDIF(A1,TODAY(),"Y")