VBA to Autofill in ActiveCell

[Thuynh08]

Hi,

I'm trying to Autofill a formula after i've found the heading "Material" and inserted a column after it. Basically i want this new column to use the formula =left("material codes", 13). I've done that much but now i can't autofill the destination to the last occupied row. Please help anyone

Thank you.

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = "Material"
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("A:N")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Material ID"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],13)"

I'm stuck here to autofill the column...

End Sub

 

[mikerickson]

With ActiveCell
    With Range(.Cells, .EntireColumn.Cells(Rows.Count,1).End(xlUp))
        .FormulaR1C1 = "=LEFT(RC[-1],13)"
    End With
End With

 

[dine]

Please try the below Code

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = "Material"
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("A:N")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
cl = ActiveCell.Column
lr = Cells(Rows.Count, cl).End(xlUp).Row
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Material ID"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],13)"
fr = ActiveCell.Row
Range(Cells(fr, cl + 1), Cells(lr, cl + 1)).FillDown
End Sub

Cheers,
Dine

 

[Thuynh08]

Omg thank you soooooo much!!!