Excel ignores VBA commands
Hi,
I am running into the frustrating problem I've seen occasionally where Excel simply doesn't do what I'm telling it to do. I am working with a lot of data and opening one "data" workbook at a time out of perhaps 40 in a folder, with four other workbooks open at any one time, in which I am accumulating filtered data from the "data" workbook. Is it possible there is a memory leak or something that is overwhelming Excel with no error message? I am using Set statements with Workbook variables, but I am re-using only five Workbook variables. Should I set them to Nothing between the times when I re-assign them to a new workbook? The statement ActiveCell.SpecialCells(xlLastCell).Offset(1, -7).Select is being ignored. As a result, data gets pasted further and further to the right and above where it should get pasted. I know it's not working because I stepped through the code and alt+tabbed to see the result. Screen updating is NOT turned off. The macro can't do its task properly, even if I could accept the bizarre offsetting pattern of pasting, because at some point a range of cells remains selected in the destination sheet, rather than one cell being selected. The copied and destination ranges don't have the same size and shape, so I get an error. I tried putting 'Range("A1").Select' in front of the above statement, and it is being ignored too. The rectangle of selected cells stays selected. TIA, Gregg Roberts |
Excel ignores VBA commands
ActiveCell and SpecialCells(xlLastCell) will return entirely different
ranges. How about this: Sub test() Dim rng As Range Set rng = ActiveCell.CurrentRegion Set rng = rng.Offset(rng.Rows.Count - 1, rng.Columns.Count - 1) Set rng = rng.Cells(1, 1) 'Don't try to select a cell offset by -7 if there 'you're not in column 8 or greater If rng.Column 7 Then rng.Offset(1, -7).Select End If End Sub -- Dianne In , Gregg Roberts typed: Hi, I am running into the frustrating problem I've seen occasionally where Excel simply doesn't do what I'm telling it to do. I am working with a lot of data and opening one "data" workbook at a time out of perhaps 40 in a folder, with four other workbooks open at any one time, in which I am accumulating filtered data from the "data" workbook. Is it possible there is a memory leak or something that is overwhelming Excel with no error message? I am using Set statements with Workbook variables, but I am re-using only five Workbook variables. Should I set them to Nothing between the times when I re-assign them to a new workbook? The statement ActiveCell.SpecialCells(xlLastCell).Offset(1, -7).Select is being ignored. As a result, data gets pasted further and further to the right and above where it should get pasted. I know it's not working because I stepped through the code and alt+tabbed to see the result. Screen updating is NOT turned off. The macro can't do its task properly, even if I could accept the bizarre offsetting pattern of pasting, because at some point a range of cells remains selected in the destination sheet, rather than one cell being selected. The copied and destination ranges don't have the same size and shape, so I get an error. I tried putting 'Range("A1").Select' in front of the above statement, and it is being ignored too. The rectangle of selected cells stays selected. TIA, Gregg Roberts |
Excel ignores VBA commands
Hi Dianne,
Based on the help file, the SpecialCells method gives the same result whether the object it is used on is Cells or ActiveCell. I am using this exact same statement to get the row number of the last row of data in the "data" workbook, so I can test to see whether I have enough rows left in the destination workbook to my pasting, and it works consistently. Also, the statements being ignored are only ignored after they have been executed many times in a loop already. As I wrote, even the statement Range("A1").Select is also being ignored. The range of cells selected in the destination workbook/sheet stays selected after this statement is "executed." This is the level of weirdness that is happening. While I waited for an answer from the NG I changed my statement to: Selection.End(xlDown).Offset(1, 0).Select Again, the statement works for a while, and then stops working for no apparent reason. There are always eight columns in the copied range, hence no reason to test for that. Gregg |
Excel ignores VBA commands
Hi Gregg,
Without seeing the code, it's difficult to say what may be causing your problem. But when opening/closing all those workbooks (and having others open at the same time), you should be especially careful to fully-qualify your Range references. I would suggest avoiding ActiveCell, ActiveSheet, Selection, and other unqualified references, as they will not always refer to what you think they will (unless you actually activate a Workbook, then Activate a Worksheet within the Workbook, which is tedious and unnecessary). In addition, it is almost never necessary to Select or Activate anything in Excel - you can just work directly on the objects themselves. Avoiding selecting or activating objects will speed your code up as well. Here's a simplified example of how I deal with opening/using multiple Workbooks: Sub Test() Dim wb1 As Workbook Dim ws1 As Worksheet Dim wb2 As Workbook Dim ws2 As Worksheet Set wb1=Workbooks.Open("C:\mysource.xls") Set ws1=wb1.Worksheets("Source") Set wb2=Workbooks.Open("C:\mydest.xls") Set ws2=wb2.Worksheets("Dest") ws2.Range("A1").Value = ws1.Range("A1").Value Set ws2=Nothing wb2.Close SaveChanges:=True Set wb2=Nothing Set ws1=Nothing wb1.Close SaveChanges:=False Set wb1=Nothing End Sub NOTE: I didn't type this into the VBE, so I may have made a syntax error or two. But hopefully it conveys my point. If you fully-qualify everything in this way, you shouldn't run into intermittent problems like the ones you've been experiencing. Unless something else is wrong in the code, which happens way too often for me. <g -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Gregg Roberts wrote: Hi Dianne, Based on the help file, the SpecialCells method gives the same result whether the object it is used on is Cells or ActiveCell. I am using this exact same statement to get the row number of the last row of data in the "data" workbook, so I can test to see whether I have enough rows left in the destination workbook to my pasting, and it works consistently. Also, the statements being ignored are only ignored after they have been executed many times in a loop already. As I wrote, even the statement Range("A1").Select is also being ignored. The range of cells selected in the destination workbook/sheet stays selected after this statement is "executed." This is the level of weirdness that is happening. While I waited for an answer from the NG I changed my statement to: Selection.End(xlDown).Offset(1, 0).Select Again, the statement works for a while, and then stops working for no apparent reason. There are always eight columns in the copied range, hence no reason to test for that. Gregg |
Excel ignores VBA commands
Right -- SpecialCells is independent of what is currently selected.
Have a look at your code with Jake Marx's comments in mind. I don't know why your code sometimes doesn't execute, but it is very likely that if you're relying on a cell or sheet being selected in order for your code to work, that's where things are going haywire. So instead of this: Selection.End(xlDown).Offset(1, 0).Select something more like this: Dim wb as Workbook dim ws as Worksheet set wb = TheWorkbookYouWantThisSetTo set ws = wb.TheWorksheetYouWantThisSetTo ws.Range("A1").End(xlDown).Offset(1,0).value = "whatever" I very rarely select anything in my code. Post back if you need clarification on anything or if you want us to look at more code. -- Dianne In , Gregg Roberts typed: Based on the help file, the SpecialCells method gives the same result whether the object it is used on is Cells or ActiveCell. I am using this exact same statement to get the row number of the last row of data in the "data" workbook, so I can test to see whether I have enough rows left in the destination workbook to my pasting, and it works consistently. Also, the statements being ignored are only ignored after they have been executed many times in a loop already. As I wrote, even the statement Range("A1").Select is also being ignored. The range of cells selected in the destination workbook/sheet stays selected after this statement is "executed." This is the level of weirdness that is happening. While I waited for an answer from the NG I changed my statement to: Selection.End(xlDown).Offset(1, 0).Select Again, the statement works for a while, and then stops working for no apparent reason. There are always eight columns in the copied range, hence no reason to test for that. Gregg |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com