Find the last textbox with value in userform...VBA

[RobbieC]

Hi there, I have a userform with 10 text input fields.

Each is named txt1 txt2 txt3.... txt10

The user can enter info into any of the boxes, but not necessarily all of them...

When the user clicks the UPDATE button, it creates a string of the values and inserts a comma if <>"" :

Dim TotalString As String
TotalString = ""
Dim txtVar As String


Dim txtNumVar(1 To 10) As String


For i = 1 To 10
    txtVar = "txt" & i
    txtNumVar(i) = Me.Controls(txtVar).Value
    If txtNumVar(i) <> "" Then txtNumVar(i) = ", " & txtNumVar(i)
    TotalString = TotalString & (txtNumVar(i))
Next

This all works fine, but I'd like to replace the final comma in the final TotalString with " and"

TotalString = TotalString (find last "," and replace with " and")

If you can point me in the right direction, I'd be very grateful

Thanks

 

[Craggs82]

Afternoon,

does this not start the TotalString with ", " ? Is that working as intended?

Regardless, theres probably a few ways to do this.

You could, InStrRev to find the last comma position, then Replace (", " with " and ") and add it onto a Left of the string up to the comma position.

Dim commaPos As Long
commaPos = InStrRev(TotalString, ",")
if commapos > 0 then TotalString = Replace(TotalString, ", ", Left(TotalString, commaPos - 1) & " and ", commaPos)

 

[RobbieC]

Brilliant, thanks mate!