debuggin help

[Nasir.Munir]

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/archives/2004/04/16/worksheet-formulas-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,

 

[Susan]

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

 

[Susan]

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

 

[Nasir.Munir]

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








- 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

 

[Nasir.Munir]

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








- 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.

 

[Susan]

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

 

[Nasir.Munir]

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

 

[Nasir.Munir]

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.

 

[Bob Phillips]

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)

 

[Susan]

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

 

[Bob Phillips]

Correction

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)

 

[Nasir.Munir]

I should have explained it earlier, my bad.
I have initialized all the variables, but when I pasted here I missed
that out.
Secondly, when I use the TREND function in the spread sheet I
certainly can use it effectively, but not with the vba code(ofcourse).
Bob thanks for the tip. I have tested your suggestion, but now I am
getting this error message with .Cells part highlighted. "Invalid or
unqualified reference". Is it confused to figure out the cell ? or
something else ? Any thought ?

 

[jaf]

Hi Nasir,
The code you posted is designed to enter a formula into a cell on a
worksheet.

=Trend(thisworkbook.... would be entered into the target cell.

List of problems.
1. You don't define which cell (or worksheet) your targeting.
2. You are creating a string. That is what testing = " does. It creates a
string because you use a double quote before the second equals sign.

3. I pasted your code into a new work book and got a type mismatch error.
Most likely because "testing" has been define as a double. (double precision
floating point NUMBER) and you are giving it string data.

4. It isn't going to work. "ThisWorkbook.Worksheets" is VBA and will not
work in a worksheet formula.

I did not see the code on the link you posted.
The web page is about using worksheet formulas in VBA.

Is that what your trying to do?

 

[Nasir.Munir]

John, thanks for the help.
I think you got me right.
1) I am initializing the variables, but I missed pasting it here.
2) I understand your string argument. Can we not cast it as a double ?
I did that, but it didn't work.
3) I can also understand that, it might not take Thisworkbook ... from
VBA to spreadsheet function. My question is how to use spread sheet
function in VBA code then ?
4) I came across an article, link provided in the first message, that
is suggesting that I can perphaps use the spreadsheet function in my
vba code.
5) I think there is something wrong with the syntax I am using.
6) My question, recomposed, would be: how can we use spreadsheet
function in vba code? If yes, then can anyone show me how to use TREND
function in my vba code ?

Thanks.

 

[jaf]

Hi Nasir,

dim testing
testing = Application.WorksheetFunction.Trend(...