#NAME? on correct formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnP - 14 Mar 2008 10:13 GMT

Hi,

I want to enter a formula into a cell using VBA but while the formula is
sound it leaves "#NAME?" in the cell until you manually click into and out of
it. Is there anything I can do to stop this?

ActiveCell.FormulaR1C1 = "=ROUND(VLOOKUP(R[3]C,'Part
Time'!A:H,8,FALSE)*52/1.5,0)"

Also, how do I fix a reference in R1C1 format? The formula below has
apostraphes around the usual excel range but that needs to be fixed. any
ideas?

ActiveCell.FormulaR1C1 =
"=IF(R[-4]C=""Full-time"",""N/A"",R[-2]C-SUMIF(B8:B5000,""Monday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part
Time'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part
Time'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part
Time'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part
Time'!A:H,6)-SUMIF(B8:B5000,""Friday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,7))"

Thanks in advance for any help you can offer.
JohnP

Reply to this Message

Peter T - 14 Mar 2008 10:47 GMT

I first wondered if perhaps you didn't have a sheet named "Part Time", but I
see what you mean.

After writing the formula to the cell try add the following line
ActiveCell.Formula = ActiveCell.Formula

Better still, try converting the formula to full R1C1 style which it isn't
as posted. What that should be will depend on relative/absolute offset ref's
that you want from the active cell. I found after doing that the formula
took first time. Just re-read the R1C1 formula for reference

I don't really follow what you are asking in the second part of your
question, though I confess didn't look closely.

Regards,
Peter T

> Hi,
>
[quoted text clipped - 10 lines]
>
> ActiveCell.FormulaR1C1 =

"=IF(R[-4]C=""Full-time"",""N/A"",R[-2]C-SUMIF(B8:B5000,""Monday"",R[3]C:R[8
000]C)/7.5*VLOOKUP(R[1]C,'Part

Time'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'P
art

Time'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,
'Part

Time'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'
Part

Time'!A:H,6)-SUMIF(B8:B5000,""Friday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Pa
rt Time'!A:H,7))"

> Thanks in advance for any help you can offer.
> JohnP

Reply to this Message

JohnP - 14 Mar 2008 11:23 GMT

Hi Peter,

That's great you have fixed the first problem. Thankyou!

The second issue is how do you get an absolute reference (fixed) into an
R1C1 formula? I need to account for the fact that the formula can be in any
column and do a sumif that refers to the variable position but always refers
back to the fixed position of B8:B5000. At the minute it turns B8:5000 into
'B8:B5000'.

Does that make sense?

> I first wondered if perhaps you didn't have a sheet named "Part Time", but I
> see what you mean.
[quoted text clipped - 46 lines]
> > Thanks in advance for any help you can offer.
> > JohnP

Reply to this Message

Peter T - 14 Mar 2008 13:01 GMT

take a look at these

s = "B8:B5000"
x = Application.ConvertFormula(s, xlA1, xlR1C1, True)
y = Application.ConvertFormula(s, xlA1, xlR1C1, False)

Debug.Print x
Debug.Print y

If it's OK for your formula to be written like this
$B$8:$B$5000

simply hardcode R8C2:R5000C2 into the R1C1 formula

Otherwise use ConvertFormula and parse the result as assigned to 'y' into
your formula.

If you play around a bit more with ConvertFormula and it's 4th argument, you
may find you can return the entire R1C1 formula without parsing. I haven't
tried but might need to use ConvertFormula twice, in both ways if that
makes sense (don't worry if not).

Regards,
Peter T

If you mean

> Hi Peter,
>
[quoted text clipped - 40 lines]
> > >
> > > ActiveCell.FormulaR1C1 =

"=IF(R[-4]C=""Full-time"",""N/A"",R[-2]C-SUMIF(B8:B5000,""Monday"",R[3]C:R[8

> > 000]C)/7.5*VLOOKUP(R[1]C,'Part

Time'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'P

> > art

Time'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,

> > 'Part

Time'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'

> > Part

Time'!A:H,6)-SUMIF(B8:B5000,""Friday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Pa

> > rt Time'!A:H,7))"
> > >
> > > Thanks in advance for any help you can offer.
> > > JohnP

Reply to this Message

James Snell - 14 Mar 2008 11:07 GMT

Answers inline...

> Hi,
>
> I want to enter a formula into a cell using VBA but while the formula is
> sound it leaves "#NAME?" in the cell until you manually click into and out of
> it. Is there anything I can do to stop this?

Your formula is wrong which is why the name doesn't work.

Either write in r1c1 or a1 format, your formula is a mix of both.

Here it is in R1C1...
ActiveCell.FormulaR1C1 = "=ROUND(VLOOKUP(R[3]C,'Part
Time'!C:C[7],8,FALSE)*52/1.5,0)"

Note: 'Part Time'!A:H should read 'Part Time'!C:C[7]

> ActiveCell.FormulaR1C1 = "=ROUND(VLOOKUP(R[3]C,'Part
> Time'!A:H,8,FALSE)*52/1.5,0)"
[quoted text clipped - 9 lines]
> Time'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part
> Time'!A:H,6)-SUMIF(B8:B5000,""Friday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'Part Time'!A:H,7))"

Probably the same situation here - If I remember correctly R1C1 is natively
fixed when it comes to cell references. So ActiveCell.FormulaR1C1 = "=R1C1"
would yield a cell value of =$A$1

Reply to this Message