I don't know what this site uses for quotation marks, but whenever I copy and paste a formula from this site I have to replace the quotation marks with "real" quotation marks to get Excel to recognize the formula. So, I whipped up a macro to do it (see it at bottom).
However, it's not working with the formula posted by Robert on this page:http://chandoo.org/wp/2012/03/05/yet-another-sales-funnel-chart-in-excel/
His formula is '=TEXT(B5,”$#,##0.00″)&” (“&TEXT(C5,”0%”)&”)”'
My macro replaces all except the quotation after 0.00. When I copy that and paste into my macro, it comes out as a comma. Since I'd like my macro to work for all instances, how do I change it to make all of the quotation marks valid?
Here's my macro:
'Sub ReplaceBadQuotes()
'Replaces all Chandoo bad quote symbols with good ones
With Application
.ScreenUpdating = False
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & LastRow).Replace What:="”", Replacement:=Chr(34), _
LookAt:=xlPart, _
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Range("A2:A" & LastRow).Replace What:="“", Replacement:=Chr(34), _
LookAt:=xlPart, _
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With
End With
End Sub'
However, it's not working with the formula posted by Robert on this page:http://chandoo.org/wp/2012/03/05/yet-another-sales-funnel-chart-in-excel/
His formula is '=TEXT(B5,”$#,##0.00″)&” (“&TEXT(C5,”0%”)&”)”'
My macro replaces all except the quotation after 0.00. When I copy that and paste into my macro, it comes out as a comma. Since I'd like my macro to work for all instances, how do I change it to make all of the quotation marks valid?
Here's my macro:
'Sub ReplaceBadQuotes()
'Replaces all Chandoo bad quote symbols with good ones
With Application
.ScreenUpdating = False
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & LastRow).Replace What:="”", Replacement:=Chr(34), _
LookAt:=xlPart, _
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Range("A2:A" & LastRow).Replace What:="“", Replacement:=Chr(34), _
LookAt:=xlPart, _
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With
End With
End Sub'