Need help with some code

[Masta]

Private Sub CommandButton207_Click()
TextBox57.Paste

Application.Wait (Now + TimeValue("0:00:01"))

Dim ws As Variant
Dim iSplit As Integer
Dim kucniBroj As String
Dim kb As Variant
Set ws = Worksheets("Prevod")
'Application.Wait (Now + TimeValue("0:00:01"))
ws = Split(ws.Range("K75").Text, ",")
TextBox58 = ws(0) & "-" & ws(1)
iSplit = InStrRev(ws(2), " ")
TextBox59 = Trim(Left(ws(2), iSplit - 1))
kucniBroj = Trim(Mid(Trim(ws(2)), iSplit - 1))
'kb = Split(kucniBroj, "/")
iSplit = InStr(kucniBroj, "/")
TextBox4 = Trim(Left(kucniBroj, iSplit - 1))
TextBox5 = Trim(Mid(kucniBroj, iSplit + 1))

TextBox58.SelStart = 0
TextBox58.SelLength = TextBox58.TextLength
TextBox58.Cut

TextBox2.SetFocus
TextBox2.SelStart = 0

TextBox2.Paste
TextBox58.SelStart = 0

TextBox59.SelStart = 0
TextBox59.SelLength = TextBox59.TextLength
TextBox59.Cut

TextBox3.SetFocus
TextBox3.SelStart = 0

TextBox3.Paste
TextBox59.SelStart = 0
CommandButton207.Enabled = False

End Sub

This is part of my coding for sorting street adresses and so far i have sucess with this part then i stuck on one problem.
This is original data that this part of code splitting and this woork good with this code:
BEOGRAD, STARI GRAD, CARA DUŠANA 021/33/28

But then i have other itteration like this
BEOGRAD, STARI GRAD, CARA DUŠANA 180
I got error , run-time error '5' Invalid procedure call or argument.

I think its problem with slash (/), i was try to do IF statement but with no luck.
Please i need help. Im not expirienced with excel vba on pro level.

 

[gravanoc]

Hello. Your issue is that you are trying to use a Variant to hold both a Worksheet object & a string. You need to declare a separate string array variable.
Now, replace ws = Split... with str = Split...

Dim str() As String
. . .
str = Split(ws.Range("K75").Text, ",")

 

[Snakehips]

@Masta It isn't good practice to declare ws as variant and use it it in that way but it is not your core issue.

Your problem is when your second iSplit does not find a slash and therefore retains the value 0 which Left and Mid will not like.

Try like this....

xBox59 = Trim(Left(ws(2), iSplit - 1))
kucniBroj = Trim(Mid(Trim(ws(2)), iSplit - 1))
'kb = Split(kucniBroj, "/")


iSplit = InStr(kucniBroj, "/")

Select Case iSplit
Case 0
'Do Whatever with that plain number string
MsgBox "There are no slashes!!"
Case Else
TextBox4 = Trim(Left(kucniBroj, iSplit - 1))
TextBox5 = Trim(Mid(kucniBroj, iSplit + 1))
End Select
TextBox58.SelStart = 0
TextBox58.SelLength = TextBox58.TextLength
TextBox58.Cut

Hope that helps.

 

[Masta]

Hello. Your issue is that you are trying to use a Variant to hold both a Worksheet object & a string. You need to declare a separate string array variable.
Now, replace ws = Split... with str = Split...

Dim str() As String
. . .
str = Split(ws.Range("K75").Text, ",")

No luck with that. Not working. But thank you for trying to help.

 

[Masta]

@Masta It isn't good practice to declare ws as variant and use it it in that way but it is not your core issue.

Your problem is when your second iSplit does not find a slash and therefore retains the value 0 which Left and Mid will not like.

Try like this....

xBox59 = Trim(Left(ws(2), iSplit - 1))
kucniBroj = Trim(Mid(Trim(ws(2)), iSplit - 1))
'kb = Split(kucniBroj, "/")


iSplit = InStr(kucniBroj, "/")

Select Case iSplit
Case 0
'Do Whatever with that plain number string
MsgBox "There are no slashes!!"
Case Else
TextBox4 = Trim(Left(kucniBroj, iSplit - 1))
TextBox5 = Trim(Mid(kucniBroj, iSplit + 1))
End Select
TextBox58.SelStart = 0
TextBox58.SelLength = TextBox58.TextLength
TextBox58.Cut

Hope that helps.

This solution is now half-done, the first result is a good BELGRADE, OLD TOWN, CARA DUSANA 021/33/28 but when the second result is BELGRADE, OLD TOWN, CARA DUSANA 180 I get only BELGRADE, OLD TOWN, CARA DUSANA no number at the end .
Maybe some other idea how to approach solving this problem?

 

[Snakehips]

With respect, only you know what you want to do with that plain number.

If there are no slashes then iSplit will be 0 and the first case, Case 0, of the Select Case will be processed.
The plain number will be the value of your variable - kucniBroj so use that as you wish. I assume you will want to assign it to one or other of your text boxes?

kucniBroj = Trim(Mid(Trim(ws(2)), iSplit - 1))
'kb = Split(kucniBroj, "/")


iSplit = InStr(kucniBroj, "/")

Select Case iSplit
Case 0  'No slashes found so do....
'Do Whatever with that plain number string
'Eg 
TextBox4 = kucniBroj   '?????????

Case Else  'Slashes found so do...
TextBox4 = Trim(Left(kucniBroj, iSplit - 1))
TextBox5 = Trim(Mid(kucniBroj, iSplit + 1))
End Select

 

[Masta]

With respect, only you know what you want to do with that plain number.

If there are no slashes then iSplit will be 0 and the first case, Case 0, of the Select Case will be processed.
The plain number will be the value of your variable - kucniBroj so use that as you wish. I assume you will want to assign it to one or other of your text boxes?

kucniBroj = Trim(Mid(Trim(ws(2)), iSplit - 1))
'kb = Split(kucniBroj, "/")


iSplit = InStr(kucniBroj, "/")

Select Case iSplit
Case 0  'No slashes found so do....
'Do Whatever with that plain number string
'Eg
TextBox4 = kucniBroj   '?????????

Case Else  'Slashes found so do...
TextBox4 = Trim(Left(kucniBroj, iSplit - 1))
TextBox5 = Trim(Mid(kucniBroj, iSplit + 1))
End Select

WOAH!!! GREAT This solve all my problems that i have with this!!! Thank you - thank you!