ちょっと変わったマニアな作品が集結

特定列に着目し、何らかのデータのある最終行を取得する場合は
Cells(Rows.Count, "A").End(xlUp).Row
ですが、「特定の」文字列あるいは数値のあるセル、複数ある場合は最後の行を知りたいのです。

詳細



え  ←ここの行番号が知りたい
#N/A
上記のようにA列に文字列あるいは数値が並んでます。その中にエラー記号である#N/A、#DIV/0!、、#VALUE!が混在します。これらエラー表示を除いた最終行、上例では「え」のある行を取得したいのです。ちなみに#N/A等はコピペ処理により文字列?になっている事もあります。

このQ&Aに関連する最新のQ&A

A 回答 (8件)

#2,5,7です。


何度もすみませんが、ふと気付けば未だどなたも
【普通に、下からループして探す】版を掲げてないようなので、
(質疑の感触としては、今回の課題にそこまでは必要ないだろうと思ってはいますが)
一例として(あくまで参考として)上げておくことにします。
無論これは[数式の戻り値]と[定数値]両方に対応させる意図で書いたものです。
(Select Case の中身は2種類からお好みで、、、)

' ' =================================

Sub Re8914431a()
Const nTop As Long = 1 ' それより上を探す必要のない上限の行を指定
Dim i As Long
  With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) ' 必ず1行めを先頭にした1列の範囲を指定
    For i = .Count To nTop Step -1
      Select Case VarType(.Cells(i))
      Case vbEmpty, vbError ' vbEmpty=0,vbError=10 ' op#1/2
      Case Else ' op#1/2
        Exit For ' op#1/2
      End Select ' op#1/2
    Next i
  End With
  If i < nTop Then i = nTop
  MsgBox i
End Sub

''      Case 5 To 8, 11 ' vbDouble=5,vbCurrency=6,vbDate=7,vbString=8,vbBoolean=11 ' op#2/2
''        Exit For ' op#2/2
''      End Select ' op#2/2

' ' =================================


以上です。お騒がせしました。
    • good
    • 0
この回答へのお礼

ありがとうございます。
これでいきます。

お礼日時:2015/02/08 13:46

WindFaller様へ


ありがとうございました。
御返事頂いた#6の内容に関しては私にも十分理解できるものでした。
#6のコードも読み込みましたが、私個人としてはとても好きな書き方でした。
#2では決め打ち型の回答を意図していた私でしたが、少し説明が足りなかったかも知れませんね。
エラートラップを省略した理由や、[定数値]に限定した記述であることの
説明ぐらいは書いておくべきだったと、今になって思っています。
なるべく汎用的で、言わば性悪説的で卒のないものを提示したい、
という回答スタイルを、以前は(出来るかは別で)目指していましたが、
最近は、なるべく要点を絞って
伝わり易い(自身で出来る理解に近づかせてあげられる)回答を心掛けよう、
という風に私の考えも少し変わってきました(まだまだ出来てないんでしょうけれど)。
そういう意味で尺度の持ち様は色々ある、ということも踏まえた上で、
優しくお答えいただいたものだと受け止めましたので、
尚ありがたく存じます。感謝しております。
今後ともよろしくお願いします。  cj 拝
    • good
    • 0

こんばんは。



High_Score様へ
別所で書きました、UsedRangeそのままでは、最終行は、確実には取れません。
UsedRangeは、使用している範囲ですから、必ず、A1からだとは限りません。逆に、A1にダミーでも置かないといけません。また、A列目にUsedRangeが掛かっても、他の場所のセルが最終行にあれば、
A列目は空白を指しています。したがって、UsedRangeではうまくありません。

#5のcj_mover様のご質問ですが、
>> SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなる>はずです。
>どのような状況のことを仰るのか、私には理解(想定)できていません。

昨日、SpecialCellsで答えを導き出そうとしていましたが、出来があまり良くなかったというか、スッキリした内容にならないのでした。今回は設問には出てきていない例ですが、[定数・数式]混在で、1列に飛び飛びの値が散在してい場合です。

