Macro programing
 

I am uploading many different files into my database.
These files come in a spreadsheet. There is one
particular column that requires the conversion of a
single didget product code to a discription.

I want to know how to write a macro that I would be able
to select that column, run it and it would convert the
data for me. The data would need to convert as follows:

1 = Personal property
3 = personal property and vehicle
5 = vehicle and personal property
7 = cosigner
8 = intangible personal property
9 = draft check
A = merchandise/household goods
C = clothing (sales finance)
F = fixtures (sales finance)
J = musical equipment (sales finance)
Z = residence (mobile home sales finance)

Thank you in advance,
JT

Macro programing
 

something like:(assuming the correct range of cells is selected)
Sub ijb()
Dim myCell As Range

For Each myCell In Selection
Select Case CStr(myCell.Value)
Case "1"
myCell.Value = "Personal property"
Case "2"
myCell.Value = "personal property and vehicle"
etc
etc
End Select
Next

End Sub

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help


"Jason Trolian" wrote in message
...
I am uploading many different files into my database.
These files come in a spreadsheet. There is one
particular column that requires the conversion of a
single didget product code to a discription.

I want to know how to write a macro that I would be able
to select that column, run it and it would convert the
data for me. The data would need to convert as follows:

1 = Personal property
3 = personal property and vehicle
5 = vehicle and personal property
7 = cosigner
8 = intangible personal property
9 = draft check
A = merchandise/household goods
C = clothing (sales finance)
F = fixtures (sales finance)
J = musical equipment (sales finance)
Z = residence (mobile home sales finance)

Thank you in advance,
JT

Macro programing
 

cRows = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 to cRows
With Cells(i,"A")
Select Case .Value
Case "1 : .Value = "Personal property"
Case "3 : .Value = "personal property and vehicle"
Case "5 : .Value = "vehicle and personal property"
Case "7" : .Value = "cosigner"
Case "8" : .Value = "intangible personal property"
Case "9" : .Value = "draft check"
Case "A" : .Value = "merchandise/household goods"

Case "C" : .Value = "clothing (sales finance)"

Case "F" : .Value = "fixtures (sales finance)"
Case "J" : .Value = "musical equipment (sales finance)"

Case "Z" : .Value = "residence (mobile home sales finance)"
End Select
End With
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jason Trolian" wrote in message
...
I am uploading many different files into my database.
These files come in a spreadsheet. There is one
particular column that requires the conversion of a
single didget product code to a discription.

I want to know how to write a macro that I would be able
to select that column, run it and it would convert the
data for me. The data would need to convert as follows:

1 = Personal property
3 = personal property and vehicle
5 = vehicle and personal property
7 = cosigner
8 = intangible personal property
9 = draft check
A = merchandise/household goods
C = clothing (sales finance)
F = fixtures (sales finance)
J = musical equipment (sales finance)
Z = residence (mobile home sales finance)

Thank you in advance,
JT

Macro programing
 

Works perfectly. Thank you very much

JT
-----Original Message-----
something like:(assuming the correct range of cells is
selected)
Sub ijb()
Dim myCell As Range

For Each myCell In Selection
Select Case CStr(myCell.Value)
Case "1"
myCell.Value = "Personal property"
Case "2"
myCell.Value = "personal property and vehicle"
etc
etc
End Select
Next

End Sub

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help


"Jason Trolian"
wrote in message
...
I am uploading many different files into my database.
These files come in a spreadsheet. There is one
particular column that requires the conversion of a
single didget product code to a discription.

I want to know how to write a macro that I would be
able
to select that column, run it and it would convert the
data for me. The data would need to convert as
follows:

1 = Personal property
3 = personal property and vehicle
5 = vehicle and personal property
7 = cosigner
8 = intangible personal property
9 = draft check
A = merchandise/household goods
C = clothing (sales finance)
F = fixtures (sales finance)
J = musical equipment (sales finance)
Z = residence (mobile home sales finance)

Thank you in advance,
JT



.

Macro programing
 

If I were doing it manually, I'd just do Edit|Replace 11 times.

As a macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim iCtr As Long
Dim myFroms As Variant
Dim myTos As Variant

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox("Select a cell in your column:", _
Default:=ActiveCell.Address(0, 0), _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
End If

myFroms = Array(1, 3, 5, 7, 8, 9, "a", "c", "f", "j", "Z")
myTos = Array("Personal property", _
"personal property and vehicle", _
"vehicle and personal property", _
"cosigner", _
"intangible personal property", _
"draft check", _
"merchandise/household goods", _
"clothing (sales finance)", _
"fixtures (sales finance)", _
"musical equipment (sales finance)", _
"residence (mobile home sales finance)")

If UBound(myFroms) < UBound(myTos) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myFroms) To UBound(myFroms)
myRng.Replace What:=myFroms(iCtr), _
Replacement:=myTos(iCtr), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub


Jason Trolian wrote:

I am uploading many different files into my database.
These files come in a spreadsheet. There is one
particular column that requires the conversion of a
single didget product code to a discription.

I want to know how to write a macro that I would be able
to select that column, run it and it would convert the
data for me. The data would need to convert as follows:

1 = Personal property
3 = personal property and vehicle
5 = vehicle and personal property
7 = cosigner
8 = intangible personal property
9 = draft check
A = merchandise/household goods
C = clothing (sales finance)
F = fixtures (sales finance)
J = musical equipment (sales finance)
Z = residence (mobile home sales finance)

Thank you in advance,
JT

--

Dave Peterson