Problems with macro

[fieldsy73]

I'm trying to run a macro created by an ex-employee but after weeks of
successfully running, the macro is now creating error messages. I
think the error may have arisen when I inadvertently chose to save
changes to the sheet which contains the macro. Unfortunately, I have
no experience fo VBA coding and neither does anyone else at my place
of work. I desperately need help otherwise I can't run a suite of
reports which are required daily. The first error code starts at the
line;

Cells(50, 2).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, -1).Select

I've been advised to change this to

lastRow = Range("b65536").End(xlUp).Row
range ("b50:b" & lastRow).select

However, it then highlights the next line of code

Selection.TextToColumns Destination:=Cells(1, 1),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True,
Other:=True, _
OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3,
9)), TrailingMinusNumbers:=True


Any help anyone could offer would be greatly appreciated.

John

 

[Jim Cone]

John,
The advice you got about the last row appears to be good advice
and the code as is works for me in XL2002.

Some questions...
What does the error message say?
Have you recently changed the version of Excel you are using?
What Excel version are you using?
Are you using Windows XP?
Is Column A blank?
How far down does the data go in Column B? (what is the last row)
Is there a line of code prior to the error that resembles...
"Application.DisplayAlerts = False"?
Do you have an untampered with version of the workbook?
(somebody must have made a backup copy)
Is the data different from that used previously? That is does it now
have : or ; instead of commas or spaces or some other similar change?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"fieldsy73" <[email protected]>
wrote in message
I'm trying to run a macro created by an ex-employee but after weeks of
successfully running, the macro is now creating error messages. I
think the error may have arisen when I inadvertently chose to save
changes to the sheet which contains the macro. Unfortunately, I have
no experience fo VBA coding and neither does anyone else at my place
of work. I desperately need help otherwise I can't run a suite of
reports which are required daily. The first error code starts at the
line;

Cells(50, 2).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, -1).Select

I've been advised to change this to

lastRow = Range("b65536").End(xlUp).Row
range ("b50:b" & lastRow).select

However, it then highlights the next line of code

Selection.TextToColumns Destination:=Cells(1, 1),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True,
Other:=True, _
OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3,
9)), TrailingMinusNumbers:=True

Any help anyone could offer would be greatly appreciated.
John