Getting a string array?

[Johannekie]

Hi,

I am writing a addin toolbar in excel VBA.

When I get a cell and the text inside, I want to compare the the text to replace it with something else.

Eg. 27d8'35.7807" must be replaced with 27d08'35.7807"

So I want to check for: after 'd' and before ' for a single digit and replace it with a 0 and the digit.

Thats why I thought of using a string array. By putting the text of the cell in the string in the array and testing for a d and a '

But the cell text must be in a object and if I use

Dim c As Object
Set c = Range("C3")
MsgBox c.Text & ": " & c(1).Text

c(1).Text is also the whole cell
and I suspected it to be (like in the eg.) only 7

Can anyone tell me how can I loop through the cell contents one digit at a time.

[royUK]

Does the position d change?

[MatrixMan]

Hi Johannekie ... if you really want to use an array, then take a look at the Characters property; this allows you to specify / examine an individual character within a string and change it if necessary. However, I'd suggest that you could do this using the InStr function instead. Hope that helps. MM.

[Johannekie]

No, the position d will always be 3rd.

Thank for all your help

[royUK]

Try this

Option Explicit

Sub amendEntry()
    Dim str    As String

    str = Cells(1, 1).Value

    If InStr(str, "d") = 3 And Mid(str, 4, 1) <> "0" Then _
       str = Left(str, 3) & "0" & Right(str, Len(str) - 3)
    Cells(1, 1) = str
End Sub

[Johannekie]

Thanks, it works perfectly.

Now I at least have something to work with.
Thanks again

[royUK]

Please mark this thread solved.