エクセルでセル参照後数値で張り付けて保存

質問

いつも大変お世話になっております。
以前より画像の添付がうまくいかないので「お絵かき添付」にしましたがうまく表示されるか自信が無いので、出来るだけ文字で表現（質問）させていただきます。

試験結果を入力するシートがあり行方向に色んな特性データが入力されていきます。
B列にLOT No、E列とF列には別シートの毎回同じセルを使用する計算表の計算結果のセルの値を引っ張てきたいのです。
B列のLOT Noと計算シートに入力されたLOT Noが同じなら入力表のE列の最下行（最新データのセル）に計算結果のセルの値を参照させたいのです。
そこで入力表のE列にLOTが同じなら「=計算表のC35」（違えば空白）として下にコピーを取って、保存前にマクロでシート全体をコピし数値で貼り付けたら、と思ったのですがシート全体を数値化するとE列の参照の式も空白に変わってしまう事に気付きました。
また、試しにやってみたら非常に時間がかかって使えないことが判明しました。
E列、F列に参照で入力された値は保存前に数値化しないと次のLOTの計算結果の時には空白になってしまいます。

当方の実力ではここまで。
分かりにくいかもしれませんが何とかよろしくお願い致します。
別シートのいくつかの計算結果の平均や最大値等を入力表に転記していて失敗したのでその再発防止対策です。

回答No.4

HohoPapa · ベストアンサー

エラーが起きないのであれば、

.Sheets("特性表").Range("C38").Value
.Sheets("特性表").Range("H38").Value
これらの値が空欄（Null）、もしくは、

MyRange.Offset(1, 0)
MyRange.Offset(1, 1)が期待と異なる場所を指していることになります。

Sheets("入力表").の5列目の最下行（E1048576）をクリックし
Ctrlキー+上矢印キーを押してみてください。
止まったところがデータの既に埋まった最下行かどうかがポイントです。

これが期待したセルではない
つまり、E列のどこか途中に、
何かが埋まっているセルがあるのではないかと思います。

お礼

本当にいつもながら感心と感謝です。

連休中にVBA講座（初級：3.5万円）を受講しましたが「マクロとは？」から始まって実践編に入らないまま終わりそうだったので、このまま帰れない、と質問したら「初級Part2か中級クラスを受講してください」と言われてむなしく帰宅。

＜＜改善提案賞＞＞を送りたいのですが。。。。

補足

ご明察！！！

