Read every character in a string using excel VBA

This example reads every character in a string from left to right and returns the result in a message box. It makes use of the Mid function.

Sub LoopThroughString()

Dim LookInHere As String
Dim Counter As Integer

'Use your own text here
LookInHere = "AutomateExcel.com"

For Counter = 1 To Len(LookInHere)
    MsgBox Mid(LookInHere, Counter, 1)
Next

End Sub

Read every word in a string using excel VBA

This example reads every word in a string from left to right and returns the result in a message box. It makes use of the Split function.

Sub LoopThroughString2()

Dim LookInHere As String
Dim Counter As Integer
Dim SplitCatcher As Variant

'Use your own text here
LookInHere = "I Heart AutomateExcel.com"

SplitCatcher = Split(LookInHere, " ")

For Counter = 0 To UBound(SplitCatcher)
    MsgBox SplitCatcher(Counter)
Next

End Sub

Split excel in to several sheets based on column data?

While working on yearly reports or data you have for several users. You have the consolidated data in to several sheets based on year or user names. This can be easily achieved by macro. Please find below an example and a macro to achieve this.

Replace zero with blank in excel

In financial sheets and calculation we want a utility to replace zero with blanks. Don't worry this can be achieved with a macro.

Find and replace enter keys in excel

Most of the time while working excel we accidently end up typing Enter keys. We can easily find and replace the enter or Alt+Enter keys in excel cells.

Press Crtl+H

Now in Find text - Press Alt Key on your keyboard and type 0010 on numpad. Remember when you are typing on numpad keep the Alt key pressed. i.e, "Alt0010". You wont see any character in Find text.

Now keep replace with as blank

Press Find All to find the Alt+Enter Keys or Replace all to replace or remove them.

Handle error formulae in excel

Most of the time when we perform any calculation using formulaes and they return #Value or #N/A which we don't want to return instead we need to display blank or 0 values. We can do this by using following formulae.

To display blank when the formulae returns error "=iferror([original formula which you want to perform],"") "

To display 0 when the formulae returns error "=iferror([original formula which you want to perform],"0") "

Example 1: =iferror(Sum(A1:A14),"")

Example 2: =iferror(Sum(A1:A14),"0")