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

What is the formula for "BinTest"?


wrote:
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

named foumula "BinTest":
=IF(COUNT(BINFilStart:A)<INDIRECT($C$114&"BinCount "&$D$114),A113+INDIRECT($C$114&"BinSize"&$D$114)," ")

Thanks
Robert

Sandy wrote:
What is the formula for "BinTest"?

wrote:
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

Here some code that might help start you off. This code takes the named
range "Range" in sheet 1 and when sheet 2 is activated the named range
"Range" is muliplied by 10 and placed in sheet 2 in Column A starting
at row 37 filling down.

Place this code in the "Sheet2" section in the Visual Basic Editor(VBE)

Private Sub Worksheet_Activate()
Dim BinTest As Long
Dim BRange, CRange, i, n As Integer
BRange = 37
CRange = Worksheets("Sheet1").Range("Range").Count
n = 1
For i = BRange To (BRange + (CRange - 1)) Step 1
Worksheets("Sheet2").Cells(i, 1).Value = _
Worksheets("Sheet1").Range("Range").Cells(n, 1).Value * 10
n = n + 1
Next
End Sub

Hope this get you started

Sandy

wrote:
named foumula "BinTest":
=IF(COUNT(BINFilStart:A)<INDIRECT($C$114&"BinCount "&$D$114),A113+INDIRECT($C$114&"BinSize"&$D$114)," ")

Thanks
Robert

Sandy wrote:
What is the formula for "BinTest"?

wrote:
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

Sandy, I just did a quick tes and I should be able to adapt and build
from this.
Thank you very much for your effort
Robert


Sandy wrote:
Here some code that might help start you off. This code takes the named
range "Range" in sheet 1 and when sheet 2 is activated the named range
"Range" is muliplied by 10 and placed in sheet 2 in Column A starting
at row 37 filling down.

Place this code in the "Sheet2" section in the Visual Basic Editor(VBE)

Private Sub Worksheet_Activate()
Dim BinTest As Long
Dim BRange, CRange, i, n As Integer
BRange = 37
CRange = Worksheets("Sheet1").Range("Range").Count
n = 1
For i = BRange To (BRange + (CRange - 1)) Step 1
Worksheets("Sheet2").Cells(i, 1).Value = _
Worksheets("Sheet1").Range("Range").Cells(n, 1).Value * 10
n = n + 1
Next
End Sub

Hope this get you started

Sandy

wrote:
named foumula "BinTest":
=IF(COUNT(BINFilStart:A)<INDIRECT($C$114&"BinCount "&$D$114),A113+INDIRECT($C$114&"BinSize"&$D$114)," ")

Thanks
Robert

Sandy wrote:
What is the formula for "BinTest"?

wrote:
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

so far my adaptation is:

Private Sub Worksheet_Activate()
Dim BinTest As Long
Dim BRange, CRange, i, n As Integer
Dim Htestc As Worksheet

'row count for target location
BRange = 114

'establish target range size based on named range "Range"
CRange = Worksheets("BinSize").Range("Range").Value
n = 1

'paste named formula "BinFill" into range of cells
' starting at 114 with size established by "Range"
For i = BRange To (BRange + (CRange)) Step 1
ActiveSheet.Cells(i, 1).Value = _
ActiveSheet.Names("BinFill").Cells(n, 1).Value
n = n + 1
Next
End Sub

I get the error "Object doesnt support this method..."
I think it means I cant use .names with Activesheet, I had the same
error with Worksheet.

this is just a matter of me not understanding what works with what and
the help info on the name object is kind of lame.