can anyone figure out what is wrong with this statement ?
I am getting compile error as " Expected: End of statement " and
Syntax error.

I am trying to use Excel function in my vba code and I got this link
which is suggesting perhaps I can use those functions by including
double quotes.

http://www.dailydoseofexcel.com/arch...in-vba-part-i/

The statement is :

testing =
"=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
1), _

ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _

ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
ThisWorkbook.Worksheets(""T-
section"").Cells(r, c))"

The second line workbook part gets highlighted when i run the macro.
I hope I can get some help here,
thanks,

what do you have "testing" dimmed as? a range? a string?
it looks like you're trying to tell it to equal those worksheet
names. if so, you don't need double quotes.
it is looking for "Table Figure"
instead of Table Figure.
unless your worksheet names really do include the quotes........
maybe this will help
susan


On Mar 30, 12:17 pm, Nasir.Mu...@dal.ca wrote:
> can anyone figure out what is wrong with this statement ?
> I am getting compile error as " Expected: End of statement " and
> Syntax error.
>
> I am trying to use Excel function in my vba code and I got this link
> which is suggesting perhaps I can use those functions by including
> double quotes.
>
> http://www.dailydoseofexcel.com/arch...ksheet-formula...
>
> The statement is :
>
> testing =
> "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> 1), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> ThisWorkbook.Worksheets(""T-
> section"").Cells(r, c))"
>
> The second line workbook part gets highlighted when i run the macro.
> I hope I can get some help here,
> thanks,

i had a look @ the dailydoseofexcel website. so i hereby amend my
statement & say keep the quotes (although you could try it without
them, just to see...).
try stepping through it & hover over each variable & see if it shows
what you want it to.
for instance, rg, cg....... if it says rg = nothing then it means it
doesn't know what rg stands for.
same with cg.
susan



On Mar 30, 12:17 pm, Nasir.Mu...@dal.ca wrote:
> can anyone figure out what is wrong with this statement ?
> I am getting compile error as " Expected: End of statement " and
> Syntax error.
>
> I am trying to use Excel function in my vba code and I got this link
> which is suggesting perhaps I can use those functions by including
> double quotes.
>
> http://www.dailydoseofexcel.com/arch...ksheet-formula...
>
> The statement is :
>
> testing =
> "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> 1), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> ThisWorkbook.Worksheets(""T-
> section"").Cells(r, c))"
>
> The second line workbook part gets highlighted when i run the macro.
> I hope I can get some help here,
> thanks,

On Mar 30, 1:21 pm, "Susan" <bogenex...@aol.com> wrote:
> what do you have "testing" dimmed as? a range? a string?
> it looks like you're trying to tell it to equal those worksheet
> names. if so, you don't need double quotes.
> it is looking for "Table Figure"
> instead of Table Figure.
> unless your worksheet names really do include the quotes........
> maybe this will help
> susan
>
> On Mar 30, 12:17 pm, Nasir.Mu...@dal.ca wrote:
>
>
>
> > can anyone figure out what is wrong with this statement ?
> > I am getting compile error as " Expected: End of statement " and
> > Syntax error.
>
> > I am trying to use Excel function in my vba code and I got this link
> > which is suggesting perhaps I can use those functions by including
> > double quotes.
>
> >http://www.dailydoseofexcel.com/arch...ksheet-formula...
>
> > The statement is :
>
> > testing =
> > "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> > 1), _
>
> > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> > ThisWorkbook.Worksheets(""T-
> > section"").Cells(r, c))"
>
> > The second line workbook part gets highlighted when i run the macro.
> > I hope I can get some help here,
> > thanks,- Hide quoted text -
>
> - Show quoted text -

Thanks for a quick reply Susan.
If I remove those double quotes then when I run the macro it
highlights the sheet name and gives me the same error message. The
article I have referred suggests that in order to take existing
quotes, it has to be doubly quoted.
testing is declared as an integer, and all the values being used are
declared as integers as well

On Mar 30, 1:25 pm, "Susan" <bogenex...@aol.com> wrote:
> i had a look @ the dailydoseofexcel website. so i hereby amend my
> statement & say keep the quotes (although you could try it without
> them, just to see...).
> try stepping through it & hover over each variable & see if it shows
> what you want it to.
> for instance, rg, cg....... if it says rg = nothing then it means it
> doesn't know what rg stands for.
> same with cg.
> susan
>
> On Mar 30, 12:17 pm, Nasir.Mu...@dal.ca wrote:
>
>
>
> > can anyone figure out what is wrong with this statement ?
> > I am getting compile error as " Expected: End of statement " and
> > Syntax error.
>
> > I am trying to use Excel function in my vba code and I got this link
> > which is suggesting perhaps I can use those functions by including
> > double quotes.
>
> >http://www.dailydoseofexcel.com/arch...ksheet-formula...
>
> > The statement is :
>
> > testing =
> > "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> > 1), _
>
> > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> > ThisWorkbook.Worksheets(""T-
> > section"").Cells(r, c))"
>
> > The second line workbook part gets highlighted when i run the macro.
> > I hope I can get some help here,
> > thanks,- Hide quoted text -
>
> - Show quoted text -

