autofill formula's and paste as value

NatureGreen

New Member
Joined
Mar 12, 2019
Messages
7
Greetings

I'm a vba beginner and trying to make a macro for my monthly Excel tasks.

Every month i get a report. I add a colum but can't do that with vba cause the report has a merged cell in the first row over half of our sheet. So i solved that with adding cells. Got 3 sheets open while working on this one so i need to use ActiveSheet.

Sub McrTest()
ActiveSheet.Range("F5", ActiveSheet.Range("F5").End(xlDown)).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Range("F5").Select
ActiveCell.Value = "NewColumName"
ActiveSheet.Range("F6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,10)"

So far so good. But now i need to autofill the formula down to the last row too and then set the colum to just value.

i have to do this with 2 colums in this sheet cause i also have to add a concatenate formula.

Thanks in advance.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following :

Code:
Sub McrTest()
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F5").Value = "NewColumName"
    Range("F6").FormulaR1C1 = "=MID(RC[-1],1,10)"
    Range("F6").Copy Destination:=Range("F7:F" & last)
End Sub

Hope this will help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub Naturegreen()
   Columns(6).Insert
   Range("F5").Value = "New Column"
   With Range("F6", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
      .FormulaR1C1 = "=mid(rc[-1],1,10)"
      .Value = .Value
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top