VBAで足した数値を求めたい

質問

・シート１
○○No　　番号

番号の様式
000-000-000
になっています。

【メインシート】
メインのページより、
設定１
設定２
から選択し、選択した該当（シート１より）「選択１or選択２（○○No）」のセルから
最大値を１プラスした数字を求めたい。

【シート１】
データが、「100-000-001」だった場合、
設定１を選択すると、
「100-000-002」とメインページに出力させたいと考えています。

またデータが「100-000-999」だった場合は、
数値をリセットし、「100-001-001」としたいです。

わかりづらくて申し訳ございませんが、
ご教授のほどよろしくお願いいたします。

回答No.4

real beatin（@realbeatin） · ベストアンサー

＃３です。補足欄拝見しました。ご苦労様です。

[番号]列は昇順に並んでいる、という前提のようですので、
「最大値」ではなく、
各[種別]毎に「最終行である番号」を基準に、
１加算した新しい付番を返すようにします。

> ... 「100-999-999」だった場合は、
> 「101-000-001」とせずに、エラーを吐くようにしたいです。
この追加要求については、
差し当たり、出力先セルの値が、エラー値 #N/A になるように書きました。
ご確認をお願いします。

' ' ///

Sub Re8958570()
Dim Target As Range, c As Range
Dim vClass, vRtn

　　' ' 「メインシートのA2」で選択された[種別]
　　' ' ◆要指定◆""で括ったシート名◆要指定◆""で括ったセル参照
　　vClass = Sheets("メインシート").Range("A2").Value

　　With Sheets("シート１")　'　◆要指定◆""で括ったシート名
　　　　' ' [種別]のデータ領域すべてをセル範囲として取得
　　　　' ' ◆要指定◆""で括ったセル参照２ヶ所 = "A2:A" ？、 = "A" ？
　　　　Set Target = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
　　End With

　　' ' [種別]のデータ領域に対して、選択された[種別]のうち最下行位置にあるセルを、検索
　　Set c = Target.Find( _
　　　　　　　　　　What:=vClass, After:=Target(1), _
　　　　　　　　　　LookIn:=xlValues, LookAt:=xlWhole, _
　　　　　　　　　　SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
　　　　　　　　　　MatchCase:=False, MatchByte:=False)

　　' ' 万が一、選択された[種別]が見つからない場合
　　' ' ◆要指定◆""で括ったメッセージ内容とタイトル
　　If c Is Nothing Then MsgBox "指定の[種別]に該当するデータは見つかりません。", vbExclamation, "Title": Exit Sub

　　' ' 結果（新しい付番）取得
　　' ' ◆要指定◆ 列の位置関係について、[種別]から見た[番号]の相対位置 = 1 ？
　　vRtn = NextID(c.Offset(, 1).Value)

　　' ' 結果出力
　　' ' ◆要指定◆""で括ったシート名◆要指定◆""で括ったセル参照
　　Sheets("メインシート").Range("B2").Value = vRtn
End Sub

Function NextID(ByVal v)
　　' ' "-"をトル
　　v = Replace(v, "-", "")
　　' ' 数値化して１加算
　　v = Val(v) + 1
　　' ' 「「100-999-999」だった場合は...エラーを吐くようにしたいです。」
　　If v Mod 1000000 = 0 Then
　　　　NextID = CVErr(xlErrNA)　'　エラー値 #N/A を返す。
　　　　Exit Function
　　End If
　　' ' 「またデータが「100-000-999」だった場合は、数値をリセットし、「100-001-001」としたいです。」
　　If v Mod 1000 = 0 Then v = v + 1

　　NextID = Format$(v, "0-000-000")
End Function

' ' ///

お礼

ご教授頂いた内容で解決しました。
ありがとうございます。(^-^)
適切なアドバイスをしていただいた、
realbeatin さんをBAにしたいと思います。

回答No.3

real beatin（@realbeatin）

こんにちは。

一例でお応えします。
ご説明の内容については解釈に幅があって、
こちらの解答が一発で要求に応えている確率は７％程度だと診ていますが、
期待した答えでなかったとしても、補足欄に情報を追加するなどして、
やりとりの中で答えを見つけるよう努めてください。
補足あれば再度お応えします。
まずは、
ここがハッキリすれば、一通りに絞った解決に導けるであろう疑問点の整理から、、、。
■■■
> ・シート１
>○○No　　番号
"シート１"は、実際のシート名でしょうか？
[○○No]という項目がＡ列、
[番号]という項目がＢ列、という意味でしょうか？

> 番号の様式
> 000-000-000
> になっています。
番号というのは、セルの値が文字列値としての"000-000-000"という意味でしょうか？
それとも、セルの値は数値で、[セルの表示形式]に[000-000-000]を指定してある
という意味でしょうか？

> 【メインシート】
"メインシート"は、実際のシート名でしょうか？
> メインのページより、
"メインのページ"は、"メインシート"とは別のフォーム等のことでしょうか？
> 設定１
> 設定２
> から選択し、選択した該当（シート１より）「選択１or選択２（○○No）」のセルから
> 最大値を１プラスした数字を求めたい。
「設定１」「設定２」
「選択１」「選択２（○○No）」
それぞれに処理を分岐したい、ということは察することが出来ますが、
どのように処理を分けたいのか書かれていません。
また、それらを判別する為には、何処の何を調べれば判るのか情報がありません。

> 【シート１】
> データが、「100-000-001」だった場合、
> 設定１を選択すると、
「設定２」を選択した場合、はどうしたいのでしょう？
「選択１」「選択２（○○No）」はどのように関連付ければいいのでしょう？
> 「100-000-002」とメインページに出力させたいと考えています。
"メインページ"が仮にシートなのだとして、何処のセルなのか分かりません。

