variable fill range

**Hammer_757** · 10-05-2006, 09:07 AM

Using VBA I need to paste a named formula “BinTest” into a range of cells in a column. Starting at A37. The length of the range of cells is dependant upon the value of another name “Range”. I want this to fire when the worksheet is selected if that is possible.

I think I need to use a fill function and somehow check the value of “Range” and then say fill down from A37 but I have no idea where to start.

Any help, code examples, etc. will be appreciated

Robert

**theDude** · 10-06-2006, 07:48 PM

My solution will run when you select the worksheet if you copy this code to the worksheet object you want to run it against. It assumes that your name 'Range' is just an integer value that equals the number of rows you want to insert the named 'BinTest' formula starting at cell A37:

Private Sub Worksheet_Activate()
Dim name1, name2, varRange, c, rowCount

' Assign named formulas to variables...
name1 = ActiveWorkbook.Names("BinTest")
name2 = ActiveWorkbook.Names("Range")
' Get value of named 'Range' workbook object- strip off the '=' sign...
rowCount = Int(Mid(name2, 2, 5))
' Ensure Excel worksheet row limit isn't exceeded...
If rowCount > 65500 Then
     MsgBox "Your value for 'Range' exceeds the row limit for EXCEL worksheets."
     Exit Sub
End If
' Define the range of cells to insert the named "BinTest" formula
' Start at cell A37 & add the value of name "Range" to the row to include in range of cells...
varRange = ActiveSheet.Cells(37, 1).Address & ":" & Cells((37 + rowCount), 1).Address

' Insert the "BinTest" formula in each cell of the range...
For Each c In Range(varRange)
    c.Formula = name1
Next
End Sub

Hope this helps,
theDude

**Hammer_757** · 10-07-2006, 03:51 PM

thanks Dude, ill be trying that out somethme this weekend

**Hammer_757** · 10-08-2006, 10:04 PM

Dude, I get a runtime error 13, "Type mismatch", at the line:
rowCount = Int(Mid(name2, 2, 5))

I think is because "Range" is actualy a named range (cell) with a simple formula in it that calculates the "range" value.
So far, I haven been able to resolve it.

**theDude** · 10-09-2006, 12:52 PM

Sorry about that...try this instead. I highlighted the corrections for clarity:

Private Sub Worksheet_Activate()
Dim name1, name2, varRange, c

' Assign named formulas to variables...
name1 = ActiveWorkbook.Names("BinTest")
name2 = ActiveWorkbook.Names("Range")
' Get value of named 'Range' workbook object- strip off the '=' sign...
RowCount = ActiveSheet.Range(name2).Value
' Ensure Excel worksheet row limit isn't exceeded...
If RowCount > 65500 Then
     MsgBox "Your value for 'Range' exceeds the row limit for EXCEL worksheets."
     Exit Sub
End If
' Define the range of cells to insert the named "BinTest" formula
' Start at cell A37 & add the value of name "Range" to the row to include in range of cells...
varRange = ActiveSheet.Cells(37, 1).Address & ":" & Cells((37 + Range(name2).Value), 1).Address

' Insert the "BinTest" formula in each cell of the range...
For Each c In Range(varRange)
    c.Formula = name1
Next
End Sub

theDude

**Hammer_757** · 10-09-2006, 01:57 PM

thanks Dude, but now I get a" Runtime error 1004, Application-defined or object-defined error. at:

rowCount = ActiveSheet.Range(name2).Value

I noticed in the Locals Window that name2 value is "="BinSize!$B$26""
the equal sign and double quotes dont look right?

I tried to mimic your first code with
rowCount = ActiveSheet.Range(Mid(name2, 2, 5)).Value
to strip the equals sign off, but I still get the problem