VBA If LEN cell < 10 then, if LEN cell <20 then...

[db74]

Hello. Could anyone point me in the right direction to write a VBA statement based upon the number of characters in a cell. I'm trying to concatenate 5 columns but the second length of the 2nd column could vary, so I was trying to add a certain number of tabs to the column (depending on the size of the cell/text) to create equal spacing. I have already made this work as an Excel formula, but need it in VBA. I've started with

c.Offset(,-1) &IF(LEN(c.Value)<10 THEN c.Value&Chr(9)&Chr(9)

but it's failing on the first IF. Thanks

 

[PeteWright]

Could you share some sample data and the desired output?

 

[Joe4]

You could structure your IF statement something like this in VBA:

If Len(c.Value) < 10 Then
    c.Offset(,-1).Value = c.Value & Chr(9) & Chr(9)
Else
    If If Len(c.Value) < 20 Then
        c.Offset(,-1).Value = c.Value & Chr(9)
    Else
        c.Offset(,-1).Value = c.Value
    End If
End If

 

[db74]

Thanks @Joe4 I'll give that a go. @PeteWright, I've added some test data below and I'm trying to concatenate all the columns but keep the spacing to then insert into a treeview. Not sure if this is possible yet, although I have managed to sort of make it work. If anyone has any better ideas on how to do this then happy to try them. Thanks

Date	Title	Author	Stream	Versions	Document No
16/09/2011	Document One	jpb		2​	600​
16/09/2011	Document Two	jpb		2​	601​
21/09/2011	Document One Hundred and Thirty Eight	jpb		2​	597​
03/11/2011	Document Twenty	jpb	ADM	1​	661​
03/11/2011	Document Fifty Four	jpb		1​	662​

 

[db74]

I've started to try and implement the If statement within the script to populate the treeview parent nodes, but encountering a scripting error. Any ideas if or how I can add the If Statement to the statement below? Thanks

Set nParent = TDocTV.Nodes.Add(, "Parent" + c.Value, c.Value, c.Offset(, -1) + If(LEN(c.Value) <10 THEN c.Value &Chr(9) &Chr(9) &Chr(9) &Chr(9) Else IF(LEN(c.Value) <20 THEN c.Value &Chr(9) &Chr(9)
Else ''
End If
End If

 

[Joe4]

That is NOT how you structure an IF...THEN block in VBA. You are using it like an Excel worksheet function (single line), which does NOT work.

If you use the VBA IF...THEN block, your line MUST begin with IF, like I show. It would be structured something like (fill in the details for each one):

If Len(c.Value) < 10 Then
    Set nParent = ...
Else
    If If Len(c.Value) < 20 Then
            Set nParent = ...
    Else
            Set nParent = ...
    End If
End If

See: If Then Else Statement in Excel VBA (explained with examples)

 

[db74]

That is NOT how you structure an IF...THEN block in VBA. You are using it like an Excel worksheet function (single line), which does NOT work.

If you use the VBA IF...THEN block, your line MUST begin with IF, like I show. It would be structured something like (fill in the details for each one):

If Len(c.Value) < 10 Then
    Set nParent = ...
Else
    If If Len(c.Value) < 20 Then
            Set nParent = ...
    Else
            Set nParent = ...
    End If
End If

See: If Then Else Statement in Excel VBA (explained with examples)

Thanks @Joe4. I tried it a few different ways with blocking the statement, but didn't think to start the If from the start which is where I was going wrong. I'll try it again but appreciate the help as been coding for the last couple of weeks now and my brain is now hurting.

 

[Joe4]

OK, if you run into any more issues, just post back your latest code attempt, and we can see if we can help you fix it.

Note that I have never worked with "TreeView" before (so I cannot help with any specific "TreeView" issues), but I think the issue is mroe with your code structure than with TreeView.

 

[db74]

Thanks again @Joe4, it is now up and running. The only issue I have now is getting tabs to work, as neither vbtab or chr(9) seem to do anything, although space(#) does work in a treeview, so may need to use this but it might get more messy.

 

[Joe4]

Perhaps this might help or give you some ideas on how you can "pad" spaces on the end so that the total length is some specified number.

Let's say that you want "c.Offset(, -1)" to show value fom "c", padded with spaces at the end so that its total length is exactly 30 spaces.
Then you could do something like this:

    c.Offset(, -1).Value = Left(c.Value & Application.WorksheetFunction.Rept(" ", 30), 30)