Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
I'm trying to insert the following formula into my spreadsheet using
the following code 'insert formula =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") .cells(lnextrow,"I").formula="=IF(G" & lprevsumrow & "<" & """" _ & ",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & "," & ""d"" & _ ")+1, "& """" & ")" The row will not always be 2, but will be a long variable called lPrevSumRow. I'm getting an "end of statement" compile error, highlighting the d in double quotes. I'm missing something in the syntax. But what the *&^% is it? Thanks again. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
i don't know what ""d"" is,
""d"" & ")+1 is it a variable, a column letter wit hte row missing? why is it in double quotes? this part doesn't make sense to me, but i'm no expert -- Gary "davegb" wrote in message oups.com... I'm trying to insert the following formula into my spreadsheet using the following code 'insert formula =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") .cells(lnextrow,"I").formula="=IF(G" & lprevsumrow & "<" & """" _ & ",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & "," & ""d"" & _ ")+1, "& """" & ")" The row will not always be 2, but will be a long variable called lPrevSumRow. I'm getting an "end of statement" compile error, highlighting the d in double quotes. I'm missing something in the syntax. But what the *&^% is it? Thanks again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Dave,
You only need to use the concatenators where you're substituting the variable lPrevSumRow for the row number. Try this (all on one line)... "=IF(G" & lprevsumrow & "<"""",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & ",""d"")+1,"""")" NickH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
& ""d"" &
should be & """d""" & -- Tim Williams Palo Alto, CA "davegb" wrote in message oups.com... I'm trying to insert the following formula into my spreadsheet using the following code 'insert formula =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") .cells(lnextrow,"I").formula="=IF(G" & lprevsumrow & "<" & """" _ & ",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & "," & ""d"" & _ ")+1, "& """" & ")" The row will not always be 2, but will be a long variable called lPrevSumRow. I'm getting an "end of statement" compile error, highlighting the d in double quotes. I'm missing something in the syntax. But what the *&^% is it? Thanks again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Gary Keramidas wrote: i don't know what ""d"" is, ""d"" & ")+1 is it a variable, a column letter wit hte row missing? why is it in double quotes? this part doesn't make sense to me, but i'm no expert -- Gary The "d" is the parameter that tells datedif what units to give the difference in, in this case, days. It's in double quotes because it has to be in quotes in the final formula. "davegb" wrote in message oups.com... I'm trying to insert the following formula into my spreadsheet using the following code 'insert formula =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") .cells(lnextrow,"I").formula="=IF(G" & lprevsumrow & "<" & """" _ & ",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & "," & ""d"" & _ ")+1, "& """" & ")" The row will not always be 2, but will be a long variable called lPrevSumRow. I'm getting an "end of statement" compile error, highlighting the d in double quotes. I'm missing something in the syntax. But what the *&^% is it? Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
NickH wrote: Dave, You only need to use the concatenators where you're substituting the variable lPrevSumRow for the row number. Try this (all on one line)... "=IF(G" & lprevsumrow & "<"""",DATEDIF(G" & lprevsumrow & ",H" & lprevsumrow & ",""d"")+1,"""")" NickH Thanks, Nick, it worked! But I find it very confusing. I've gone through it a couple of times, trying to interpret the various sets of quotes, and can't make sense of them. This is the first time I've tried to insert a formula this complex using a macro, and my head is spinning with quotation marks. Particularly, the the last set of 4. Trying to understand this, I went through the formula on paper, marking which sets of " marks I thought were a pair. It took a couple of tries because the first attempt gave inconsistent results as far as the way they seemed to be applied. I finally got a consistent result, but I want to make sure I have it right. To convey my guesstimate to you, I numbered each quote mark, from 1 to 20. Is it too much to ask for you, or someone else, if they can, to tell me if I have it right? I've listed the numbered pairs below as my best guess. This seemed simpler, and less prone to interpretation, than trying to explain the pairings with words. 1 & 2 3 & 8 4 & 7 5 & 6 11 & 20 12 & 15 13 & 14 16 & 19 17 & 18 Does this make sense? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Sorry Dave, Way too heavy for me.
All I did was put your original example formula - =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") - into a cell and then recorded a macro as I made a change to it (deleted and replaced the last bracket), hit return and stopped the macro. I then edited the result by replacing the 2s with " & lprevsumrow & " I hope this helps for the future. NickH |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Ah, I see what you're getting at now.
I would say they are paired something like this... 1 & 20 These quotes embrace the whole formula 2 & 3 are embracing - & lprevsumrow & - which is a variable hence the need to interrupt the string 4 & 7 embracing "" and telling the code to interpret the quotes within as text NOT an instruction to break out of the string 8 & 9 again embracing - & lprevsumrow & 10 & 11 same again 12 & 15 "d" - same as 4 & 7 16 & 19 "" - same as 4 & 7 again Kind regards, NickH |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
NickH wrote: Sorry Dave, Way too heavy for me. All I did was put your original example formula - =IF(G2<"",DATEDIF(G2,H2,"d")+1,"") - into a cell and then recorded a macro as I made a change to it (deleted and replaced the last bracket), hit return and stopped the macro. I then edited the result by replacing the 2s with " & lprevsumrow & " I hope this helps for the future. NickH You're a sneaky devil, Nick! That's so clever it sounds immoral, or at least unethical. Not to mention a great idea! Will definitely have to remember it for future reference. Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
NickH wrote: Ah, I see what you're getting at now. I would say they are paired something like this... 1 & 20 These quotes embrace the whole formula 2 & 3 are embracing - & lprevsumrow & - which is a variable hence the need to interrupt the string 4 & 7 embracing "" and telling the code to interpret the quotes within as text NOT an instruction to break out of the string 8 & 9 again embracing - & lprevsumrow & 10 & 11 same again 12 & 15 "d" - same as 4 & 7 16 & 19 "" - same as 4 & 7 again Kind regards, NickH I'm not sure of this. Particularly about enclosing the whole thing in quotation marks. In my limited past experience, anything in ampersands should NOT be in quotes, because it's already text by definition. But I'm not sure of any of this. Does anyone have a lot of experience with this and can clarify? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
Okay, yes you are right the correct way of looking at it would be as
follows... This string: "=IF(G" is concatenated to this variable: lprevsumrow which is concatenated to this string: "<"""",DATEDIF(G" which is concatenated to this variable: lprevsumrow concatenated to this string: ",H" concatenated to this variable: lprevsumrow concatenated to this string: ",""d"")+1,"""")" To explain the double quotes: When the code encounters a quotation mark (") it reads it as the start of a string. the next quotation mark encountered will signal the end of a string unless it is followed immediately by a second quotation which means 'hey I'm part of the string so don't stop yet!' My apologies for not thinking it through properly before. Kind regards, NickH |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting formula
NickH wrote: Okay, yes you are right the correct way of looking at it would be as follows... This string: "=IF(G" is concatenated to this variable: lprevsumrow which is concatenated to this string: "<"""",DATEDIF(G" which is concatenated to this variable: lprevsumrow concatenated to this string: ",H" concatenated to this variable: lprevsumrow concatenated to this string: ",""d"")+1,"""")" To explain the double quotes: When the code encounters a quotation mark (") it reads it as the start of a string. the next quotation mark encountered will signal the end of a string unless it is followed immediately by a second quotation which means 'hey I'm part of the string so don't stop yet!' My apologies for not thinking it through properly before. Kind regards, NickH That makes more sense. I understand the double quotes. Some of the others were a little harder to discern. It's starting to make sense now, which is what I was hoping to get out of this, so I can do it better next time. Thanks for taking your time, Nick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Formula | Excel Discussion (Misc queries) | |||
HELP with inserting formula | Excel Programming | |||
inserting a formula | Excel Discussion (Misc queries) | |||
Formula changes while inserting a row !!!! | Excel Worksheet Functions | |||
Inserting Formula | Excel Programming |