Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
別シートの最終行を取得
投稿日時: 19/06/24 14:30:49
投稿者: vaioyuki

いつもお世話になっております。
またもや相談させてください。
 
CSVデータを読み込みシート貼り付けて、そのデータをもとに別シートで計算をしています。
データの量は月によって変わります。
あらかじめ数を予想してBシートに計算式を元から入れていますが、
数が多くなるとデータの容量も大きくなり困っています。
 
Range("A1:A" & Worksheets("DWH").Range("A" & Rows.Count).End(xlUp).Row).Formula = "=Match(Substitute($g3," - ",""),DWH!$E:$E,0)"
 
このようにしてCSVファイルを貼り付けてから自動で計算式を入力しようとしたら、実行時エラー13となりました。
別シートの最終行の取得方法が違っているのでしょうか?
 
よろしくお願いします。
 

回答
投稿日時: 19/06/24 15:05:38
投稿者: sk

引用:
実行時エラー13となりました。

引用:
Range("A1:A" & Worksheets("DWH").Range("A" & Rows.Count).End(xlUp).Row).Formula = "=Match(Substitute($g3," - ",""),DWH!$E:$E,0)"

文字列と文字列で引き算(数値データの減算)しようとしているため。
 
引用:
CSVデータを読み込みシート貼り付けて、そのデータをもとに別シートで計算をしています。

引用:
あらかじめ数を予想してBシートに計算式を元から入れています

引用:
Range("A1:A" & Worksheets("DWH").Range("A" & Rows.Count).End(xlUp).Row).Formula = "=Match(Substitute($g3," - ",""),DWH!$E:$E,0)"

[DWH]という名前のワークシートが「読み込みシート」であるとして、
数式を設定しようしている[Bシート]の A列 と G列の行位置が
ずれているのには何か意味があるのでしょうか。

投稿日時: 19/06/24 15:20:59
投稿者: vaioyuki

skさま
 
いつもありがとうございます。
 
 
=MATCH(SUBSTITUTE($G3,"-",""),DWH!$E:$E,0)
 
上記の式はG3に入力されている電話番号の"-"を抜いたものがDWHシートにあるため"-"を空白にしているためなのですが引き算として認識されているのでしょうか?
 

引用:
[DWH]という名前のワークシートが「読み込みシート」であるとして、
数式を設定しようしている[Bシート]の A列 と G列の行位置が
ずれているのには何か意味があるのでしょうか。

 
DWHシートは1行目にタイトル、2行目からデータが入力されていて、
Bシート(計算式が入っているシート)は上2行にタイトルと項目があり、3行目から式が入力されています。
最後にこのBシートが値貼り付けされて別ブックに保存されます。

回答
投稿日時: 19/06/24 15:35:10
投稿者: sk

引用:
=MATCH(SUBSTITUTE($G3,"-",""),DWH!$E:$E,0)

引用:
上記の式はG3に入力されている電話番号の"-"を抜いたものがDWHシートにあるため
"-"を空白にしているためなのですが引き算として認識されているのでしょうか?

VBA の文字列リテラルの中で「文字としてのダブルクォーテション」を
正しく記述出来ていないので、結果としてそういう式になっただけでしょう。
 
引用:
DWHシートは1行目にタイトル、2行目からデータが入力されていて、
Bシート(計算式が入っているシート)は上2行にタイトルと項目があり、
3行目から式が入力されています。

ならば、セル範囲 A1:A2 は数式の設定対象から外すべきです。
 
--------------------------------------------------------------------
 
Dim lngLastDataRow As Long
Dim lngFirstFormuraRow As Long
Dim lngLastFormuraRow As Long
Dim strFormula As String
 
With Worksheets("DWH")
    lngLastDataRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
lngFirstFormuraRow = 3
lngLastFormuraRow = lngLastDataRow + lngFirstFormuraRow - 1
strFormula = "=Match(Substitute($g" & lngFirstFormuraRow & ",""-"",""""),DWH!$E:$E,0)"
 
With Worksheets("Bシート")
    .Range("A" & lngFirstFormuraRow & ":A" & lngLastFormuraRow).Formula = strFormula
End With
 
--------------------------------------------------------------------

回答
投稿日時: 19/06/24 15:40:33
投稿者: Suzu

vaioyuki さんの引用:
CSVデータを読み込みシート貼り付けて、そのデータをもとに別シートで計算をしています。
データの量は月によって変わります。
あらかじめ数を予想してBシートに計算式を元から入れていますが、
数が多くなるとデータの容量も大きくなり困っています。

 
「容量が大きくなり困る」
これが単純にファイル容量が増えて困る と言う事であれば
VBAで計算式を与えていますが、計算を行わせた後、
範囲をコピーし、値貼り付けをおこなってはいかかですか?

回答
投稿日時: 19/06/24 16:03:09
投稿者: sk

補足:

vaioyuki さんの引用:
データの量は月によって変わります。

vaioyuki さんの引用:
別シートの最終行の取得方法

[DWH](元は CSV ファイル)の最終行ではなく
[Bシート]の G 列の最終行を取得したい場合は、
 
引用:
With Worksheets("DWH")
    lngLastDataRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

With Worksheets("Bシート")
    lngLastDataRow = .Range("G" & .Rows.Count).End(xlUp).Row
End With
 
引用:
lngLastFormuraRow = lngLastDataRow + lngFirstFormuraRow - 1

lngLastFormuraRow = lngLastDataRow
 
---------------------------------------------------------------
 
のように書き換えて下さい。

投稿日時: 19/06/24 17:30:11
投稿者: vaioyuki

skさま
 

引用:
VBA の文字列リテラルの中で「文字としてのダブルクォーテション」を
正しく記述出来ていないので、結果としてそういう式になっただけでしょう。

 
なるほど!!
そうですね!!
そうでした!!
""の使い分け方が間違ってますね。(^^;)
 
A1:A2も外すべき、これもそうでした。
うっかり過ぎました。。。
 
 
Suzuさま
 
引用:
VBAで計算式を与えていますが、計算を行わせた後、
範囲をコピーし、値貼り付けをおこなってはいかかですか?

 
頭が悪くてすいません。
これの意味がいまいちわからないのですが。。。
今現在、A3:AI503列(500データとして)列ごとに式が入力されています。
前任者が作成したものなので何ともいえないのですが、
DWHシートともうひとつパターンシートがあり、この2つのシートから読み取った計算式が入力されています。
これを最終的に値コピーで新ブックに貼り付けているようです。
 
With ActiveSheet.Cells(1, 1).Resize(ENM_ROW.goukei, ENM_COL.saigo)
  .Calculate '再計算
  .Copy 'コピー
  .PasteSpecial Paste:=xlPasteValues '値の貼り付け
  Application.CutCopyMode = False 'コピーモード解除
End With
With ActiveSheet
  For Lng_for = ENM_ROW.kaishi To ENM_ROW.goukei - 1 'データの開始行から合計行の手前まで繰り返し
    If .Cells(Lng_for, ENM_COL.denwa).Value = "" Then '電話番号がなければ
      .Range(.Rows(Lng_for), Rows(ENM_ROW.goukei - 1)).Delete '電話番号のない行を削除
      Exit For '抜ける
    End If
  Next Lng_for
  .Columns(ENM_COL.sakujo).Delete '削除列を削除
End With
 
このブックは500件のデータなのですが、
他ブックは2500件を超えるものあり、容量も9MBを超えて開くのも処理をするにも非常に重くなっていて困っています。
なのでなんとかVBAで軽くならないかなと思っています。

投稿日時: 19/06/25 10:55:45
投稿者: vaioyuki

閉じます。