プログラミングとしては、本来、常識的に考える自体を想定して作るものだと思います。
SpecialCellsは、本来、目視で確認して、その該当するものを、[定数と数式]で分けて扱う、ワークシート用の組み込みマクロだといっても良いかと思います。

いろいろな想定の中で、[定数・数式]の片方がなければ、SpecialCells は、実行時エラーをはきます。むろん、[定数・数式]混在している場合には、SpecialCellsは、二つ使わないといけません。それぞれが、エラーを出すことを想定して作らなければならないし、仮に、[定数・数式]の両方のオブジェクトが存在していて、それらを、Unionで統合したとしても、今度は、そのAreasの順番が、セル位置とは順序どおりにならないのでは、とてもややこしくなってしまいます。

あえて、昨日作ったお粗末な内容を書きますが、Unionで統合することはやめ、以下のように比較するだけにしました。しかし、このようなコードを誰が評価するか、と思うのです。

それで、結局、昨日、4つ作った中で、cj_moverさんがご指摘になった、ループで探していく方法に落ち着きました。

'//出せなかったサンプル例
Sub TestSample2()
 Dim c1 As Range
 Dim c2 As Range
 Dim Rng As Range
 Dim Ret As Variant
 Dim i As Long
 Dim a, b
 On Error Resume Next '←エラートラップ
 Set c1 = Columns("A").SpecialCells(xlCellTypeFormulas, 7)
 Set c2 = Columns("A").SpecialCells(xlCellTypeConstants, 7)
 On Error GoTo 0
  If c1 Is Nothing And c2 Is Nothing Then
     Ret = "N/A"
  ElseIf Not c1 Is Nothing Or Not c2 Is Nothing Then
    If Not c1 Is Nothing Then
     a = LastRowFind(c1)
    End If
    If Not c2 Is Nothing Then
     b = LastRowFind(c2)
    End If
    Ret = IIf(a >= b, a, b)
  End If
 MsgBox "Last Row: " & Ret
End Sub

Function LastRowFind(r As Range)
Dim Ret As Variant
Dim i As Long
   For i = 1 To r.Areas.Count
   With r.Areas(i)
    Ret = .Cells(.Cells.Count).Row
   End With
   Next
 LastRowFind = Ret
End Function
'//
***********
シート上の見本データ
A列
--


   ←空白値(Empty)※コピー&ペーストすれば、「空白文字」が混入しています。




  ←空白値(Empty)
  ←空白値(Empty)
#N/A
#DIV/0!
#VALUE!
    • good
    • 0
この回答へのお礼

ありがとうございます。
UsedRangeは調べてみましたが、書式変更だけでも使ったことになるようで、使用は止めました。値だけならCells.ClearContentsの代わりに使用領域だけクリア出来ると思ったのですが。

>←空白値(Empty)※コピー&ペーストすれば、「空白文字」が混入しています。
確認してみて驚きました。数式「=""」をコピー&値のみペーストでCells(Rows.Count,"A").End(xlUp).Rowが反応します。正直、長さゼロ文字列に反応するのは仕様に問題があると思います。

お礼日時:2015/02/07 11:34

#2です。

お礼欄拝見しました。

> コピペで文字列になるというのは、例えば数式で=1/0などと書けば #DIV/0!と表示されるので、これをコピーし他セルで値のみペーストを選んで表示されるものです。あるいはvlookupで該当が無い場合の#N/Aのコピペとか。

> 無理な場合は、数式側で=IF(Iserror(vlookup), vlookup(), "")等で対処するしかありませんが、他人も使うファイルなので出来れば手を加えたくありません。

多少、誤解があるように思えるので補足しますが、、、。
セルの値を分類する場合には、
 [数式の戻り値]なのか[定数値]なのか、という分け方があります。
もう一つがデータ型での分類ですが、代表的なものに、
 [数値]※1[文字列値][論理値][エラー値]があります。
