Is there a special rule in using call procedure macros in a
personal.xls project vs. a VBAproject assigned to a specific
worksheet? The following macro allows me to search col 3 until the
data runs out & parse out certain characters into col 4. This works
when the macro is setup for a specific but fails to run or give an
error when it's in personal.xls module. I have tried this in both
2003 & 2007 versions of excel. I would appreciate any help. Thanks.

Sub AccountType()
Dim s1 As Sheet1
Dim row As Integer
Dim sTemp As String

Set s1 = Sheet1

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub

Hi,

Try it this way:

Sub AccountType()
Dim s1 As Sheet
Dim row As Integer
Dim sTemp As String

Set s1 = ActiveWorkbook.Sheets("Sheet1")

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub


--
Hope that helps.

Vergel Adriano


" wrote:

Is there a special rule in using call procedure macros in a
personal.xls project vs. a VBAproject assigned to a specific
worksheet? The following macro allows me to search col 3 until the
data runs out & parse out certain characters into col 4. This works
when the macro is setup for a specific but fails to run or give an
error when it's in personal.xls module. I have tried this in both
2003 & 2007 versions of excel. I would appreciate any help. Thanks.

Sub AccountType()
Dim s1 As Sheet1
Dim row As Integer
Dim sTemp As String

Set s1 = Sheet1

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub

On Apr 19, 10:56 am, Vergel Adriano
wrote:
Hi,

Try it this way:

Sub AccountType()
Dim s1 As Sheet
Dim row As Integer
Dim sTemp As String

Set s1 = ActiveWorkbook.Sheets("Sheet1")

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub

--
Hope that helps.

Vergel Adriano



" wrote:
Is there a special rule in using call procedure macros in a
personal.xls project vs. a VBAproject assigned to a specific
worksheet? The following macro allows me to search col 3 until the
data runs out & parse out certain characters into col 4. This works
when the macro is setup for a specific but fails to run or give an
error when it's in personal.xls module. I have tried this in both
2003 & 2007 versions of excel. I would appreciate any help. Thanks.

Sub AccountType()
Dim s1 As Sheet1
Dim row As Integer
Dim sTemp As String

Set s1 = Sheet1

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub- Hide quoted text -

- Show quoted text -

Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet

How about:

Dim s1 As worksheet
'....
Set s1 = activesheet

I'm gonna guess that you want your code to run against the activesheet--no
matter what that sheet is (or where it is).

wrote:

Is there a special rule in using call procedure macros in a
personal.xls project vs. a VBAproject assigned to a specific
worksheet? The following macro allows me to search col 3 until the
data runs out & parse out certain characters into col 4. This works
when the macro is setup for a specific but fails to run or give an
error when it's in personal.xls module. I have tried this in both
2003 & 2007 versions of excel. I would appreciate any help. Thanks.

Sub AccountType()
Dim s1 As Sheet1
Dim row As Integer
Dim sTemp As String

Set s1 = Sheet1

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub

--

Dave Peterson

There is no Sheet object. Use

Dim s1 As Worksheet



In article . com,
wrote:

Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet

On Apr 19, 11:11 am, JE McGimpsey wrote:
There is no Sheet object. Use

Dim s1 As Worksheet

In article . com,



wrote:
Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet- Hide quoted text -

- Show quoted text -

Thanks. That fixed it--though it runs a little slow.

It should be

Dim s1 As WorkSheet


--
Hope that helps.

Vergel Adriano


" wrote:

On Apr 19, 10:56 am, Vergel Adriano
wrote:
Hi,

Try it this way:

Sub AccountType()
Dim s1 As Sheet
Dim row As Integer
Dim sTemp As String

Set s1 = ActiveWorkbook.Sheets("Sheet1")

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub

--
Hope that helps.

Vergel Adriano



