#NAME Perplexity

[MichaelDavid]

Greetings! This one has me stumped. I wrote the following Procedure:

Sub TestLbName()

Dim LstRowData As Long

LstRowData = [O2]

' NOTE: The follwg straight computation (which is NOT a formula) gives the
correct result!

Cells(LstRowData + 2, "W") = Range("W" & LstRowData) ^ 2 / Range("I4")

' Whereas the following formula will not compile:

Cells(LstRowData + 2, "W").Formula = "= Range("W" & LstRowData) ^ 2 /
Range("I4")"

' and the following formula will compile but gives a #NAME error upon
execution

Cells(LstRowData + 2, "W").Formula = "= (W & LstRowData) ^ 2 / (I4)"

' And if LstRowData is, for example, 115, the following formula gives the
correct result.

Cells(LstRowData + 2, "W").Formula = "= (W115) ^ 2 / (I4)"

' Is there any way of constructing this formula for the general case
(LstRowData not necessarily 115)? The only solution I can presently come up
with is a monster Select Case

End Sub

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Greetings! I should also mention, that if I change the instruction which reads:
LstRowData = [O2]
to
LstRowData = 115
I get identically the same results for each of the instructions.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Rick Rothstein]

Does this do what you want?

Cells(LstRowData + 2, "W").Formula = "=" & Range("W" & LstRowData) & _
"^2/" & Range("I4")

 

[MichaelDavid]

Greetings! At 3:50 AM, I came up with the following possible solution:
I give a name (TotNetPurchs) to the one cell range that is to receive the
formula as follows:

Range("W" & LstRowData).Name = ActiveSheet.Name & "!TotNetPurchs"

Then I use that named range in the formula as follows:

Cells(LstRowData + 2, "W").Formula = "= (TotNetPurchs) ^ 2 / (I4)"

and this places the correct result in the cell without errors. I am looking
forward to receiving any comments or suggestions about my proposed solution.
Thanks!
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[keiji kounoike]

I think your following code

Cells(LstRowData + 2, "W").Formula = "= Range("W" & LstRowData) ^ 2 /
Range("I4")"

should be

Cells(LstRowData + 2, "W").Formula = "= W" & LstRowData & "^ 2/I4"

keiji

 

[MichaelDavid]

Hi Rick:
Thanks for your help. Your solution works. To get its timing, I ran it
100 times. 100 executions took between 0.09375 and 0.109375 secs. Not bad!
When I ran the solution I came up with at 3:58 AM this morning, 100
executions took between 0.125 and 0.1406 secs. Using Names in Excel VBA
results in a heavy time cost. I hope they fix this some day. There is no need
for a 30+ % time cost in using names.
When I then went to actually look at what was in Cells(LstRowData + 2,
"W"), what I saw was:
'' =903246.434903973^2/33870000
which makes it difficult to determine how the computation was done, and what
cells the terms of the computation came from. One of my purposes here, is to
make the results easy to verify, so that an upcoming coding review can speed
right along. Again, thanks greatly for your help.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Hi keiji:
Thanks for your help. Your solution works. To get its timing, I ran it
100 times. Nine times out of 10, 100 executions of your solution takes
0.09375 secs. Not bad! When I ran the solution I came up with at 3:58 AM this
morning, 100 executions took between 0.125 and 0.1406 secs. Using Names in
Excel VBA results in a heavy time cost. I hope they fix this some day. There
is no need for a 30+ % time cost in using names.
When I then went to actually look at what was in Cells(LstRowData + 2,
"W"), what I saw was:
W115^ 2/I4
which makes it easy to determine how the computation was done, and where its
various terms came from. One of my purposes here, is to make the results easy
to verify, so that an upcoming coding review can speed right along--and your
solution fits the bill. Again, thanks for your solution.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[keiji kounoike]

You're welcome.

keiji