VBA Define name

[Heyitslopes]

I'm trying to use the Define name function in excel but I want to use it in VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BidQTY As Range
Dim BidUP As Range
Set BidQTY = Sheets("Job to Date").Range("D1:D10")
Set BidUP = Sheets("Job to Date").Range("E1:E10")
Sheets("Job to Date").Range("F1").Formula = BidQTY * BidUP
End Sub

I have this bit of code right now but i'm getting an error on the last line?

 

[Rick Rothstein]

I'm trying to use the Define name function in excel but I want to use it in VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BidQTY As Range
Dim BidUP As Range
Set BidQTY = Sheets("Job to Date").Range("D1:D10")
Set BidUP = Sheets("Job to Date").Range("E1:E10")
Sheets("Job to Date").Range("F1").Formula = BidQTY * BidUP
End Sub

I have this bit of code right now but i'm getting an error on the last line?

Show us the formula that you want to end up with in cell F1 so we have some idea what you are trying to do.

 

[MickG]

Perhaps:-

 Sheets("Job to Date").Range("F1").Value = Application.Product(Union(BidQTY, BidUP))

 

[Heyitslopes]

That gave me the answer 128595600000000000000 what i'm actually trying to do is define the ranges and then have F1:F10 be the products of the corresponding cells IE F2=D2*E2 going down

 

[Rick Rothstein]

That gave me the answer 128595600000000000000 what i'm actually trying to do is define the ranges and then have F1:F10 be the products of the corresponding cells IE F2=D2*E2 going down

I am not sure exactly what you are doing or why you are doing this in a SheetChange event (it looks like you are trying to put the same formula into the range over and over again as various sheets are selected), but wouldn't it be simpler to just put the formulas in cells F1:F10 directly as they would then update automatically whenever necessary?

 

[XonsArgi]

Let me help here. When you use the formula property of a range it is a string like "=SUM(A1:A10)"
You are tryin to make a multiplication of ranges in VBA, wich doesn't work already and put to a property taht won't get what you are triyn...
What I would suggest is, you first make a function in a module on your VBA project that multiply a range in the form that you want or use the one that already exists, since you didn't specify how you would like it to multiply.
Something like this

Sheets("Job to Date").Range("F1").Formula = "=PRODUCT(BidQTY , BidUP)"

didn't test it, but I believe a named range must be a public variable on the worksheet it'll be used. Or you can use the namin' range of excel. Hope it works for you.