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?

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>