• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

Sending a range to function

Private Function FormulaFiller(idata As String, istart As String, iend As String)
    
    Dim rgCopy As Range, rgDelete As Range, rgFill As Range, rgClear as range
    
    Set rgCopy = ["istart"&:&"iend"]    'Formulas in these cells will be copied down
    Set rgFill = ["idata"]       'First piece of raw data

    set rgClear = range( ' need to clear the data/formula below istart and iend 
    Set rgFill = Range(rgFill, rgFill.Worksheet.Cells(Rows.Count, rgFill.Column).End(xlUp))  'All data in that column
    Set rgFill = rgCopy.Resize(rgFill.Rows.Count)       'Fill formulas down in this range
    
    Set rgDelete = rgCopy.Offset(1, 0).Resize(rgCopy.Worksheet.UsedRange.Rows.Count)
    rgDelete.ClearContents
    
    rgCopy.AutoFill rgFill, xlFillCopy
    Application.CutCopyMode = False

End Function

Open in new window



Hi,

I have the about function. However, I am getting an object not found error on these lines

    Set rgCopy = ["istart"&:&"iend"]    'Formulas in these cells will be copied down
    Set rgFill = ["idata"]       'First piece of raw data

    set rgClear = range( ' need to clear the data/formula below istart and iend

As you can see, I am getting a range from different function and trying to 'pull down' formulas. But I am having hard time bringing in ranges as string :/
If you see this thread, you will understand: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27383431.html

I also need to clear the space below the initial formulas before those fields get populated. I started on a line but my solutions are not working/giving errors.

Any help is much appreciated!
Thanks!
0
Shanan212
Asked:
Shanan212
1 Solution
 
Rory ArchibaldCommented:
Try using Evaluate:

Set rgCopy = Evaluate(istart & ":" & iend)
0
 
Shanan212Author Commented:
Thanks that worked!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now