" wrote:
Is there a special rule in using call procedure macros in a
personal.xls project vs. a VBAproject assigned to a specific
worksheet? The following macro allows me to search col 3 until the
data runs out & parse out certain characters into col 4. This works
when the macro is setup for a specific but fails to run or give an
error when it's in personal.xls module. I have tried this in both
2003 & 2007 versions of excel. I would appreciate any help. Thanks.

Sub AccountType()
Dim s1 As Sheet1
Dim row As Integer
Dim sTemp As String

Set s1 = Sheet1

row = 4

Do Until s1.Cells(row, 1) = ""
sTemp = s1.Cells(row, 3)
sTemp = Right(sTemp, Len(sTemp) - 4)
s1.Cells(row, 4).Value = sTemp
row = row + 1
Loop
End Sub- Hide quoted text -

- Show quoted text -

Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet

You might try:

Public Sub AccountType()
Dim rCell As Range
On Error Resume Next
With Application
.EnableEvents = False
.Calculation = False
.ScreenUpdating = False
End With
With ActiveWorkbook.Sheets("Sheet1")
For Each rCell In .Range(.Cells(4, 3), _
.Cells(.Rows.Count, 3).End(xlUp))
With rCell
.Offset(0, 1).Value = Mid(.Text, 5)
End With
Next rCell
End With
With Application
.ScreenUpdating = True
.Calculation = True
.EnableEvents = True
End With
End Sub

In article .com,
wrote:

Thanks. That fixed it--though it runs a little slow.

If your values in C4:Cxxx are always text (no formulas), you could do:

Select the range
data|text to columns
fixed width
draw a line after the 4th character (and remove any other lines)
Do not import the first field
and import the second field as text
And change the destination to be on column to the right.

In code:

Option Explicit
Sub AccountType2()
Dim myRng As Range
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range

Set wks = ActiveSheet

With wks
Set TopCell = .Range("C4")
Set BotCell = TopCell
If IsEmpty(TopCell.Offset(1, 0)) Then
Set BotCell = TopCell
ElseIf IsEmpty(TopCell.Offset(2, 0)) Then
Set BotCell = TopCell.Offset(1, 0)
Else
Set BotCell = TopCell.End(xlDown)
End If

.Range(TopCell, BotCell).TextToColumns _
Destination:=TopCell.Offset(0, 1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(4, 2))
End With

End Sub

wrote:

On Apr 19, 11:11 am, JE McGimpsey wrote:
There is no Sheet object. Use

Dim s1 As Worksheet

In article . com,



wrote:
Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet- Hide quoted text -

- Show quoted text -

Thanks. That fixed it--though it runs a little slow.

--

Dave Peterson

On Apr 19, 12:54 pm, Dave Peterson wrote:
If your values in C4:Cxxx are always text (no formulas), you could do:

Select the range
data|text to columns
fixed width
draw a line after the 4th character (and remove any other lines)
Do not import the first field
and import the second field as text
And change the destination to be on column to the right.

In code:

Option Explicit
Sub AccountType2()
Dim myRng As Range
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range

Set wks = ActiveSheet

With wks
Set TopCell = .Range("C4")
Set BotCell = TopCell
If IsEmpty(TopCell.Offset(1, 0)) Then
Set BotCell = TopCell
ElseIf IsEmpty(TopCell.Offset(2, 0)) Then
Set BotCell = TopCell.Offset(1, 0)
Else
Set BotCell = TopCell.End(xlDown)
End If

.Range(TopCell, BotCell).TextToColumns _
Destination:=TopCell.Offset(0, 1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(4, 2))
End With

End Sub





wrote:

On Apr 19, 11:11 am, JE McGimpsey wrote:
There is no Sheet object. Use

Dim s1 As Worksheet

In article . com,

wrote:
Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet- Hide quoted text -

- Show quoted text -

Thanks. That fixed it--though it runs a little slow.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Than you all for the suggestions. Yes, colC is all text so I can use
Dave's suggestion. Thx again.