wierd error I cannot figure out
 

The following code (that is only the beginning of the whole procedure) works
fine when I run it by typing Alt+F8 - Alt+r or when I run it line by line
(F8).

However, when I use the shortcut and type Ctrl+Shift+I the code executes the
line

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
geroa\geroa inventory.xls")

and it does not execute the rest.

Why is this?

wierd error I cannot figure out
 

Sorry, the initial code is

' Keyboard Shortcut: Ctrl+Shift+I

Option Explicit

Sub build()

Dim temp_workbook, d_address As String, futures_close As Single, w As Workbook
Dim c, d, f As Range

Application.ScreenUpdating = False

temp_workbook = ActiveWorkbook.Name


With Worksheets(1).Columns("M")
Set f = .Find("- Futuros", LookIn:=xlValues)
If Not f Is Nothing Then
futures_close = f.Offset(0, -10).Value
End If

Set d = .Find("Divisa plazo", LookIn:=xlValues)
If Not d Is Nothing Then
d_address = d.Address
Do
d.Value = "- Divisa plazo"
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address < d_address
End If
End With


Cells.Sort key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess

Union(Columns("B:L"), Columns("N:O"), Range(Columns("Q"),
Columns("Q").End(xlToRight))).Delete Shift:=xlToLeft

Columns("A:C").Copy

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
ge\ge inventory.xls")



"Antonio" wrote:

The following code (that is only the beginning of the whole procedure) works
fine when I run it by typing Alt+F8 - Alt+r or when I run it line by line
(F8).

However, when I use the shortcut and type Ctrl+Shift+I the code executes the
line

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
geroa\geroa inventory.xls")

and it does not execute the rest.

Why is this?

wierd error I cannot figure out
 

Antonio,

I think that you need to qualify your Worksheet, Range and Cells objects,
e.g.:

Workbooks("myBook.xls").Worksheets(1).Columns("M")
Workbooks("myBook.xls").Cells.Sort key1:=.Range("M2"), Order1:=xlAscending,
Header:=xlGuess

I suspect that your code is acting on a different workbook than you think
and so is not finding your search items, i.e., a different workbook is
active than you think.
Also, I notice that you don't use you temp_workbook variable, beyond setting
it the Activeworkbook.
hth,

Doug

"Antonio" wrote in message
...
Sorry, the initial code is

' Keyboard Shortcut: Ctrl+Shift+I

Option Explicit

Sub build()

Dim temp_workbook, d_address As String, futures_close As Single, w As
Workbook
Dim c, d, f As Range

Application.ScreenUpdating = False

temp_workbook = ActiveWorkbook.Name


With Worksheets(1).Columns("M")
Set f = .Find("- Futuros", LookIn:=xlValues)
If Not f Is Nothing Then
futures_close = f.Offset(0, -10).Value
End If

Set d = .Find("Divisa plazo", LookIn:=xlValues)
If Not d Is Nothing Then
d_address = d.Address
Do
d.Value = "- Divisa plazo"
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address < d_address
End If
End With


Cells.Sort key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess

Union(Columns("B:L"), Columns("N:O"), Range(Columns("Q"),
Columns("Q").End(xlToRight))).Delete Shift:=xlToLeft

Columns("A:C").Copy

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
ge\ge inventory.xls")



"Antonio" wrote:

The following code (that is only the beginning of the whole procedure)
works
fine when I run it by typing Alt+F8 - Alt+r or when I run it line by
line
(F8).

However, when I use the shortcut and type Ctrl+Shift+I the code executes
the
line

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
geroa\geroa inventory.xls")

and it does not execute the rest.

Why is this?

wierd error I cannot figure out
 

Hi Doug,

The procedure works fine except when run with the shortcut.

I am running this macro only on a specific worksheet and there are no
ambiguities with the addressing.

I have many more variables in the procedure, which is much longer, I have
just listed the beggining of it.

Again, the problem is only that when run with the shortcut it does not
execute fully.


"Doug Glancy" wrote:

Antonio,

I think that you need to qualify your Worksheet, Range and Cells objects,
e.g.:

Workbooks("myBook.xls").Worksheets(1).Columns("M")
Workbooks("myBook.xls").Cells.Sort key1:=.Range("M2"), Order1:=xlAscending,
Header:=xlGuess

I suspect that your code is acting on a different workbook than you think
and so is not finding your search items, i.e., a different workbook is
active than you think.
Also, I notice that you don't use you temp_workbook variable, beyond setting
it the Activeworkbook.
hth,

Doug

"Antonio" wrote in message
...
Sorry, the initial code is

' Keyboard Shortcut: Ctrl+Shift+I

Option Explicit

Sub build()

Dim temp_workbook, d_address As String, futures_close As Single, w As
Workbook
Dim c, d, f As Range

Application.ScreenUpdating = False

temp_workbook = ActiveWorkbook.Name


With Worksheets(1).Columns("M")
Set f = .Find("- Futuros", LookIn:=xlValues)
If Not f Is Nothing Then
futures_close = f.Offset(0, -10).Value
End If

Set d = .Find("Divisa plazo", LookIn:=xlValues)
If Not d Is Nothing Then
d_address = d.Address
Do
d.Value = "- Divisa plazo"
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address < d_address
End If
End With


Cells.Sort key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess

Union(Columns("B:L"), Columns("N:O"), Range(Columns("Q"),
Columns("Q").End(xlToRight))).Delete Shift:=xlToLeft

Columns("A:C").Copy

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
ge\ge inventory.xls")



"Antonio" wrote:

The following code (that is only the beginning of the whole procedure)
works
fine when I run it by typing Alt+F8 - Alt+r or when I run it line by
line
(F8).

However, when I use the shortcut and type Ctrl+Shift+I the code executes
the
line

Workbooks.Open ("c:\documents and settings\antonio\my documents\client
geroa\geroa inventory.xls")

and it does not execute the rest.

Why is this?