|
▼マリモ さん:
以下のような簡素化もできます。
Range("C1").Select
ActiveCell.FormulaR1C1 = "0"
Range("D1").Select
ActiveCell.FormulaR1C1 = "1"
Range("C1:D1").Select
Selection.AutoFill Destination:=Range("C1:DS1"), Type:=xlFillDefault
↓
Range("C1").Value = 0
Range("D1").Value = 1
Range("C1:D1").AutoFill Destination:=Range("C1:DS1"), Type:=xlFillDefault
↓
Range("C1") = 0
Range("C1").Resize(, 121).DataSeries
Range("A1").Select
ActiveCell.FormulaR1C1 = "code"
Range("B1").Select
ActiveCell.FormulaR1C1 = "cyomei"
Range("C1").Select
ActiveCell.FormulaR1C1 = "sex"
Range("D1").Select
ActiveCell.FormulaR1C1 = "age"
Range("E1").Select
ActiveCell.FormulaR1C1 = "pop"
↓
Range("A1").Value = "code"
Range("B1").Value = "cyomei"
Range("C1").Value = "sex"
Range("D1").Value = "age"
Range("E1").Value = "pop"
↓
Range("A1:E1").Value = Array("code", "cyomei", "sex", "age", "pop")
また、簡素化といえるかどうか、実際のコード実行数はかわらないというか、むしろ増えますが
変換数が今回のような3種類ではなく、多い場合には有効かも。
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="男", Replacement:="m", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="女", Replacement:="f", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
↓
まず、変数定義
Dim aryCvt1 As Variant
Dim aryCvt2 As Variant
Dim idx As Long
で、実行コードを
aryCvt1 = arrat(" ", "男", , "女")
aryCvt2 = arrat("", "m", "f")
For idx = LBound(aryCvt1) To UBound(aryCvt1)
Cells.Replace What:=aryct1(idx), Replacement:=aryct2(idx), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
|
|