Susan I wish I could hover over the variables to see the values, but
the error I am getting is a compile time error.
I have tried it with and without quotes, no luck. Also I have tried to
use different variable types as well, all in vein.

nasir -
can you post the whole code with your declarations??
susan


On Mar 30, 12:31 pm, Nasir.Mu...@dal.ca wrote:
> On Mar 30, 1:25 pm, "Susan" <bogenex...@aol.com> wrote:
>
>
>
>
>
> > i had a look @ the dailydoseofexcel website. so i hereby amend my
> > statement & say keep the quotes (although you could try it without
> > them, just to see...).
> > try stepping through it & hover over each variable & see if it shows
> > what you want it to.
> > for instance, rg, cg....... if it says rg = nothing then it means it
> > doesn't know what rg stands for.
> > same with cg.
> > susan
>
> > On Mar 30, 12:17 pm, Nasir.Mu...@dal.ca wrote:
>
> > > can anyone figure out what is wrong with this statement ?
> > > I am getting compile error as " Expected: End of statement " and
> > > Syntax error.
>
> > > I am trying to use Excel function in my vba code and I got this link
> > > which is suggesting perhaps I can use those functions by including
> > > double quotes.
>
> > >http://www.dailydoseofexcel.com/arch...ksheet-formula...
>
> > > The statement is :
>
> > > testing =
> > > "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> > > 1), _
>
> > > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> > > ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> > > ThisWorkbook.Worksheets(""T-
> > > section"").Cells(r, c))"
>
> > > The second line workbook part gets highlighted when i run the macro.
> > > I hope I can get some help here,
> > > thanks,- Hide quoted text -
>
> > - Show quoted text -
>
> Susan I wish I could hover over the variables to see the values, but
> the error I am getting is a compile time error.
> I have tried it with and without quotes, no luck. Also I have tried to
> use different variable types as well, all in vein.- Hide quoted text -
>
> - Show quoted text -

I am just trying to test, if I can use "TREND" function of excel in my
vba code. TREND function takes range as parameter.


Dim r As Integer
Dim c As Integer
Dim rg As Integer
Dim cg As Integer
Dim testing As Double

testing = "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg -
1, cg - 1), _

ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _

ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
ThisWorkbook.Worksheets(""T-
section"").Cells(r, c))"
Debug.Print testing

thought it wise to explain the trend function:
It takes three parameter as range. I am representing range as
Range(cell,cell) instead of Range("a1:b3") - the usual way of dealing
with range. I read an article which suggested that I can use cell as
range function argument. So a total of five references have been used,
where first two rerestent the first range, next two for second , and
third representing the third parameter.

Not tested, but I would expect it to be more like this

testing ="=TREND(TableFigure!" & .Cells(rg - 1, cg -1).address & ","
& _
"TableFigure!") & .Cells(rg, cg).address & "," & _
"TableFigure!").Cells(rg - 1, cg).address & "," & _
"T-section!") & .Cells(r, c).address & ")"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> can anyone figure out what is wrong with this statement ?
> I am getting compile error as " Expected: End of statement " and
> Syntax error.
>
> I am trying to use Excel function in my vba code and I got this link
> which is suggesting perhaps I can use those functions by including
> double quotes.
>
> http://www.dailydoseofexcel.com/arch...in-vba-part-i/
>
> The statement is :
>
> testing =
> "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg -
> 1), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> ThisWorkbook.Worksheets(""T-
> section"").Cells(r, c))"
>
> The second line workbook part gets highlighted when i run the macro.
> I hope I can get some help here,
> thanks,
>

i honestly don't know anything about the TREND function, i'm just
looking at your code.
you haven't told VBA where to start........ such as,
you tell it to use these values
rg - 1, cg - 1
but you haven't told it where to start.

as in
rg = 5
cg = 3

rg-1, cg-1

then it would know to start in rg & cg, & subtract one from each of
them. and even though vba is not using those values except to put
into this function, you may have to qualify them.

a question - if you enter this formula directly into the worksheet,
does it work there????

=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg -
1, cg - 1), _
ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
ThisWorkbook.Worksheets(""T-section"").Cells(r, c))

i don't know - maybe i'm way off. just trying to help.
susan



On Mar 30, 12:45 pm, Nasir.Mu...@dal.ca wrote:
> I am just trying to test, if I can use "TREND" function of excel in my
> vba code. TREND function takes range as parameter.
>
> Dim r As Integer
> Dim c As Integer
> Dim rg As Integer
> Dim cg As Integer
> Dim testing As Double
>
> testing = "=TREND(ThisWorkbook.Worksheets(""TableFigure"").Cells(rg -
> 1, cg - 1), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg, cg), _
>
> ThisWorkbook.Worksheets(""TableFigure"").Cells(rg - 1, cg), _
> ThisWorkbook.Worksheets(""T-
> section"").Cells(r, c))"
> Debug.Print testing