Microsoft Excel - VBA function in EXCEL. - Asked By Appu on 23-Sep-11 04:53 AM

Hi all,

   If i give test1.2 in a text box and press a button i want to get the test1 and test2 in a result textbox.
i achieved the thing like if i give test 1 in a text box , i am getting test1.1, test1.2, test1.3, test1.4. in a result textbox.
Please guide me to achieve the underlined functionality. i used VBA function in EXCEL.
Thanks in Advance

test1   test1.1
        test1.2
      test1.3
      test1.4

test2   test2.1
      test1.2
      test1.5

I think I understand your test1.2 question, but don't understand how to derive the test2 output in your sample.  Some VBA for the underlined question:

Public Function ParseTest(ByVal ctlCurr As Control) As String

    Dim intPos      As Integer
    Dim strResult     As String
    Dim strBase     As String
    Dim intStart      As Integer
    Dim intEnd      As Integer
    Dim intOut      As Integer

    strResult = ""
    intPos = InStr(1, ctlCurr.Value, ".")
    If intPos > 0 Then
      For intStart = intPos - 1 To 1 Step -1
        If Asc(Mid(ctlCurr.Value, intStart, 1)) < 48 _
        Or Asc(Mid(ctlCurr.Value, intStart, 1)) > 57 Then
          Exit For
        End If
      Next intStart
      strBase = Left(ctlCurr.Value, intStart)
      intStart = Val(Mid(ctlCurr.Value, intStart + 1, intPos - intStart - 1))
      intEnd = Val(Mid(ctlCurr.Value, intPos + 1, Len(ctlCurr.Value) - intPos))
      For intOut = intStart To intEnd
        strResult = strResult & strBase & CStr(intOut) & vbCrLf
      Next intOut
      strResult = Left(strResult, Len(strResult) - 1)
    End If

    ParseTest = strResult

End Function

In my test, I put an afterupdate event on the first text box:

Private Sub TextBox1_AfterUpdate()

    Me.TextBox2.Value = ParseTest(Me.TextBox1)

End Sub