# If Cell Left of Selected Cell is Blank, then Sum Offset of (,-3)+(,-2) else (,-1)

#### MikeL

##### Active Member
Hello,
I have selected last nonblank cell in Row 3.
'Range End Method last blank cell in Row 3
Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Select

Would like If Then with logic like:
If Offset(,-1) is 0 Then Sum (offset(,-3)+ (,-2) else just Offset(,-1)

How to write this in VBA?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Crystalyzer

##### Well-known Member
VBA Code:
``````Sub LastBlankCellToLeft()
Dim c As Range
'Range End Method last blank cell in Row 3
Set c = Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1)

If c.Offset(0, -1).Value = 0 Then
c.Formula2R1C1 = "=RC[-3]+RC[-2]"
Else
c.Formula2R1C1 = "=RC[-1]"
End If
End Sub``````

#### footoo

##### Well-known Member
VBA Code:
``````Sub v()
With Cells(3, Columns.Count).End(xlToLeft).Offset(, 1)
If .Offset(, -1) <> 0 Then .Value = .Offset(, -1) Else .Value = .Offset(, -3) + .Offset(, -2)
End With
End Sub``````

#### MikeL

##### Active Member
VBA Code:
``````Sub LastBlankCellToLeft()
Dim c As Range
'Range End Method last blank cell in Row 3
Set c = Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1)

If c.Offset(0, -1).Value = 0 Then
c.Formula2R1C1 = "=RC[-3]+RC[-2]"
Else
c.Formula2R1C1 = "=RC[-1]"
End If
End Sub``````
Thanks I got error 438 on this line c.Formula2R1C1 = "=RC[-1]"

#### Crystalyzer

##### Well-known Member

Thanks I got error 438 on this line c.Formula2R1C1 = "=RC[-1]"
is the empty cell by chance in column A?

#### MikeL

##### Active Member
VBA Code:
``````Sub v()
With Cells(3, Columns.Count).End(xlToLeft).Offset(, 1)
If .Offset(, -1) <> 0 Then .Value = .Offset(, -1) Else .Value = .Offset(, -3) + .Offset(, -2)
End With
End Sub``````
Thanks - this works. How do I autofill down?

#### MikeL

##### Active Member

is the empty cell by chance in column A?
Yes, also how can I autofill down

#### footoo

##### Well-known Member
VBA Code:
``````Sub v()
Dim col&, lr&, rng As Range
col = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = Range(Cells(3, col), Cells(lr, col))
rng.FormulaR1C1 = "=IF(RC[-1]=0,RC[-3]+RC[-2],RC[-1])"
rng = rng.Value 'Omit this line if you want to keep the formula
End Sub``````

#### MikeL

##### Active Member
VBA Code:
``````Sub v()
Dim col&, lr&, rng As Range
col = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = Range(Cells(3, col), Cells(lr, col))
rng.FormulaR1C1 = "=IF(RC[-1]=0,RC[-3]+RC[-2],RC[-1])"
rng = rng.Value 'Omit this line if you want to keep the formula
End Sub``````
Thanks

Replies
11
Views
149
Replies
1
Views
66
Replies
2
Views
60
Replies
2
Views
105
Replies
5
Views
87

### Forum statistics

1,144,617
Messages
5,725,326
Members
422,615
Latest member
Mareza ### 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.

### Which adblocker are you using?    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

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