Excelでの採番方法

質問

Excelのユーザーフォームを使って、シートへデータを反映させるマクロを組んでいます。
そこで、データ一つ一つに番号をつけたいのですが、
B列に入力した日付（例：2014/01/06）から年の2ケタだけを参照しA列に14001、14002としていきたいです。
また、年が変わった時に番号もリセットされる方法がわかりません。
B列への入力を（2015/01/06）とした場合に、番号が15001、15002と、1から連番になる方法はありますでしょうか。

回答No.6

kagakusuki

マクロを使わずとも関数でも十分同じ事が出来ます。
　まず、連番を表示させるA列のセルの１つに次の様な関数を入力して下さい。

=IF(ISERROR(1/DAY(INDEX($B:$B,ROW()))),"",MOD(YEAR(INDEX($B:$B,ROW())),100)*100+COUNTIF($B:$B,"<"&INDEX($B:$B,ROW()))-COUNTIF($B:$B,"<"&DATE(YEAR(INDEX($B:$B,ROW())),1,1)+COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW())))+1)

　次に、上記の関数を入力したセルをコピーして、A列において連番を表示させる全てのセルに貼り付けて下さい。

　以上です。

回答No.5

bunjii

>B列に入力した日付（例：2014/01/06）から年の2ケタだけを参照しA列に14001、14002としていきたいです。
マクロを使うまでもありません。
B列に日付を入力するとA列にデータ番号が表示される関数式をセットしておくだけで良いと思います。
1行目は項目が記載されるものとして2行目からA列に次の式を入力しておきます。

A2=IF(B2="","",VALUE(CONCATENATE(RIGHT(YEAR(B2),2),TEXT(IFERROR(IF(YEAR(B1)=YEAR(B2),MOD(A1,1000)+1,1),1),"0000"))))

A2を下へオートフィルで必要数コピーします。

回答No.4

masatsan

Ｎｏ３さんがおっしゃるように、やりたいことがわからない。
データを反映って何？
Ａ列に14001．。。。はできますが
”年が変わったときにリセット”ってどういう意味？
”連番になる”ってＢ列が連番だったら連番になるけど。

ともかくやりたいことが全く不明。

回答No.3

keithin

ん？
「日付シリアル」とは、あなたがＢ列に記入したい「具体的な日付」のことです。

そもそも
あなたが「具体的に何をしたい」のかご相談ではまったく不明瞭なので、適切な回答が出来なくて困ってます。

あなたが言ってるのは「Ｂ列に日付を記入したい」「Ａ列に年ごとの連番を振りたい」だけですが、
○Ｂ列に記入する「日付」って？具体的には例えば「今日の日付」でいいんですか。それとも例えばユーザーフォームに何か日付を記入させたのを転記してるとかですか
○Ｂ列の「どこに」日付を記入したいんですか。たとえば最下行とかでいいんでしょうか

そういった具体的な内容を逐一説明してもらってから具体的なマクロを回答してたんじゃ、いつまでたっても終わりませんので、説明不足については全部こっちで考えて次のようなマクロで回答にします。

private sub CommandButton1_Click()
　with cells(rows.count, "B").end(xlup)
　　.offset(1) = date
　　.offset(1, -1) = format(date, "yy") * 1000 + application.count(range("B:B")) - application.countif(range("B:B"), "<" & dateserial(year(date), 1, 1))
　end with
end sub

判らないなら勝手に改変しないで、まずそのまま使ってください。
実際に動かしてみた後、意図と違うところは適切に応用してください。

回答No.2

tom04

こんばんは！
単純に・・・
あらかじめA列に数式を入れておいてはダメですか？

ユーザーフォームのコマンドボタン押下で、B列の２行目以降にシリアル値が入るとします。

A2セルに
=IF(B2="","",MOD(YEAR(B2),100)*1000+SUMPRODUCT((YEAR(B$2:B2)=YEAR(B2))*1))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておいても大丈夫だと思います。

※　セルの左上に出現するエラーチェックオプションのマークが目障りであれば
メニュー　→　ファイル　→　オプション　→　数式　→　「エラーチェックルール」
→　「領域内のセルを除いた数式」と「空白セルを参照する数式」のチェックを外しておきます。

ご希望の方法でなかったらごめんなさいね。m(_ _)m

回答No.1

keithin

ふつーに「その年の件数」を数えるだけです。

作成例：
with cells(rows.count, "B").end(xlup).offset(1)
.value = Ｂ列に入力したい日付シリアル
.offset(0, -1) = format(.value, "yy") * 1000 + application.countif(range("B1:B" & .row), "<=" & .value) - application.countif(range("B1:B" & .row), "<" & dateserial(year(.value), 1, 1))
end with

補足

ありがとうございます。
すみません。B列に入力したい日付シリアルというものがいまいちよくわかりません。どのようにすればよろしいでしょうか。
教えていただいたコードですが、《》を削除して張り付けすればよろしいでしょうか。
再度御教授頂けますと幸いです。

《with Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = txt番号.Value≫
.Offset(, 1).Value = txt年月日.Value
.Offset(, 2).Value = combo担当.Value