Instr

[Karen53]

Hi,

How would I catch the error if the string does not have a space in it and
save the value as it is in that case?

For iCtr = 2 To OldLusedrow
OldString = ws.Range("B" & iCtr).Value
ws.Range("A" & iCtr).Value = Left(OldString, InStr(OldString, "
") - 1)
Next

 

[Dave Peterson]

Dim SpacePos as long

For iCtr = 2 To OldLusedrow
OldString = ws.Range("B" & iCtr).Value
spacepos = instr(1, oldstring, " ", vbtextcompare)
if spacepos = 0 then
'no spaces, so no changes
else
ws.range("a" & ictr).value = left(oldstring, spacepos-1)
end if
next ictr

Maybe???????

 

[Karen53]

Never Mind. I got it.

Thanks

 

[Roger Govier]

Hi Karen

Dim test as Integer
Dim Newstring as String

For iCtr = 2 To OldLusedrow
OldString = ws.Range("B" & iCtr).Value
Test=Instr(OldString," ")
If Test = 0 then
Newstring = Oldstring
Else
Newstring = Left(Oldstring, test)
End If
ws.Range("A" & iCtr).Value = NewString
Next