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

db74

New Member
Joined
Jul 5, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
c.Offset(,-1) &IF(LEN(c.Value)<10 THEN c.Value&Chr(9)&Chr(9)
but it's failing on the first IF. Thanks
 
I wish I could help 😞 but I'm still not sure what you need.

Could you provide an example output? If not Excel, maybe on a piece of paper or written in Word?
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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:
VBA Code:
    c.Offset(, -1).Value = Left(c.Value & Application.WorksheetFunction.Rept(" ", 30), 30)
Thanks again @Joe4 I'll give this a go.

@PeteWright I'm trying to populate a userform treeview with columned data, so taking the previous example data I'm trying to get something like this, with everything to line up.

1658476354293.png
 
Upvote 0
Hi @Joe4. I've tried the Rept code but unfortunately despite the rows containing the same number of characters it doesn't line up. I guess that's due to the size of the characters. Any further ideas would be appreciated. Thanks

1658687992744.png
 
Upvote 0
Just been having a play and it may work with Courier font. Thanks
 
Upvote 0
I've tried the Rept code but unfortunately despite the rows containing the same number of characters it doesn't line up.

Just been having a play and it may work with Courier font.
Yes, that is correct. The choice of font will affect that.
I don't remember which ones of the top of my head work best, but I think Courier may be one of them.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top