Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Programing | New Users to Excel | |||
excel programing | Excel Discussion (Misc queries) | |||
Iteration programing | Excel Programming | |||
Programing a macro to pause while running for data entry | Excel Programming | |||
programing book | Excel Programming |