Identify cells with leading apostrophe via VBA

[AlexanderBB]

How do you detect if text in a cell starts with a leading apostrophe ? TIA.

 

[MARK858]

Re: One last try... can anyone answer this

Maybe...

Sub xxx()
Dim c As Range
For Each c In Selection
    If c.PrefixCharacter = "'" Then
        MsgBox c.Address
    End If
Next c
End Sub

 

[Rick Rothstein]

Re: One last try... can anyone answer this

How do you detect if text in a cell starts with a leading apostrophe ? TIA.

I am not sure that you can; but, given that Excel hides it from you and from all output and calculations, why would you need to know this?

 

[AlexanderBB]

Re: One last try... can anyone answer this

> why would you need to know this?
The worksheet stores text, which also exists in Access (where it is manipulated and corrections done etc).
A routine then compares Excel with Access, it they're the same do nothing, otherwise update Excel.
So you see the problem.. Excel says the data is different because of the missing apostrophe.
That could have been accepted and updated anyway but it bugged me I couldn't read exactly what the cell contained.
It seemed wrong, if a cell is formatted for text, that's what you should get, not part of it. So I was looking for some way to resolve that.

And it looks like Mark has it, c.PrefixCharacter does pick up the apostrophe, but as the cell isn't Selected, I'm trying some changes.

Thanks Mark & Rick.

 

[MARK858]

Re: One last try... can anyone answer this

but as the cell isn't Selected, I'm trying some changes.

I used Selection because you didn't state any particular range it was supposed to relate to

 

[AlexanderBB]

Re: One last try... can anyone answer this

I didn't have a range Mark, was using .cells(row, column). Now trying thus

                    Dim c As Range
                    For Each c In Worksheets("Sheet1").Range("C" & kRow)
                    Debug.Print c.PrefixCharacter
                    Next

As you can probably tell, I'm no expert. The For loop seems is only executed once, so maybe isn't necessary?

 

[Rick Rothstein]

Re: One last try... can anyone answer this

I didn't have a range Mark, was using .cells(row, column). Now trying thus

                    Dim c As Range
                    For Each c In Worksheets("Sheet1").Range("C" & kRow)
                    Debug.Print c.PrefixCharacter
                    Next

As you can probably tell, I'm no expert. The For loop seems is only executed once, so maybe isn't necessary?

Correct... the loop is not necessary for a single cell. If kRow has a value in it, then...

Debug.Print Worksheets("Sheet1").Range("C" & kRow).PrefixCharacter

 

[MARK858]

Re: One last try... can anyone answer this

Just to add a cell is a Range and you should specify it (even when using variables) when asking questions or else you liable to get things like Selection being used (despite how much we dislike selecting ).

Anyway hopefully you have it sorted now

 

[AlexanderBB]

Re: One last try... can anyone answer this

This is coming together even more nicely that ever hoped. My original line was

If (CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i))))) And ("'" & CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i))))) Then

can now be

CommaBit = IIf(i = 3, Evaluate(TitleCol).PrefixCharacter, "") 'TitleCol= "Sheet1!$B$178"
If CommaBit & CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i)))) Then

Only when i = 3 is existence of any 'leading comma' possible.
Speed isn't much different (doing 3000 rows) but it seems better code and Evaluate is working very nicely.
And knowing now about Prefix Character, I was able to Google and find a bit more.

Thanks to you both.

 

[Macropod]

Re: One last try... can anyone answer this

This thread seems to be a continuation of the discussion you started in https://www.mrexcel.com/forum/excel...y-leading-comma-cell-vba-udf-post5197838.html, except that there you described the leading character as a comma. Is this, in fact, the same topic but with different terminology? If so, which terminology is correct?