script problem

[alanoreilly]

Hi, please help!!!

this piece of VB script is from an automatically generated button for a report I'm designing....

The first one works fine but I want to append the info from the seconf one on the bottom of the first.... I want to very simply just do something like.....

select cell a9
selection end down
offset (1,0)... or is is (0,1) I can never remember!

The problem is I don't know how to use this code within the function below


Call Run("CODA_extractAndDisplayDetails", 4, "$A$9" ,"G:\Alan\Monthly Report.bdd", True)

Call Run("CODA_extractAndDisplayDetails", 5, "$A$9" ,"G:\Alan\Monthly Report.bdd", True)

Any help very much appreciated...

Thanks
Alan

 

[GlennUK]

Look at the help for Offset if you can't remember the arguments. Look at the address property in help too, as it looks like you have to pass the address of the cell to the call.

 

[alanoreilly]

Thanks Glenn, I have been looking at the help but the problem is that really I dont know very much about VB aside from the basics....

Essentially instead of passing the cell A9 to the call I want to pass something along the lines I had above... so instead of telling it go to cell a9, I tell it go to cell a9, end down and offset one row.... the problem is I have no idea how to do this...

 

[GlennUK]

See if this works:

Call Run("CODA_extractAndDisplayDetails", 5, Range("A9").End(xlDown).Offset(1,0).Address ,"G:\Alan\Monthly Report.bdd", True)

 

[alanoreilly]

I've been using...

Call Run("CODA_extractAndDisplayDetails", 5, "A9" ,"G:\Alan\Monthly Report.bdd", True)

Call Run("CODA_extractAndDisplayDetails", 5, Range("A9").Select.End(xlDown).Offset(1, 0).Select ,"G:\Alan\Monthly Report.bdd", True)

The first part is fine but the second gives me an error message saying "Object required" any ideas....??

 

[alanoreilly]

I tried your suggection above... Range("A9").End(xlDown).Offset(1,0).Address

but it doesn't seem to work... the macro appears to work and even tells me it has transferred the data but when I check only the first part has worked

PArt 1
Call Run("CODA_extractAndDisplayDetails", 5, "$A$9", "G:\Alan\Monthly Report.bdd", False)

Part 2
Call Run("CODA_extractAndDisplayDetails", 4, Range("A9").End(xlDown).Offset(1, 0).Address, "G:\Alan\Monthly Report.bdd", False)

 

[GlennUK]

You may have to fully qualify the reference, so put a Workbooks("wbname"). before the Range qualifier, and see if that works.

 

[alanoreilly]

I just tried...

ActiveSheet.Range("A9").End(xlDown).Offset(1,0).Address

again it goes through teh motions as if it has worked but when I check it has only loaded the first part...

 

[GlennUK]

Step through the code and see what string that results in, and see whether it's what you expected. Or strip that code out and assign it to a variable, and pass the variable to the call, but place a MsgBox statement before the call to show the string.

 

[alanoreilly]

Thanks Glenn but I'm afraid most of that is double dutch to me....

apologies,