> またデータが「100-000-999」だった場合は、
> 数値をリセットし、「100-001-001」としたいです。
右端３桁には、"...-000"のように、0に相当する数字を設定しないで、
"001"-"999"という999単位で変位するルール、なのだということは解ります。
左端３桁、中央３桁は"000"-"999"、1000単位で変位するということでよいのでしょうか？
■■■

こちらで想定した、可能性が高そうな解釈を繋ぎ合わせた、設問。
■■■
"シート１" "メインシート"というシート名の２つのワークシートがある。
シート"シート１"
　Ａ列には、[○○No]という項目、
　Ｂ列には、[番号]という項目、
　が、それぞれあり、どちらも項目タイトルが１行め、データは２行めから。
　データの内容は、どちらも共通で、文字列値として
　"000-000-000" - "999-999-999" に相当する値が設定されている。
　それ以外のフォーマットのデータや空行は存在しない。
シート"メインシート"
　"設定１" "設定２" という名前の２つのボタンが配置されている。
　どちらのボタンを押した場合でも、
　結果の出力先は、"メインシート"の（仮に）Ｂ２セルである。
処理内容（"設定１"を選択した場合）。
　"シート１"Ｂ列[番号]項目にある付番の最大値を探し、
　最大値に１加算した新たな付番を"メインシート"のＢ２セルに返す。
　ただし、新たな付番の右端３桁が"000"である時は、
　右端３桁を"001"に置き換える。
処理内容（"設定１"以外を選択した場合）。
　内容は"設定１"に対する処理に似ているが、
　元データとして参照するセル範囲などが異なるので、
　"設定１"に関連付けたマクロを基に参照するセル範囲などの指定を
　書き換えたマクロを別途用意するように質問者側で適宜運用する。
■■■

とりあえず、こちらの想定に頼るものですが、
ボタンでなくっても、文字列値でなくっても、動くように
多少の汎用性を持たせたマクロを書いて、一応の動作確認と検証を済ませました。
'　要指定◆　　と、書かれた行では、各種パラメータの
指定（または確認）を、そちらで適切に済ませてから、
動作を確認してください。
因みに、"000-000-000"の意味する所が[セルの表示形式]である場合は、
より簡単な記述で済みます。
或いは、実際のニーズによっては、多少難度を高めた処理が必要になあるのかも知れません。
不足があったとしても、補足があれば対応します。
ひとまず、以下のマクロを試してみて下さい。

' ' ///

Sub Re8958570()　'　実行するマクロ
Const シート1 = "シート１"　'　要指定◆""で括ったシート名
Const 番号列 = 2　'　要指定◆[番号]項目がシート１の何列めにあるか
Const データ先頭行 = 2　'　要指定◆[番号]項目のデータ先頭行が何行めにあるか
Dim c As Range
Dim vMax As Variant

　　With Sheets(シート1)
　　　　For Each c In Range(.Cells(データ先頭行, 番号列), .Cells(Rows.Count, 番号列).End(xlUp))
　　　　　　If c.Value > vMax Then vMax = c.Value
　　　　Next
　　End With

Const メインシート = "メインシート"　'　要指定◆""で括ったシート名
Const 出力先 = "B2"　'　要指定◆""で括った出力先セル参照

　　Sheets(メインシート).Range(出力先).Value = NextID(vMax)
End Sub

Function NextID(ByVal v)　'　新しい付番を返す関数
　　v = Replace(v, "-", "")
　　v = Val(v) + 1
　　If v Mod 1000 = 0 Then v = v + 1
　　NextID = Format$(v, "0-000-000")
End Function

' ' ///

補足

回答ありがとうございます。
だいたいはご明察のとおりです。

希望する内容が少しだけ組立ができました。

実際の内容をもう少し書かせて頂きますと、
シート１には、
データ種別と番号列があります。

番号列は、データ種別の番号を混合してあります。
例えば、

[データ先頭][番号列]
種別１　　　100-000-001
種別１　　　100-000-002
種別３　　　300-000-001
種別１　　　100-000-003
種別１　　　100-000-004
種別１　　　100-000-005
種別１　　　100-000-006
種別３　　　300-000-002
種別２　　　200-000-001
種別２　　　200-000-002
種別２　　　200-000-003
種別２　　　200-000-004

となっています。

この状態であるので、
ただ単に、最終行である番号を足しただけでは、
できないです。

メインシートのA2に
「種別1」が選択されていた場合、
ボタンを押したあとに、シート１より
「種別１」に該当している箇所を探し出して、
この場合、種別１の最終行は「100-000-006」であるので、
「100-000-007」として出力させたいです。

「種別２」と選択していた場合は、
最終行は「200-000-004」であるので、
「200-000-005」として出力させたいです。

番号列のルールには、
「000-000-000」でありますが、
一番最初の3桁は、種別番号になります。
ですので、「100-999-999」だった場合は、
「101-000-001」とせずに、エラーを吐くようにしたいです。

よろしくお願いいたします。

回答No.2

noname#212058

こんな感じでしょうか。
シートへの値の入出力はそちらで工夫してください。
------------------------------------------------------------------
Dim code As String ' 元のコード
Dim addedCode As String ' 求めたコード
Dim codeNumber As Long ' 加算値を計算するための変数
code = "100-000-001"

' コードを数値に変換し、1を足す
codeNumber = CLng(Replace(code, "-", ""))
codeNumber = codeNumber + 1

' 求めた数値をコードのフォーマットに変換
addedCode = Format(codeNumber, "000-000-000")

お礼

解決しました。
回答ありがとうございました。

回答No.1

kngj1740

文字形式なのでそのままでは計算は不可かと。各要素に分解して数値に変換して演算し、文字形式に戻し合成が必要。そういう事が出来る関数があるかどうかは知りません。