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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

**Hammer_757** · 10-09-2006, 02:10 PM

Actualy that was a problem with the named range, not the code, now that its fixed rowCount loads just fine, with the correct value.

Robert

**Hammer_757** · 10-09-2006, 02:43 PM

I still have the problem (Application-defined or object-defined error. at:
rowCount = ActiveSheet.Range(name2).Value)

However, “Range” comes from a different worksheet, “BinSize”. (“Range” IS a global named range)

If worksheet "BinSize" is selected in excel and the code is ran, it works fine. If the correct worksheet, Htestc, is selected the error occurs.

I tried changing the line to:

rowCount = ActiveWorkbook.Range(name2).Value

and get: “Object doesn't support this property or method (Error 438)”

Im getting beat up by somethin that seems pretty simple

**theDude** · 10-09-2006, 05:57 PM

Replace this line of code:

Please Login or Register  to view this content.

with this line of code:

Please Login or Register  to view this content.

Replace this line of code:

Please Login or Register  to view this content.

with this line of code:

Please Login or Register  to view this content.

Hope this helps,
theDude

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

yes, it works.
Thanks for the help and education!

variable fill range

LinkBack

Thread Tools

Rate This Thread

Display

variable fill range

Inserting named formulas in cells using VB

Correction to code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions