| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
#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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
#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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
#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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
#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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Correct formula for this | Excel Worksheet Functions | |||
| What is the correct formula for IF C5 = Y, then L5 *.06? | Excel Worksheet Functions | |||
| Need the correct formula | Excel Discussion (Misc queries) | |||
| How do I correct this formula? | Excel Worksheet Functions | |||
| What's the correct formula? | Excel Discussion (Misc queries) | |||
Hybrid Mode