誤解されているように思われるのは、
値貼付けを含むコピペでは
[数式の戻り値]か[定数値]どうかに関わらず、
データ型が変わるようなことは基本的にありません。※2
=1/0という数式が置かれたセルの戻り値は、エラー値の#DIV/0!ですが、
これをコピーして値貼付けをしたとしても、それは、
[エラー値]であり[定数値]です。
#N/Aでも同様です。
見た目で[文字列値]のように感じてしまったとしても、
実際の値は[エラー値]です。
#VALUE!
#REF!
#DIV/0!
#N/A
のようなエラー値を示す文字列を、
セルに貼り付けたり、文字としてタイピングして入力したりしても、
そのセルの値は[エラー値]であり[定数値]です。
表計算アプリであるExcelにとっては、[エラー値]という特別な値を
扱い易くする為に(一種のショートカットとして)
#N/Aのような文字入力を可能にしている、という風に考えてください。
逆に、文字列値としての"#N/A"がセルに設定したりすると、
非常に特殊で、扱い難いファイル、として敬遠されるものだと思います。

もう一点、
ご提示の数式'=IF(Iserror(vlookup), vlookup(), "")'についてですが、
エラーだった場合の[数式の戻り値]は[文字列値]です。
この値をコピーして値貼付けをしたとしても、それは、
[文字列値]としての"長さ0の文字列"であり[定数値]です。
見た目が空白に見えても、実際には[文字列値]ですので、
例えば、range.SpecialCellsメソッド等でも、[文字列値]としてヒットすることになります。

似たような例で、
日付シリアル値としての 2015/2/6 と
文字列値としての "2015/2/6" と
見た目は同じように見えても実は別の値である場合等もあります。
このように、
見た目の[表示文字列]から想像する値と実際の[値]とが違う場合は多々ありますし、
VBAでもExcel数式でも、それらの違いをよく理解した上で扱う必要があります。

 ※1 : この場合の[数値]というのは、普通の[数値]の他に、
      [通貨][日付][時刻]等のように内部的に数値であるデータ型を含んでいます。
 ※2 : 基本的ではない特殊な例として、文字列値としての"#N/A"をセルに確定するには、
      事前にセルの[表示形式]を[文字列]に設定しておいて、
      セルを直接[入力モード]にして(数式バーなどに)#N/Aを入力することで
      可能ではあります。が、普通はこんなことしない方がいいです。

補足としては以上になりますが、
エラー値だけを振るい落とす、という要求であったなら、
range.SpecialCellsメソッドを用いた#2の方法で十分だと考えています。

以下、蛇足ですが、
#3様の、
> SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなるはずです。
どのような状況のことを仰るのか、私には理解(想定)できていません。
具体的に書いて貰えれば、こちらも勉強になるのになぁ、
という思いで話に触れました。他意はまったくありません。
    • good
    • 0
この回答へのお礼

再度ありがとうございます。
確認してみました。確かに関数式の戻り値としての#DIV/0!と自分でタイプした#DIV/0!の両方をIserror()にかけたところ、どちらもTRUEでした。どちらでも使えるようで一安心です。

また、""が文字列扱いとは知りませんでした。。。。って書いてて気づきましたが、そもそもダブルクォーテーション使った時点で文字列ですよね。

お礼日時:2015/02/07 00:50

No.1です。



>エラーを除いて残った数値の中で最終行を知りたいのです

というコトはNo.2さんやNo.3さんの回答通り
データの最終行から地道にループさせ、エラーセルが見つかれば
それより前のセルでエラーもしくは空白以外のセルが見つかるまでループさせるのが
一番手っ取り早いと思います。m(_ _)m
    • good
    • 0
この回答へのお礼

再度ありがとうございます。

お礼日時:2015/02/07 00:28

こんにちは。



>#N/A等はコピペ処理により文字列?
これは、テキスト値では、文字列ですから、コピー&ペーストしたら、定数のエラーとして反応してしまいます。

SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなるはずです。確かにご質問の通りのエラーが入っている条件なら問題ないはずです。しかし、いろんな場合を考えて、昨日から試してみたけれども、3つの方法を思いつきましたが、その内、一番確実なものを出しておきます。

#2様のおっしゃった通り、
>下からループして探すのが妥当になるのではないかと思いますが

昨日、実験的に考えたのは、以下の通りです。
---



え  ←ここの行番号が知りたい
   ←が空白値の場合
#N/A

これは、定数・数式に関わりなく、エラー値を除く、データの最後【空白行の手前】ということになりますから、5ではなく、「4」という答えになります。

それと、#2様がご提示されたものに、手を加えた
エラー
エラー
エラー

エラー
エラー

この場合は、4にデータがあっても、それ以下にエラー値があるので、意味をなしません。
'//
Sub TestSample4()
 Dim rw As Long, i As Long
 Dim ErrRow As Long
 Dim r As Range
 Dim LastRow As Long
 rw = Cells(Rows.Count, 1).End(xlUp).Row
 ErrRow = rw
 If rw = 1 Then MsgBox "Blank Data": Exit Sub
 Set r = Range("A1", Cells(rw, 1))
 For i = 1 To rw
  If Not (IsError(Cells(i, 1)) Or IsEmpty(Cells(i, 1))) Then 'エラーと空白を除く
   LastRow = Cells(i, 1).Row
  ElseIf IsError(Cells(i, 1)) Then
   If LastRow = 0 Then ErrRow = Cells(i, 1).Row 'エラーのセルを調べる
  End If
 Next i
 If ErrRow < LastRow Then
   MsgBox "This Row is not available!", 48
 Else
   MsgBox "LastRow: " & LastRow, 64
 End If
End Sub
'//
    • good
    • 0
この回答へのお礼

ありがとうございます。
上から順に調べる方法ですね。Is関連は関数式のみ使っており、VBAで使うことが無かったので、試してみます。

お礼日時:2015/02/07 00:26

こんにちは。



 指定した列範囲内で、
 エラー値以外の定数値を持つセル範囲の
 一番したにある行
ということに対して、まっすぐに取り組む
(最下行がエラー値以外の定数である場合に対応させる)
なら、以下のように書くことが出来ます。

Sub Re8914431()
Dim r As Range
  Set r = Range("A:A").SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues Or xlNumbers Or xlLogical)
  If r Is Nothing Then
    MsgBox "エラー値以外の定数値は存在しません"
  Else
    With r.Areas(r.Areas.Count)
      MsgBox .Cells(.Cells.Count).Row
    End With
  End If
End Sub

 A列の
  定数値 且 (文字列値 または 数値 または 論理値)
  であるセル範囲を取得。
 この段階で、飛び飛びの複数セル範囲が取得されていとして、
  r.Areas(r.Areas.Count)で、最後のブロック(矩形範囲)を取得し
   .Cells(.Cells.Count)でブロック内の最後のセルを取得
といった流れになります。

> ちなみに#N/A等はコピペ処理により文字列?になっている事もあります。
とりあえず、
 #VALUE!
 #REF!
 #DIV/0!
 #N/A
を試しに色んな手順でコピペしてみましたが、'文字列?'になる、というのは、
どういうことなのか(どういう手順で再現できるのか)判りませんでした。
何であれ、エラー値に加え'文字列?'をも除く必要がある、ということでしたらば、
端から普通に、下からループして探すのが妥当になるのではないかと思いますが、、、。
'文字列?'の由来が判れば、また違うトライも可能なのかも知れません?

取り敢えず、以上です。
    • good
    • 0
この回答へのお礼

ありがとうございます。
SpecialCellsを使って特定種類のセルを選べるようですね。是非使ってみます。コピペで文字列になるというのは、例えば数式で=1/0などと書けば #DIV/0!と表示されるので、これをコピーし他セルで値のみペーストを選んで表示されるものです。あるいはvlookupで該当が無い場合の#N/Aのコピペとか。

