0

I'm new to vba and I've done a lot of research online but still can't figure out this one.

Sub DailyFundRecMacro()
Dim rng As Range
Dim wks As Worksheet

 ' Prevents screen refreshing.
Application.ScreenUpdating = False

Worksheets("FIMCO Data").Activate

Set wks = Worksheets("FIMCO Data")
Set rng = wks.Range(("E1"), Range("E1").End(xlDown))

Sheets("Converter").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=rng, Type:=xlFillDefault

'Loop all the cells in range
For Each cell In Range("E1:E398")
    If Application.WorksheetFunction.IsNA(cell) Then
        'If cell contains #N/A, then set the value to 0
        cell.Value = 0
    End If
Next

Range("B1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Trial Balance Values").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

Problematic line:

Selection.AutoFill Destination:=rng, Type:=xlFillDefault

When I Set rng = wks.Range(("E1"), Range("E1").End(xlDown)), the autofill won't work, but when I simply use Selection.AutoFill Destination:=Range(("E1"), Range("E1").End(xlDown)), the code works fine.

Any help is appreciated. Thanks!

dwirony
  • 5,487
  • 3
  • 21
  • 43
Lily Jinjia Li
  • 15
  • 4
  • 1
    There are several issues here - one, your ranges are not qualified in either line, which means they're just referring to whatever the `ActiveSheet` is. `Selection.AutoFill Destination:=Range(("E1"), Range("E1").End(xlDown))` will work for you because the ranges aren' t qualified and are working with `Sheets("Converter")`, but you want those to work with `Sheets("FIMCO Data")`. – dwirony May 24 '18 at 20:01
  • [This might be of interest](https://stackoverflow.com/q/10714251/445425) – chris neilsen May 25 '18 at 00:10

0 Answers0