apply if formula to column range

[288enzo]

I have a with statement that is specific to one cell, I would like to apply it to the column.

    With Range("E4")
    .Formula = "=IF(C4="""",LEFT(B4,3),LEFT(C4,3))"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    End With

This is what I came up with, but where I think I'm having an issue is where I'm using a specific cell for the formula and value.

With Range("E4:E" & Range("B" & Rows.Count).End(xlUp).Row).Select
    If Value = """" Then
    .Formula = "=LEFT(B4,3)"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    Else
    .Formula = "=LEFT(C4,3)"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    End With
End Sub

I'm getting an error I can't have an end with without with. My statement starts with "with".

 

[288enzo]

What is your expected outcome in each separate cell in column E?

Sorry, I'm terrible at explaining what I'm trying to accomplish.

If E is blank and C is blank then LEFT(B,2)
If E is blank and C is not blank then LEFT(C,3)

2021_CertReadinessReport_87_87STX_LA_10302021.xls
	B	C	D	E
3	B	C	D	E
4	00 Total
5	00 Group		405C16	Data
6	00 Group		405C17	Data
7	00 Group	00a group	425A01
8	00 Group		425A05	Data
9	00 Group		425A06	Data
10	00 Group		425A10	Data
11	00 Group	00b group	425A16
12	00 Group		425A61	Data
13	00 Group		425A76	Data
Dealer Status

 

[GWteB]

It must be me, but I'm totally lost. From your minisheet I can't tell what the (original) starting situation is and what the final desired situation should be.
If you look at the minisheet of my post #10 you will hopefully see some correlations between what's in columns B and C on the one hand, and the result in target column E on the other. Such an interrelation cannot be deduced from the mini-sheet you posted.

 

[288enzo]

It must be me, but I'm totally lost. From your minisheet I can't tell what the (original) starting situation is and what the final desired situation should be.
If you look at the minisheet of my post #10 you will hopefully see some correlations between what's in columns B and C on the one hand, and the result in target column E on the other. Such an interrelation cannot be deduced from the mini-sheet you posted.

You're right, you're lost because I'm terrible. The first sheet is the before the macro is run, here is what it would look like after.

2021_CertReadinessReport_87_87STX_LA_10302021.xls
	B	C	D	E
3	B	C	D	E
4	00 Total			00
5	00 Group		405C16	Data
6	00 Group		405C17	Data
7	00 Group	00a group	425A01	00a
8	00 Group		425A05	Data
9	00 Group		425A06	Data
10	00 Group		425A10	Data
11	00 Group	00b group	425A16	00b
12	00 Group		425A61	Data
13	00 Group		425A76	Data
Dealer Status

 

[GWteB]

Ok, how about ...

Dim r As Range
For Each r In Range("E3:E" & Range("B" & Rows.Count).End(xlUp).Row)
    If Len(r.Value) = 0 Then
        If r.Offset(, -2).Value = "" Then
            r.Value = Left(r.Offset(, -3).Value, 2)
        Else
            r.Value = Left(r.Offset(, -2).Value, 3)
        End If
        r.HorizontalAlignment = xlLeft
    End If
Next

 

[288enzo]

Ok, how about ...

Dim r As Range
For Each r In Range("E3:E" & Range("B" & Rows.Count).End(xlUp).Row)
    If Len(r.Value) = 0 Then
        If r.Offset(, -2).Value = "" Then
            r.Value = Left(r.Offset(, -3).Value, 2)
        Else
            r.Value = Left(r.Offset(, -2).Value, 3)
        End If
        r.HorizontalAlignment = xlLeft
    End If
Next

As you can imagine what you wrote works perfectly, thank you. Can you help me understand why in one line you have value = 0 and in another you have value = ""?

 

[GWteB]

You are welcome and thanks for letting me know.

Can you help me understand why in one line you have value = 0 and in another you have value = ""?

No special reason, just two different ways of doing the same thing /getting the same result. Although I try to be as consistent as possible in what I'm coding, I've paid less attention to that in this situation, moreover and meanwhile dealing with the question: "what exactly does @288enzo want?"

BTW, I didn't use Value = 0, I used Len(Value) = 0 and that's quite something different.

To determine whether a String variable contains characters at all, you can test whether the length of that text string is greater than 0, and that's what I did in the first comparison.

Other ways are:
If s = "" If s = vbNullString

With a worksheet range there is a fourth option:
If IsEmpty(Range("A1").Value)

 

[288enzo]

You are welcome and thanks for letting me know.


No special reason, just two different ways of doing the same thing /getting the same result. Although I try to be as consistent as possible in what I'm coding, I've paid less attention to that in this situation, moreover and meanwhile dealing with the question: "what exactly does @288enzo want?"

BTW, I didn't use Value = 0, I used Len(Value) = 0 and that's quite something different.

To determine whether a String variable contains characters at all, you can test whether the length of that text string is greater than 0, and that's what I did in the first comparison.

Other ways are:
If s = "" If s = vbNullString

With a worksheet range there is a fourth option:
If IsEmpty(Range("A1").Value)

So much to learn, thank you for taking the time to explain.