無理な場合は、数式側で=IF(Iserror(vlookup), vlookup(), "")等で対処するしかありませんが、他人も使うファイルなので出来れば手を加えたくありません。

お礼日時:2015/02/06 20:32

こんばんは!



>上例では「え」のある行を取得したいのです

エラーのすぐ上のセルを選択するのではなく、エラーのすぐ上の行番号を取得したい!
という解釈ですの一例です。
エラーとは数式によるエラーとします。

Sub Sample1()
Dim c As Range
Set c = Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors)
If Not c Is Nothing Then
MsgBox c(1).Row - 1
End If
End Sub

※ エラーのすぐ上のセルを選択したい場合は
>MsgBox c(1).Row - 1

>c(1).Offset(-1).Select
にしてください。m(_ _)m
    • good
    • 0
この回答へのお礼

ありがとうございます。
エラーの上と書いてしまし誤解を招きました。すみません。エラーを除いて残った数値の中で最終行を知りたいのです。

お礼日時:2015/02/06 20:19

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelで、条件と一致する最後のセルを検索したい

Excelで、条件と一致する最後のセルを検索したいのですが、どの関数をどのように使えばいいかわかりません。
どなたかお力をお貸し下さい。

問)
日付  社名  品名  個数
1/1   A    あ    1
1/1   C    い    2
1/2   B    う     3
1/2   A    え    4
1/3   C    お    5
1/3   A    あ    3

答)
A社の最終購入日は  1/3 あ 3
B社の最終購入日は  1/2 う  3
C社の最終購入日は  1/3 お 5

となるようお願いします。

Aベストアンサー

エクセルには「最後の該当」を検索する関数はありません。
無駄に複雑な数式を駆使すればもちろん「やればできます」が、添付図のようにちょっと工夫するだけで、普段使いの数式だけでやっつけるのがお勧めです。

A2:
=IF(OR(C2="",COUNTIF($C$2:C2,C2)<COUNTIF(C:C,C2)),"",C2)
以下コピー

答えは簡単なVLOOKUP関数を使うだけです。
H2:
=IF(COUNTIF($A:$A,$G2),VLOOKUP($G2,$A:$E,2,FALSE),"")




まぁ一応ご参考に作業列を使わない方法:
H2:
=IF(COUNTIF($C:$C,$G2),INDEX(B:B,MAX(IF($C$2:$C$999=$G2,ROW($C$2:$C$999)))))
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する

説明は省略しますので、ブラックボックスで使ってください。

Q[初心者です]VBAで指定列からAを検索し、発見したら隣のセルに値0を入れるマクロ。

VBAで指定列からAを検索し、発見したら隣のセルに0を入れるマクロを組みたいのですが、組み方がVBA初心者の為わかりません。
(例)
L列に、A、B、C、D、E、Fとランダムに文字が入っていて、
文字Aを検索し、発見したら隣のI列に値0を入れるというマクロです。

Sub Search()
Dim A As String
Set A = Worksheets("Sheet1").Cells.Find("A")
If A Is Nothing Then
ActiveCell.Offset(0, 1).Value = 0

End If
End Sub
と過去の質問で考えてみたのですが、Aがあった時、、、、
とコードが書けないです。
大変困っているので、ご教授頂けないでしょうか?
出来れば、そのままマクロに出来るコードを教えて頂けないでしょうか?
宜しくお願い致します。

Aベストアンサー

こんばんは。

#3さんのおっしゃっていることも、もっともなのですが、気になる点がありましたので、自分のことを踏まえて、書かせていただきます。

いずれ、また、同じようなケースが出会うと思います。こんな原則を考えてみたらどうでしょうか?それは、私も自身も同じなのですが、ワークシートのコマンドで行われるものは、記録マクロから作ってみるということです。他にも、「統合」とか、「置換」とか「オートフィルタ」「フィルタオプション」とかは、みんなパターンが決まっています。
その中の代表格が、この「Find」 です。

