inserting formula

[davegb]

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.

 

[Gary Keramidas]

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

 

[NickH]

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

 

[Tim Williams]

& ""d"" &

should be

& """d""" &

 

[davegb]

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

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]

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?

 

[NickH]

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

 

[NickH]

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

 

[davegb]

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.

 

[davegb]

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?

 

[NickH]

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

 

[davegb]

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.