whats wrong

[Karol_tom]

Hi,

I want to copy some information from pasted in inputbox
all pasted information have format:
xxxxxx COMPANY_NAME xxxxxx x x xxxxxxxx
where company name can contain one space, two or no space

I have big problem - I cant read properly company name - what's wrong ?



Dim zmienna As String
Dim n(1 To 13), z As Integer

zmienna = Application.InputBox("Podaj warto¶æ zmiennej", "zmienna", "111111
gulacz company sc 111114 S 9 PL7755533")

If Len(zmienna) < 6 Then
MsgBox "Nieprawid³owe dane", vbCritical
Exit Sub
End If

'find all spacebar
z = 1
For i = 1 To Len(zmienna)
If ((Mid(zmienna, i, 1)) = " ") Then
n(z) = i
z = z + 1
End If
Next i

adr_number = Left(zmienna, n(1) - 1)
If Len(adr_number) <> 6 Then
MsgBox "co¶ tu jest nie tak - adress number ró¿ny od 6 znaków", vbCritical
Exit Sub
End If

MsgBox ("Pierwsza czê¶æ" & adr_number)
'Tax-ID
tax = Right(zmienna, Len(zmienna) - n(z - 1))
MsgBox ("ostatnia czê¶æ" & tax & "OK")
'company name
Al_name = Mid(zmienna, n(1), (Len(zmienna) + 2 - (n(z - 4) + n(1))))

MsgBox ("company name" & Al_name & "OK")

 

[Dave Peterson]

If you're using xl2k or higher, you can use something like:

Option Explicit
Sub testme()

Dim mySplit As Variant
Dim iCtr As Long
Dim myStr As String
Dim CompanyName As String

myStr = Application.InputBox("Podaj wartos'c' zmiennej", "zmienna", _
"111111 gulacz company sc 111114 S 9 PL7755533")

If Trim(myStr) = "" Then
Exit Sub
End If

'remove extra spaces
myStr = Application.Trim(myStr)

mySplit = Split(myStr, " ")

If (UBound(mySplit) - LBound(mySplit) + 1) < 6 Then
MsgBox "Not enough pieces"
Exit Sub
End If

'xxxxxx COMPANY_NAME xxxxxx x x xxxxxxxx
'ignore first string and last 4 strings
CompanyName = ""
For iCtr = LBound(mySplit) + 1 To UBound(mySplit) - 4
CompanyName = CompanyName & " " & mySplit(iCtr)
Next iCtr

'remove leading space
CompanyName = Mid(CompanyName, 2)

MsgBox CompanyName

End Sub

VBA's Split was added with xl2k. But there are ways to split that string in
xl97, too. Post back if you need to support xl97.

 

[Karol_tom]

I use xl2k, but I don't understand this...and how can i get value from right
to first space (in this case PL7755533)

 

[Dave Peterson]

The code splits the string into pieces based on the space characters (after
extra spaces have been removed).

Then it ignores the first piece and the last 4 pieces based on your layout of
the string:
xxxxxx COMPANY_NAME xxxxxx x x xxxxxxxx

So if you had 133 pieces, it would grab pieces 2-129 (ingnoring pieces 1, 130-133).

 

[Karol_tom]

I use xl2k, but I don't understand this...and how can i get value from
right
to first space (in this case PL7755533)

Okay, I discover it:
prawy = UBound(mySplit)
Tax = mySplit(prawy)

 

[Dave Peterson]

Ahh.

Now I understand!


or just
tax = mysplit(ubound(mysplit))
and the one before:
something = mysplit(ubound(mysplit)-1)

(and so forth)