PDA

View Full Version : Solved: Simple Macro



Hoopsah
10-28-2008, 07:52 AM
I know I am getting thicker here but,

I am looking for a macro that will check bolumn B and if it starts with apostrophe zero then do nothing, if it doesn't start with a apostophe zero then add it.

Should be straighforward, I have put
Sub Add_An_Apostrophe()
'
' Add_An_Apostophe Macro
' Macro recorded 03/10/2008 by Gerry McNally
'
If (Range("B1").Value = "0") Then

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row

Cells(i, "B").Value = "'" & Cells(i, "B").Text
Next i

Else

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row

Cells(i, "B").Value = "'0" & Cells(i, "B").Text
Next i
'
End If
End Sub


But that adds a zero even if there is one there!!

:banghead: :dunno

mdmackillop
10-28-2008, 08:08 AM
Does this help?
http://support.microsoft.com/kb/124739

Kenneth Hobs
10-28-2008, 08:14 AM
Sub AZero()
Dim r As Range
For Each r In Range("B1", Cells(Rows.Count, "B").End(xlUp))
If Not (VarType(r) = vbString And Left(r, 1) = "0") Then
r = "'0" & r
End If
Next r
End Sub

Hoopsah
10-28-2008, 08:17 AM
Fantastic.

Thanks Ken

Thanks MD too - decent link

Cheers Guys