Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
Hi,
I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
Stefan,
In VBA, try using DSUM and let Excel convert it to the Swedish equivalent. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
You need to see why Excel is complaining. Instead of something like:
rmg.Formula=your_formula first try: rng.Value= Chr(39) & your_formula Then go to the worksheet and try to remove the single quote to view the problem -- Gary''s Student - gsnu200713 "svai" wrote: Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
Hi Bob, I tried that, but it gives the same error: "Application-defined or
object-defined error" /Stefan "Bob Phillips" wrote: Stefan, In VBA, try using DSUM and let Excel convert it to the Swedish equivalent. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
Well, here's the problem: I place the single quote in front of my formula in
vba, and when I remove the single quote in Excel it results in a working formula... It then works exactly as intended. But without the "Chr(39) &" there's an error occuring... /Stefan "Gary''s Student" wrote: You need to see why Excel is complaining. Instead of something like: rmg.Formula=your_formula first try: rng.Value= Chr(39) & your_formula Then go to the worksheet and try to remove the single quote to view the problem -- Gary''s Student - gsnu200713 "svai" wrote: Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
Stefan,
I know that my advice is correct, but I am not sure what happens with analysis toolpak functions (probably doesn't work), if the semi-colons use the same principle. Please try these options Cells(114 + 15 * i + j, 3).Value = _ "=DSUM(Listan!$D:$G,""Förbrukning""," & _ Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" But thinking about it, I think it is the analysis toolpak problem. How many cells are being compared against in Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i))? Maybe need a different formula. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi Bob, I tried that, but it gives the same error: "Application-defined or object-defined error" /Stefan "Bob Phillips" wrote: Stefan, In VBA, try using DSUM and let Excel convert it to the Swedish equivalent. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to link strings in a formula in VBA?
But thinking about it, I think it is the analysis toolpak problem.
I am missing the connection. I didn't see any functions listed that are in the analysist toolpak???? Changing Value to the more Explicit Formula might help Cells(114 + 15 * i + j, 3).Formula = _ "=DSUM(Listan!$D:$G,""Förbrukning""," & _ Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" -- Regards, Tom Ogilvy "Bob Phillips" wrote: Stefan, I know that my advice is correct, but I am not sure what happens with analysis toolpak functions (probably doesn't work), if the semi-colons use the same principle. Please try these options Cells(114 + 15 * i + j, 3).Value = _ "=DSUM(Listan!$D:$G,""Förbrukning""," & _ Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" But thinking about it, I think it is the analysis toolpak problem. How many cells are being compared against in Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i))? Maybe need a different formula. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi Bob, I tried that, but it gives the same error: "Application-defined or object-defined error" /Stefan "Bob Phillips" wrote: Stefan, In VBA, try using DSUM and let Excel convert it to the Swedish equivalent. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "svai" wrote in message ... Hi, I've run into a problem I can't come around: I want to give a number of cells a formula: =DSUMMA(Listan!$D:$G;"Förbrukning";AH4:AI5) (DSUMMA equals DSUM in the Swedish version, and the cell reference AH4:AI5 changes for each target cell) I do it in VBA like this (don't mind the i:s and j:s): Cells(114 + 15 * i + j, 3).Value = "=DSUMMA(Listan!$D:$G;""Förbrukning"";" & Range(Cells(2 + 2 * j, 34 + 2 * i), Cells(3 + 2 * j, 35 + 2 * i)).Address & ")" The connection between the target cell and the reference cells is a bit messy, hence the i:s and j:s. However, this generate a 1004 error, which I trace to the "=DSUMMA"-string; Excel refuses to accept the formula. Showing the whole string ("=DSUMMA(...)") in a msgbox gives a correct syntax and is exactly what I want to put in the cells (which works if I do). How do I overcome this? Thanks /Stefan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Evaluating a DDE formula created by strings | Excel Worksheet Functions | |||
Use Formula to sort two strings | Excel Worksheet Functions | |||
Formula that will ignore strings | Excel Programming | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions |