Excel ignores VBA commands

  • Thread starter Thread starter Gregg Roberts
  • Start date Start date
G

Gregg Roberts

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
 
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
 
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
 
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]
 
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.
 
Back
Top