Why isn't this working?


Posted by Frank Krumm on December 26, 2001 7:18 AM

Here is the code:
Worksheets("Sheet3").Activate
T=Selection.Address
Range("A2:F2").Copy
Range(T).PasteSpecial xlPasteValues
Range(T).Offset(1,0).Select
Worksheets("Sheet2").Activate

This runs within a larer macro that downloads and evaluates data from an external source in Worksheet 2.

Sheet 3 RangeA2:F2 contains results from Sheet2.

I begin by selecting A4:F4 on Sheet 3, then go to Sheet2 and run the program.

Range A2 copies nicely down the page. However, the rest of the range has #NA's in it.

Any thoughts?

Posted by Frank Krumm on December 26, 2001 7:23 AM

Also, on sheet3, the copy row not only has the cute barber pole thing going, but Range C2-F2 is flashing. Cool, but undesired. Range B2 is blank and should be.

Posted by Scott on December 26, 2001 7:28 AM

If you add "Application.CutCopyMode = False" after your Paste Special Command, it should stop the "Barber Pole" thing. As for everything else, it seems to work ok on my end.

Posted by Frank Krumm on December 26, 2001 7:46 AM

Barber stops but #NA's still there

Scott, thank you for your help. Please allow me to burden you with the entire code and a brief explanation.
S=Selection.Address
str1=ActiveCell.Value
newFor="=Qlink|Bars!'" & str1 & ",d,250,dohlc,headers'"
Range("A2:F253").Select
Selection.FormulaArray=newFor
---then the code you have---
Range(S).Offset(1,0).Select

This accesses stock data from a source called Qlink. The values are downloaded into sheet2 for the stock symbol that I choose. The data is evaluated and results are generated.

Sheet3 is my attempt to transfer the stock-by-stock evaluation results to a table that I can look at or print.

The split second before the results on Sheet2 appear, there are #NA's everywhere, then the results replace the NA's.

Posted by Scott on December 26, 2001 8:11 AM

Re: Barber stops but #NA's still there

Frank, I don't see anything here that jumps out at me. Are the cells that you are copying from in Sheet3 Links to Sheet2? You might want to try and go through the code step by step using "Debug-Step Into" (You can go line by line by pressing F8). Switch back between your VB Window and Excel after each line of code. At least you will be able to tell what line in the code is causing the problem.

Posted by Frank Krumm on December 26, 2001 8:28 AM

Thanks Scott and a short ?

Yes, the range C2:F2 on Sheet3 has short formulas =Sheet2!V2 etc.

I find that if I cut the Range T part and put in a separate Module on Sheet 3 that it will run fine. That tells me that there is something with it being involved with the larger macro that is the prob.

I'm wondering if in the larger macro then I can just write a line that says Application.Run etc and it will run the sheet 3 macro. I'm using syntax Application.Run but I keep blowing it after that. Please help.

Posted by Scott on December 26, 2001 8:48 AM

Re: Thanks Scott and a short ?

First, change the code (smaller macro) from a "Sub" to a "Function". Example "Sub Update_Sheet()" to Function "Update_Sheet()"
Then Insert this line into your original code(larger macro):
Run (Update_Sheet())

Let me know if this works.

Yes, the range C2:F2 on Sheet3 has short formulas =Sheet2!V2 etc. I find that if I cut the Range T part and put in a separate Module on Sheet 3 that it will run fine. That tells me that there is something with it being involved with the larger macro that is the prob. I'm wondering if in the larger macro then I can just write a line that says Application.Run etc and it will run the sheet 3 macro. I'm using syntax Application.Run but I keep blowing it after that. Please help.

Posted by Frank Krumm on December 26, 2001 8:48 AM

Aplication.Run doesn't work either.

Well I got the Syntax right but it does the same as if I had the lines in there. Yes, the range C2:F2 on Sheet3 has short formulas =Sheet2!V2 etc. I find that if I cut the Range T part and put in a separate Module on Sheet 3 that it will run fine. That tells me that there is something with it being involved with the larger macro that is the prob. I'm wondering if in the larger macro then I can just write a line that says Application.Run etc and it will run the sheet 3 macro. I'm using syntax Application.Run but I keep blowing it after that. Please help.

Posted by Frank Krumm on December 26, 2001 9:27 AM

I'm going back to my fingers and toes!!!

I'm sure that your idea will help, however, somehow now the workbook is edit-only. I din't do it!!! Also, when I try to change the Sub to Function this error message comes up and won't stay gone long enough to change. That I can fix by just creating a new module, but the edit only thing has me baffled. First, change the code (smaller macro) from a "Sub" to a "Function". Example "Sub Update_Sheet()" to Function "Update_Sheet()"



Posted by Frank Krumm on December 26, 2001 9:55 AM

The Run Function solution produced the same results

Although the worksheet is still edit-only, I just saved it as another worksheet and carry-on. The new idea produces the same result.

Scott you have been a big help and I'm starting to feel intrusive, so please do not feel obligated to continue. I will though. I'm sure that your idea will help, however, somehow now the workbook is edit-only. I din't do it!!! Also, when I try to change the Sub to Function this error message comes up and won't stay gone long enough to change. That I can fix by just creating a new module, but the edit only thing has me baffled. : First, change the code (smaller macro) from a "Sub" to a "Function". Example "Sub Update_Sheet()" to Function "Update_Sheet()"