Excel VBA With Range --- End With string manupulation help

  • Hi
    I am trying to tidy up the text within a range

    Code
    With Range("C1:F102")
     
            .Value = Application.Proper(.Value)   
       
    End With


    The above works.


    If I put MsgBox .value inside the block it comes up with type miss match error.


    What i am trying to do is i need to get rid of any "," at the end of all strings. I need t get the string length and reduce the font size by 1pt (now its 8) if the lenght of the string is over 43. Also I want to split up the text to different cells if the string lenght is over 43 and there is a coma in the string and the adjacent cell to the right is empty. I dont know if i am asking for too much.


    Please help

  • Re: Excel VBA With Range --- End With string manupulation help


    ichayan,
    Welcome on board but don't forget to use code tags when posting code: I added them for you this time;
    What about a short sample of your file to show how the data is and what is the final result you want
    PCI

    Triumph without peril brings no glory: Just try

  • Re: Excel VBA With Range --- End With string manupulation help


    For the first part of your question, I'm not entirely what you are trying to do with the MsgBox. Do you mean return a value from the MsgBox? In which case you would use an InputBox.


    But for the second part of your question I believe you are looking for the TRIM and SPLIT functions. Here is a page that includes explanations of both and many other forms of string manipulation: http://msdn.microsoft.com/en-u…ry/aa903372(v=vs.71).aspx

  • Re: Excel VBA With Range --- End With string manupulation help


    Quote

    If I put MsgBox .value inside the block it comes up with type miss match error.


    How are you returning a value from a MsgBox?

    Code
    x = MsgBox("Continue", vbYesNo)


    Quote

    get rid of any "," at the end of all strings


    Code
    If Mid(x, Len(x), 1) = "," Then
        x = Replace(x, x, Mid(x, 1, Len(x) - 1))
    End If
    MsgBox x


    Quote

    reduce the font size by 1pt (now its 8) if the lenght of the string is over 43


    Test for length with (you would probably have to loop through range)

    Code
    If Len(Range(....)) > 43 then
    Range(...).Font.Size = 7


    Quote

    split up the text to different cells if the string lenght is over 43 and there is a coma in the string and the adjacent cell to the right is empty


    I am using static ranges however you will get the point - I assume you want it split at comma (and assume only one comma)

    Code
    Dim varArr As Variant
    If Len(Range("G6")) > 43 And InStr(Range("G6"), ",") > 0 And Range("G6").Offset(, 1) = vbNullString Then
        varArr = Split(Range("G6"), ",")
        Range("G6") = varArr(0)
        Range("H6") = varArr(1)
    End If

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Excel VBA With Range --- End With string manupulation help


    Thank you all for valuable suggestions. I have done all that is required using for loops. Sorry I think I didnt put my question right. In the code I posted above, I was trying to do String manipulation when I am in the "with block". Please correct if I am wrong, "With range(range)" will traverse through each cell in the range one by one and ".value" is the value inside the current cell . When I was trying to do Len(.value) it was coming up with type mismatch error. So I tried to print the value useing MsgBox .value which also returned a typ mismatch error. My question is how do I paly around with the string values within the loop.

  • Re: Excel VBA With Range --- End With string manupulation help


    Your example is not a "classic" loop - you would need to

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!