#NAME? on correct formula

J

JohnP

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
 
P

Peter T

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 said:
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,'PartTime'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'P
artTime'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,
'PartTime'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'
Part
Click to expand...
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))"
 
J

James Snell

Answers inline...

JohnP said:
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?
Click to expand...

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))"
Click to expand...

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
 
J

JohnP

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 said:
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 said:
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,'PartTime'!A:H,3)-SUMIF(B8:B5000,""Tuesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'P
artTime'!A:H,4)-SUMIF(B8:B5000,""Wednesday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,
'PartTime'!A:H,5)-SUMIF(B8:B5000,""Thursday"",R[3]C:R[8000]C)/7.5*VLOOKUP(R[1]C,'
Part
Click to expand...
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
Click to expand...
Click to expand...
 
P

Peter T

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 said:
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 said:
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 said:
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
Click to expand...
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 =
Click to expand...
"=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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error '1004': Application defined or Object defined error 2
Create Detail Sheets from Pivot Table 1
Running Macro on Multiple Worksheets 6
Vlookup in vba - how to use absolute rows not relative 1
R1C1 Formula 1
displaying formula result 1
Int Variable into R1C! Formula 1
Macro autofills formula when a cell value is changed 4

Top