Wednesday, 18 January 2017

VBA (Excel) – Add Single Quote, Excel text cells, Zeros 0s Fix

Hill All, happens to copy and paste from SQL tables or web Page to Excel files, if some fields begin with Zero 
example:“0000545LWEBCODE” , 
when paste to Excel Zeros 0s before other numbers were removed 
example:“545LWEBCODE”
To fix Zeros 0 before number and other things , Here is VBA funcion/Script which add a single quote ‘ before each cells: you can copy from any external platform like Internet, SQL Tables, SQL Views etc.., and paste it in your Microsoft Excel sheet,
Just Copy and Paste this code in a Module a run it.


‘** VBA Script Excel Zeros 0s Fix Script by lwebcode.blogspot.com
Public Sub AddQuote()
Dim Start_, End_, Col, i As Integer
Dim SName As String

Start_ = InputBox(“Row Start:”, “Start”, “1”)
End_ = InputBox(“Row End:”, “End”, “10000”)

Col = CInt(InputBox(“Column (in Number) to Add Quote”, “Column”, “1”))

SName = InputBox(“Sheets’s Name:”, “Sheets”, Sheets(1).Name)

For i = Start_ To End_
    If Sheets(SName).Cells(i, Col).Value “” Then
        Sheets(SName).Cells(i, Col).Value = “‘” & Sheets(SName).Cells(i, Col).Value
    End If
Next

MsgBox “Executed”, vbInformation, “Executed”

End Sub


No comments:

Post a Comment