Macro Advice

[#1]

Hi guys,

I am writing a macro for the first time and having trouble with it, Please advice

Macro
Sub Test set mytext = ActiveDocument.GetSheetObject("A").GetCell(0,0) ActiveDocument.Variables("vTest").SetContent mytext.text, true set myvar = ActiveDocument.GetSheetObject("B").GetCell(vTest,0) ActiveDocument.Variables("Test").SetContent mytext2.text, true End Sub

I am trying to pull a value from sheet object A , store it in a variable vTest and then pass it as a parameter for pulling a value from Sheet object B. The macro above is what i have so far. Please advise,

Can i use a set x = vTest statement of some sort and create a temp variable and use that to get the value from Object B.

Also,

I know what i am trying to maybe easier with a peek function, but i want to be able to keep it dynamic as i have a huge access database linked to my application. Please advice,

Thank you for your time and effort,

Best,

Nik

[Anonymous · Accepted solution]

A few minor fixes:

Sub Test

set mytext = ActiveDocument.GetSheetObject("A").GetCell(1,0)
ActiveDocument.Variables("vTest").SetContent mytext.text, true

vt=ActiveDocument.Variables("vTest").GetContent.string
set mytext2 = ActiveDocument.GetSheetObject("B").GetCell(vt,0)
ActiveDocument.Variables("Test").SetContent mytext2.text, true

End Sub

[#3]

Thank you Michael for the response.

Best,

Nik

[#4]

I tried the macro. It works but only upto a specific rowNo after which it gives me an error. Is there a limit on the number the variable can take? (for example: 347 returns an error), My column certainly does have more than 347 rows. Please advice,

Thanks,

Nik

[Anonymous]

Variable can take anything - number or text.  Wondering if GetCell() has any limits.  Which would be rather strange

[#6]

That is the exact same reaction i had, get cell is giving me an exception. Do you think its a better idea to use the peek function? Can it be written to dynamically give different values?

[Anonymous]

I think it is not about limit.  More likely the number you're getting from the object A points you to the row in the object B which doesn't exist.

[#8]

I will double check the total number of rows for object B. Thanks again.

[Anonymous]

No Problem.

BTW, I just checked in my sample - it gives error with row 26 in A because it points to row 19 in B, but B has only 18 rows.

On the other hand, it works fine if I read row 1700 from A - it points to row 18 (or 17) in B.

So, it's not the limit.

[#10]

I understand now, What if i use something like

if(GetSelectedCount(A)>0 , concat(Row No,  ','). will it give me the RowNo from B or just A?