表の最終行から「END」「↓」で450行以下数行に姿を現しました。
最初（かなり前）にTESTで表を作ったときに式を適当に500行程度下に引っ張った名残でした。
このTEST表をそのまま使用し始めたようです。(当方が渡した）

とにかく、便利になりました。
これまでは計算表は別ファイルの計算専用シートで3~12個の計算結果をメモして電卓で平均を出して入力表に打ち込んでいた（知りませんでした）ので手間とミス防止の両面で非常に信頼性と作業効率が向上しました。

回答No.3

HohoPapa

計算表シートのJ15の値を入力表シートのE列最下行に複写し、
更に
計算表シートのJ20の値を入力表シートのF列最下行に複写する。
ということのようですので、次のようなコードになります。
なお、入力表シートの1行目に複写されることはない前提です。

余談かもしれませんが、
入力表シートのE列、F列に単に追記していくだけなので
このマクロを誤って複数回実行してしまうと
その回数だけ追記してしまいますよ。

私だったら、
計算表シートのどこかに埋まっている（かもしれない）
LOT番号と入力表シートのB列？の最下行が
一致しているかどうかをチェックします。

Option Explicit

Sub Sample3()

　Dim MyRange As Range

　With ThisWorkbook
　　Set MyRange = _
　　　.Sheets("入力表").Cells(Rows.Count, 5).End(xlUp)
　　MyRange.Offset(1, 0).Value = .Sheets("計算表").Range("J15").Value
　　MyRange.Offset(1, 1).Value = .Sheets("計算表").Range("J20").Value
　End With

End Sub

お礼

いつもお世話になっております。
さて朝から喜び勇んで試しているのですが、何故かうまくいきません。

ただし、エラー表示もなく、何も起こらないのですが。
色々試して（と言ってもシート名を変える程度しか思いつかないのですが）
何も起こらないでので昨夜試した
Application.Goto Reference:=Worksheets("入力表").Columns("A"), Scroll:=True
を追加してみたら入力表のA列に移動するのでマクロは動いているようです。
他のシートのE、F列にも何も転記されていません。

ダミーファイルではうまくいったのですが、何か確認すべきところご教授お願いします。
いつものことですがお手数をお掛けしてしまう事になり申し訳ありません。

Sub 計算表()

Dim MyRange As Range

With ThisWorkbook
Set MyRange = _
.Sheets("入力表").Cells(Rows.Count, 5).End(xlUp)
MyRange.Offset(1, 0).Value = .Sheets("特性表").Range("C38").Value
MyRange.Offset(1, 1).Value = .Sheets("特性表").Range("H38").Value

Application.Goto Reference:=Worksheets("入力表").Columns("A"), Scroll:=True

End With

End Sub

補足

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

今日はバタバタしていて会社では試せませんでしたので今自宅でダミーシートで試してみました。

バッチリ！！です。
また実行の回数だけ同じ結果が追加されることも確認しました。
そこで、計算シートに実行ボタンを作って、マクロ実行後入力シートに移動するように1行追加したらバッチリです。
J列以降の入力項目もあるので続けて作業できるのでご懸念はなくなりました。
また、元々A列より順に横に埋めていくのでE、F列だけが出っ張ると分かるので間違うことはありません。

明日実ファイルで確認後BAで締め切らせていただきます。

回答No.2

HohoPapa

akira0723さんが直面している現況と課題が
ポストされた文章から読み取ることができません。

>B列にLOT No、
>E列とF列には別シートの毎回同じセルを使用する計算表の
>計算結果のセルの値を引っ張てきたいのです。
この記述と

>E列、F列に参照で入力された値は
>保存前に数値化しないと
>次のLOTの計算結果の時には空白になってしまいます。
この記述から、以下をイメージします。

『計算表』シートは、
『LOT No』ごとの行（あるいは列）があるのではなく
何やら計算したい都度、
このシートに『LOT No』と必要な情報を埋めると、
X1セルとX2セルに特性Aと特性Bが計算される。
つまりワーキングシートだ。

他方、akira0723さんがポストした画像のような
『LOT No』ごとの特性A,Bの一覧がある。
これを『特性一覧』シートと仮に呼びます。

これらの予想が正しいとするとやりたいことは

『計算表』シートに必要事項を埋めボタンを押すと
『計算表』シートの『LOT No』で
『特性一覧』シートを検索し、
X1、X2セルの値を該当行のE列、F列に複写する。
該当行が無い場合は、
『特性一覧』シートの最終行に
『LOT No』、X1、X2セルの値を追記する。

という動作ではないでしょうか。

ならば、
『計算表』シートの『LOT No』、特性Aと特性Bのセル位置
を明らかにしてください。

これらの予想が違っているようなら
現況と期待をもう一度説明してみてください。

※画像を添付して質問するテクニックはマスターしてほしいところです。
さらに、現況を説明する時には、現況だけを、
手作業で行っていることをシート名やセル番地を使い、
入力しているとか、目でxxを探しているとか、どこそこにコピペしているとか
具体的に書き込んだほうが伝わりやすいと思います。

補足

お世話になります。
そうですね、確かに分かりにくい。
やりたいことはご推察程難しいことではないと思うのですが。
ロットが一致したらとか書いてしまったのが間違いでした。
単に入力表の最下行E列、F列のことでした。

Sheet１が入力表でSheet２が計算表です。
Sheet１には毎日最下行に新しいLOTのデータが入っていきます。
1日に１ロット（1行）しか入力することはありません。
その中のある特性（E列）にはSheet２で計算した値を入れたいのです。

Sheet２（計算表）では3回の測定データの計算結果がG15、H15、I15に出力されます。
その3回の平均値（J15）をSheet１の入力表の最下行のE列に持ってきたいのです。
更に特性Bの計算結果はSheet２のG20、H20、I20に、平均値がJ20に出力され、これはSheet１（入力表）のF列の最下行にもっていきたいのです。
つまり、添付図のLOT GHI789の特性Aの789、特性Bの321という値はSheet２（計算表）のJ15とJ20の参照です。(ここまではLOTの一致やVLOOKUPでもできるのですが、このままでは翌日の入力時に問題が出ます。

次のロットもSheet２（計算表）の同じ表（セル）を使用するので上記のJ15、J20に次のロットの結果が出ます。
これを前回の行の下（最下行）に持ってくる、これの繰り返し作業です。

この作業を繰り返すためには前回のE列の値を数値化しておかないとSheet２から当該ロットの数値がなくなると困ったことになります。

よって、１つのロットの入力作業が終わったらファイルを上書き保存する時にE列(J15の参照）、F列（J20の参照）を数値でコピペすればよいのですが、最下行だけをコピペする方法が分かりませんのでGIVE UPとなった次第です。

Sheet２の特定のセルの値をSheet１のE列の最下行に入れたい。
この方が分かりやすかったですか。

回答No.1

kkkkkm

> シート全体を数値化するとE列の参照の式も空白に変わってしまう

なぜ？でしょう。参照の結果が数値であれば、値貼り付けで数値になると思いますが…。

> 試しにやってみたら非常に時間がかかって使えないことが

どれほどのデータがあってどれほど時間がかかったのでしょう。

> B列のLOT Noと計算シートに入力されたLOT Noが同じなら入力表のE列の最下行（最新データのセル）に計算結果のセルの値を参照させたいのです。

VLOOKUP関数を利用して参照させたのですか？

お礼

早々のご回答ありがとうございました。
質問の内容が分かりにくくて申し訳ありませんでした。

＞参照の結果が数値であれば、値貼り付けで数値になると思いますが…。

問題にしたのは、次の行の例えばVLOOKUPなりIF関数でロットを照合して産所したセルは良いのですが、その下の行が空白で張り付けられてしまいます。
あまりに初歩的な問題を書いてしまったために混乱させてしまいました。

＃No2さんのご回答で無事解決しました。
お手数をおかけしました。