#NAME? on correct formula
 

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

#NAME? on correct formula
 

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

"JohnP" wrote in message
...
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[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

#NAME? on correct formula
 

Answers inline...

"JohnP" wrote:

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)"

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))"


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

#NAME? on correct formula
 

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?

"Peter T" wrote:

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

"JohnP" wrote in message
...
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[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

#NAME? on correct formula
 

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
"JohnP" wrote in message
...
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?

"Peter T" wrote:

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

"JohnP" wrote in message
...
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[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