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".

 

[GWteB]

Put a missing End If right above the End With.

 

[288enzo]

Put a missing End If right above the End With.

Thanks, I made a few changes and am getting the same end with without with

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
    If Value = """" Then
    .Formula = "=LEFT(C4,3)"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    End If
End With

 

[GWteB]

You made just one change too many. Keep the red colored one (as I suggested) & get rid of the striked one ...

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
    If Value = """" Then
    .Formula = "=LEFT(C4,3)"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    End If
End With

 

[288enzo]

You made just one change too many. Keep the red colored one (as I suggested) & get rid of the striked one ...

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
    If Value = """" Then
    .Formula = "=LEFT(C4,3)"
    .Value = Range("E4")
    .HorizontalAlignment = xlLeft
    End If
End With

I had tried that after your suggestion, but got the message "object required" which made me think I need another "if".

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 If
End With

 

[GWteB]

Hadn't looked closely at your code before, but what you're probably trying to achieve won't work this way (with temporary use of formulas), at least not in a relatively easy to code way.
Alternatively you could use the code below

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

 

[288enzo]

I removed .Select and after running it all data in E was cleared

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

I also tried it like this, and had the same results.

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

 

[GWteB]

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

 

[288enzo]

Hadn't looked closely at your code before, but what you're probably trying to achieve won't work this way (with temporary use of formulas), at least not in a relatively easy to code way.
Alternatively you could use the code below

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

I like what you did using offset, thank you. The problem is that your code also clears out all data in E.

I would have used ISBLANK, but for whatever reason the cell that looks blank isn't technically blank. I ran ISBLANK and the cell came up with FALSE, so I have confirmed something is there. That's why the IF statement is IF(E4="""") THEN

My original code was specific to one cell:

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

I'm trying to apply the above to an entire range in column E.

Hopefully that makes more sense.

I'm terrible at explaining.

Basically, if any cell in Range("E4:E" & Range("B" & Rows.Count).End(xlUp).Row) is equal to "" then FORMULA.

Thank you

 

[GWteB]

I like what you did using offset, thank you. The problem is that your code also clears out all data in E.

I'm just guessing since I don't know what you're trying to achieve. My post #6 code is my best guess as a derivative of your code in post #1 and results in ...

Book1
	B	C	D	E
4	B4	C4		C4
5	B5			B5
6	B6	C6		C6
7	B7			B7
8	B8	C8		C8
9	B9	C9		C9
ENZO