whats wrong

  • Thread starter Thread starter Karol_tom
  • Start date Start date
K

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")
 
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.
 
I use xl2k, but I don't understand this...and how can i get value from right
to first space (in this case PL7755533)
 
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).
 
I use xl2k, but I don't understand this...and how can i get value from
right
to first space (in this case PL7755533)
Click to expand...

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

Now I understand!


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

(and so forth)
 
Back
Top