VBA - Entering formula

superfb

Active Member
Joined
Oct 5, 2011
Messages
251
Office Version
  1. 2007
Platform
  1. Windows
Hi,

The below code i have had helped with in a previous thread. However, i am trying to enter a formula in the last row of data in Col C but it isnt entering it.

Additionally when this data has been enter i would like it to be copied and pasted values and the subsequent column D - 0 the values to be cleared.

Many thanks


VBA Code:
Sub Macro4()
'
With Range("C" & Rows.Count).End(xlUp)
      .Offset(, -2).Resize(, 14).Copy
      .Offset(, -2).Insert xlDown
   End With
   
 With Range("C" & Rows.Count).End(xlUp).Select
 
 FormulaR1C1 = "=IF(R[-1]C=""Mar"",""Jun"",IF(R[-1]C=""Jun"",""Sep"",IF(R[-1]C=""Sep"",""Dec"",IF(R[-1]C=""Dec"",""Mar""))))"
  End With
 
 
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
It should be:

VBA Code:
With Range("C" & Rows.Count).End(xlUp)
 
.FormulaR1C1 = "=IF(R[-1]C=""Mar"",""Jun"",IF(R[-1]C=""Jun"",""Sep"",IF(R[-1]C=""Sep"",""Dec"",IF(R[-1]C=""Dec"",""Mar""))))"
.Value = .Value
End With

I don't know what this means:

and the subsequent column D - 0 the values to be cleared
 
Upvote 0
It should be:

VBA Code:
With Range("C" & Rows.Count).End(xlUp)
 
.FormulaR1C1 = "=IF(R[-1]C=""Mar"",""Jun"",IF(R[-1]C=""Jun"",""Sep"",IF(R[-1]C=""Sep"",""Dec"",IF(R[-1]C=""Dec"",""Mar""))))"
.Value = .Value
End With

I don't know what this means:
Thank you!

"and the subsequent column D - 0 the values to be cleared"

The month is in column C, i basically wanted another step to clear out the contents/data in those columns after copying and pasting the last row
 
Upvote 0
The entire column, or just the same row as the formula went into?
 
Upvote 0
You can use:

Code:
With Range("C" & Rows.Count).End(xlUp)
 
.FormulaR1C1 = "=IF(R[-1]C=""Mar"",""Jun"",IF(R[-1]C=""Jun"",""Sep"",IF(R[-1]C=""Sep"",""Dec"",IF(R[-1]C=""Dec"",""Mar""))))"
.Value = .Value
Intersect(.Entirerow, range("D:L,O:O")).Clear
End With
 
Upvote 0
Solution

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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