Visual Basic: how can I limit the number of characters in a range

[msmith3]

I have the following code. Within the range B4-P4 are the days of the week (Monday-Sunday) and I'm trying to limit the characters so that it will say (Mon-Sun). I'm not sure if .Left is the right property to use so if anyone has any other ideas, please offer them!


With Range("B4:P4")
.Font.Size = 10
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Left ........... (Help?!)

End With

 

[gordsky]

I have the following code. Within the range B4-P4 are the days of the week (Monday-Sunday) and I'm trying to limit the characters so that it will say (Mon-Sun). I'm not sure if .Left is the right property to use so if anyone has any other ideas, please offer them!


With Range("B4:P4")
.Font.Size = 10
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Left ........... (Help?!)

End With

How are the days of the week entered into the cells. Is it via another code, from a date or are they manually entered?
If from a date then you could use the FORMAT to just have the three letters of each day. Assuming they are entered manually you will need to loop through each cell in the range (if you want to use the left function) something like the below should work.

Set Rng = Range("g2:M2")

For Each cel In Rng
    cel = left(cel, 3)

    With cel
      .Font.Size = 10
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With

Next cel

 

[learnerrr]

If you can't find a real solution, try this little trick.

Dim rng As Range
Set rng = Range("B4:P4")
For Each cell In rng
    With rng.Characters(Start:=4, Length:=Len(cell) - 3).Font
        .ColorIndex = 2
    End With
Next cell

 

[msmith3]

How are the days of the week entered into the cells. Is it via another code, from a date or are they manually entered?
If from a date then you could use the FORMAT to just have the three letters of each day. Assuming they are entered manually you will need to loop through each cell in the range (if you want to use the left function) something like the below should work.

Set Rng = Range("g2:M2")

For Each cel In Rng
    cel = left(cel, 3)

    With cel
      .Font.Size = 10
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With

Next cel

The Macro CreateCalendar creates this:

the code for the days of the week is written in CreateCalendar as:

The results of that macro is then copied into the macro GetCalendars to look like this:

This is the code for GetCalendars:

My goal is to edit the days of the week in the macro GetCalendars to say Mon, Tues, Wed, Thurs, Fri, Sat, Sun rather than the current Monday, Tuesday, Wednesday, etc.

 

[gordsky]

Just replace everything below your line

'Edit the written days of the week

with this and it will work

Set Rng = Range("B4:P4")

For Each cel In Rng
    cel = left(cel, 3)

    With cel
      .Font.Size = 10
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With

Next cel

 

[learnerrr]

Maybe this piece of code is what you want.

Dim rng As Range
Dim cell As Range
Set rng = Union(Range("B5:H5"), Range("J5:P5"))
For Each cell In rng
    With cell
        If .Value = "Tuesday" Then
               .Value = Left(.Value, 4)
        ElseIf .Value = "Thursday" Then
               .Value = Left(.Value, 5)
        Else
               .Value = Left(.Value, 3)
        End If
     End With
Next cell

 

[msmith3]

Maybe this piece of code is what you want.

Dim rng As Range
Dim cell As Range
Set rng = Union(Range("B5:H5"), Range("J5:P5"))
For Each cell In rng
    With cell
        If .Value = "Tuesday" Then
               .Value = Left(.Value, 4)
        ElseIf .Value = "Thursday" Then
               .Value = Left(.Value, 5)
        Else
               .Value = Left(.Value, 3)
        End If
     End With
Next cell

That worked!! Thank you so much you saved me from a major headache.