Syntax Formula help
 

It doesn't matter whether I use .formulaR1C1 or .value I just can't get
the below syntax right!!!

WRange.Value = "=" & "Ipbmatch(rc[-7]," & (LURange.Address) & ")"

Wrange is a predefined range of cells in one column
IPBMatch is UDF = Public Function IPBMatch(CellRef As Range, SrchVals
As Range) As String
LURange is, again, a predefined range.

I'm sure this is straightforward for somebody that knows what they are
doing but, unfortunately, clearly I don't!

Any help appreciated.

Regards,

Adrian


--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

Syntax Formula help
 

I'd try:

WRange.formular1c1 _
= "=" & "Ipbmatch(rc[-7]," _
& LURange.Address(ReferenceStyle:=xlR1C1, external:=True) & ")"

I removed the extra ()'s surrounding the LURange.address stuff. If that LURange
is a contiguous range, then I would think that you wouldn't need them.

If it's a discontiguous range, you'll want to put them back.

And remember if you're speaking R1C1 reference style, then all the stuff has to
use that reference style.



Kobayashi wrote:

It doesn't matter whether I use .formulaR1C1 or .value I just can't get
the below syntax right!!!

WRange.Value = "=" & "Ipbmatch(rc[-7]," & (LURange.Address) & ")"

Wrange is a predefined range of cells in one column
IPBMatch is UDF = Public Function IPBMatch(CellRef As Range, SrchVals
As Range) As String
LURange is, again, a predefined range.

I'm sure this is straightforward for somebody that knows what they are
doing but, unfortunately, clearly I don't!

Any help appreciated.

Regards,

Adrian

--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

--

Dave Peterson

Syntax Formula help
 

Dave,

Thanks very much! I had to qualify the 'IPBmatch' function with it's
full address (it's in a different workbook) but as soon as I did it
worked a treat!

Clearly it's the '(ReferenceStyle:=xlR1C1, external:=True)' part that
makes this work and which I didn't know had to be included.

One last thing (I promise), I have 'Application.Calculation =
xlCalculationManual' early in my code but as soon as I enter the above
formula, that you helped me with, in the range it automatically starts
calculating?

Any ideas?

Many thanks indeed for your help. I'd have been here all week!


--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

Syntax Formula help
 

You could enter the "formula" as text.

WRange.formular1c1 _
= "$$$$$=" & "Ipbmatch(rc[-7]," _
& LURange.Address(ReferenceStyle:=xlR1C1, external:=True) & ")"

Then right before your code ends, you could add a line that does
Edit|Replace
what: $$$$$=
with: =
replace all

(record a macro to get the code.)

Then the UDF's that you're plopping into the range should update with one
calculation.

Kobayashi wrote:

Dave,

Thanks very much! I had to qualify the 'IPBmatch' function with it's
full address (it's in a different workbook) but as soon as I did it
worked a treat!

Clearly it's the '(ReferenceStyle:=xlR1C1, external:=True)' part that
makes this work and which I didn't know had to be included.

One last thing (I promise), I have 'Application.Calculation =
xlCalculationManual' early in my code but as soon as I enter the above
formula, that you helped me with, in the range it automatically starts
calculating?

Any ideas?

Many thanks indeed for your help. I'd have been here all week!

--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

--

Dave Peterson

Syntax Formula help
 

Dave,

That's excellent and does the trick (although I'm still not sure why
selecting manual calculation doesn't prevent the formula from
automatically calculating?)

Many thanks for all your help!

Adrian


--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

Syntax Formula help
 

When I turn calculation to manual, then type a formula in a single cell, that
formula will evaluate--but the rest of the formulas that depend on that cell
won't.

I would expect that you're seeing the same behavior when you do it via code.



Kobayashi wrote:

Dave,

That's excellent and does the trick (although I'm still not sure why
selecting manual calculation doesn't prevent the formula from
automatically calculating?)

Many thanks for all your help!

Adrian

--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=557868

--

Dave Peterson