Transfer Information from a multiline textbox to multiple cells - VBA - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

Sök Forumets startsida

Forum

Diskussioner

VBA

Transfer Information from a multiline textbox to multiple cells

Föregående

Nästa

2016-04-02 12:39

VBA-Lover

Medlem sedan: 2016-04-02

1 inlägg

Transfer Information from a multiline textbox to multiple cells

Hi I have created a UserForm with a text box. This is being used to create a list with numbers that a Loop later will populate other information with I want each number to be inserted on a seprate Row, so I can move this information in to a WorkScheet where each number is on it´s own cell. I´m using this code: Dim strCu() As String Dim i As Long strCu = Split(CUNUMBER.Text, Chr(10)) For i = 0 To UBound(strCu) CU.Cells(2 + i, 1) = strCu(i) Next i In the next Column i want to use this digits to create a number using this formula: Cells(2, 2).FormulaLocal = "=IF(LEN(A2)=6;CONCATENATE(""CUSTOMER"";A2);""Wrong"")" Cells(2, 2).AutoFill Destination:=Range("B2:B" & Rows.Count).End(xlUp).Row In this formula is a check that the number is 6 digits long to check that information that the user has put in is according to standard. The problem is that all rows, except the last one, is being stored as text and LEN thinks it contains 7 digits. Last Rows is stored as a Value and contians 6 digits, as all cell should bee. I also want the strCu loop to skip a row if it´s empty, but I can´t make it work. I have tried If strCu = "", <>"", =Char(10), =vbCrLf. It must be some hidden character that i don´t se. I have also tried =LEN(Trim(A2)) and it still says that it´s 7 digits, when I only se 6. Help appreciated. You can answer is Swedish if it´s easier :-)

2016-04-06 08:28

Christian

Medlem sedan: 2015-08-26

1209 inlägg

Re: Transfer Information from a multiline textbox to multiple cells

Whenever a split function finds an empty space, it will return a zero lenght element (""). Which is why i think your on the right track about the hidden chars. If stru(i) = "" within your loop should work to find the empty elements. I found a code for you that will clean strings, simply create a new string variable that you store the return value in within your loop. Such as: myCleansedElement = Cleanstring(strCu(i))

Good luck /chris

Function CleanString(StrIn As String) As String
' "Cleans" a string by removing embedded control (non-printable)
' characters, including carriage returns and linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
If Asc(Mid(StrIn, iCh, 1)) < 32 Then
'remove special character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh

End Function

Sida 1 av 1

Transfer Information from a multiline textbox to multiple cells