>Set A = Worksheets("Sheet1").Cells.Find("A")

>過去の質問で考えてみたのです

どうも、Find メソッドは、あるレベル以下の人は、省略する傾向があるようです。何が大事で、何が大事でないかというのは、やってみなければ分かりませんが、検索語だけを入れる書き方は、実務では、あまりしないほうがよいと思います。

だいたい、以下のTestFind2 ぐらいまでに、省略は、とどめたほうがよいです。

それは、Find は、必ずしも自分が思っているデフォルトとは違うことがあるので、「明示的(意図的に)」にオプションは入れたほうがよいです。
例えば、大文字小文字の違いを付けるなら、MatchCase:=True, 数式まで探すなら、LookIn:=xlFormulas

なお、Find メソッドは、5年経っても、たぶん完全に覚えられません。面倒なコードのひとつです。ですが、これはパターンが決まっているので、ひとつパターンが決まったら、それに当てはめればよいだけです。

#3さんで示されているMougのサンプルコードと似てはいるのですが、Mougのサンプルコードでは、Verionによって、失敗することがあります。

'--------------------------------------
'記録マクロをそのまま使う方法
Sub TestFind1()
Dim c As Range
 Set c = Columns("L:L").Find(What:="A", _
           After:=ActiveCell, _
           LookIn:=xlValues, _
           LookAt:=xlPart, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
           MatchCase:=False, _
           MatchByte:=False, _
           SearchFormat:=False)
 c.Offset(0, 1).Value = 0
End Sub
'--------------------------------------
'TestFind1 をアレンジしてみる
Sub TestFind2()
Dim c As Range
'検索語
Const MYTXT As String = "A"
 Set c = ActiveSheet.Columns("L:L").Find(What:=MYTXT, _
           LookIn:=xlValues, _
           LookAt:=xlPart, _
           MatchCase:=False)
 If Not c Is Nothing Then
    c.Offset(0, 1).Value = 0
 End If
End Sub

'---------------------------------------
'複数ある場合(パターンを使った方法)
'---------------------------------------
Sub TestFind3()
  Dim c As Range
  Dim FirstAdd As String
  Const MYTXT As String = "A"
  Set c = ActiveSheet.Columns("L:L").Find( _
    What:=MYTXT, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    MatchCase:=False)
  
  If Not c Is Nothing Then
    FirstAdd = c.Address
    Do
      c.Offset(, 1).Value = 0
      Set c = ActiveSheet.Columns("L:L").FindNext(c)
      If c.Address = FirstAdd Then Exit Sub
    Loop Until c Is Nothing
  End If
End Sub

こんばんは。

#3さんのおっしゃっていることも、もっともなのですが、気になる点がありましたので、自分のことを踏まえて、書かせていただきます。

いずれ、また、同じようなケースが出会うと思います。こんな原則を考えてみたらどうでしょうか?それは、私も自身も同じなのですが、ワークシートのコマンドで行われるものは、記録マクロから作ってみるということです。他にも、「統合」とか、「置換」とか「オートフィルタ」「フィルタオプション」とかは、みんなパターンが決まっています。
その中の代表...続きを読む

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

QExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。

以下のようなプログラムをVBAで作成したいと考えています。

A1のセルに値があれば、その値をB1に返す。
次にA2のセルに値があれば、その値をB2に返す。
A行に値がある一番下のセルまで同じようなことをさせたいと考えています。

VBAは初心者です。
どなかた宜しくお願い致します。

Aベストアンサー

#2さんと似たものですが・・・・参考にしてください。

Sub test001()
Dim i As Long
i = 1
Do While Cells(i, 1) <> ""
Cells(i, 2) = Cells(i, 1)
i = i + 1
Loop
End Sub

QVBA 指定の列の最終行を取得

 Excel VBAについて質問します。すいませんが宜しくお願いします。

画像の様に、A列の最終行とF列の最終行が異なった時に、
A列とF列の位置を、メッセージボックスで表示したいのですが、

例えば、画像で説明しますと、"最終列はA11とF9です"
こんな感じで表示されると良いですが、
お手数ですが教えて頂けますか?

Aベストアンサー

>画像の様に、A列の最終行とF列の最終行が異なった時に
一応、異なった時にメッセージを出力します。

質問を読むと、A列とF列は例のように思えたので、A列を基準に他の列の最終行が異なれば異なった列をすべて表示します。A列からF列まですべて同じ行数ならなにも表示しません。(最後のコメント文を有効にすれば出力します)

ご参考に。


Sub Test()
  Const maxColumn = 6 '列数 A~Fの6列
  
  Dim LastRow(maxColumn) As Long '各列の最終行
  Dim c As Integer       '列カウンタ
  Dim msg As String       'メッセージ
  
  '各列の最終行を調べる
  For c = 1 To maxColumn
    LastRow(c) = Cells(Rows.Count, c).End(xlUp).Row
  Next

  'A列との違いを調べる
  For c = 2 To maxColumn
    If LastRow(1) <> LastRow(c) Then
      msg = msg & "と" & Cells(LastRow(c), c).Address(0, 0)
    End If
  Next
  
  'メッセージの出力
  If Len(msg) > 0 Then
    msg = "最終行は" & Cells(LastRow(1), 1).Address(0, 0) & msg & "です"
    MsgBox msg
  Else
    'MsgBox "最終行は全て " & LastRow(1) & "行目です"
  End If
End Sub

>画像の様に、A列の最終行とF列の最終行が異なった時に
一応、異なった時にメッセージを出力します。

質問を読むと、A列とF列は例のように思えたので、A列を基準に他の列の最終行が異なれば異なった列をすべて表示します。A列からF列まですべて同じ行数ならなにも表示しません。(最後のコメント文を有効にすれば出力します)

ご参考に。


Sub Test()
  Const maxColumn = 6 '列数 A~Fの6列
  
  Dim LastRow(maxColumn) As Long '各列の最終行
  Dim c As Integer       '列カウンタ
  Di...続きを読む

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Q指定した文字があった場合、その行を削除するマクロが欲しいです

指定した文字があった場合、その行を削除するマクロが欲しいです
Sheet1(Sheet1以外は対象外)のB列に
XYZ
という文字があった場合、その行をすべて削除する
というマクロはどのように作ればいいでしょうか?
お時間ある方アドバイスいただければ幸いです。

Aベストアンサー

手抜きですがこんな感じでどうでしょう。
削除する行が多いなら画面更新を停止した方が良いでしょう。

Sub Sample()
 Sheets("Sheet1").Select
 Do While (True)
  Columns("B:B").Select
  Set mySelect = Selection.Find(What:="XYZ")
  If mySelect Is Nothing Then Exit Do
  Rows(mySelect.Row).Select
  Selection.Delete Shift:=xlUp
 Loop
End Sub

Q数式による空白を無視して最終行を取得するマクロ

A1~D8まで数式が入っていて、if関数によって条件に合わない場合は、空白にしています。
条件に合わない場合は行すべてが空白になり、1つのセルだけが空白になることはありません。
(画像参考)

A1~D3までは数式によって数値や文字列が表示されています(A列・B列は文字列です)
A4~D8まではif関数による空白(””)の状態です。

この状態で、最終行の次のセルをアクティブにするマクロ
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
を実行すると、A9が選択されます。

これを、A4を選択するマクロを教えていただけないでしょうか。

説明が分かり難かったらすみません。

よろしくお願いします。

Aベストアンサー

No.1です。
たびたびごめんなさい。

もう少し短くできました。

Sub Sample2()
Dim i As Long
On Error Resume Next '//←念のため
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(i, "A") <> "" Then Exit For
Next i
Cells(i, "A").Offset(1).Select
End Sub

にしてみてください。m(_ _)m


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング