Excel (VBA)

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

 
(Windows 10全般 : Excel 2016)
セル範囲を変数にして関数に使いたい
投稿日時: 19/07/02 10:55:17
投稿者: vaioyuki

またまた知恵をお貸しください。
 
https://www.moug.net/faq/viewtopic.php?t=78368
 
こちらで計算式をVBAから貼り付ける?作業を引き続きしています。
関数の中で
 
=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:$F$3003,$F3,$K$3:$K$3003),"""")
 
があり、赤字部分を変数にして出来ないかなと考えています。
そこで、
 

Sub keisan()
Dim lngLastDataRow As Long
Dim lngFirstFormuraRow As Long
Dim lngLastFormuraRow As Long
Dim Goukei As Long
Dim OrderLastF As Long
Dim OrderLastK As Long

With ThisWorkbook.Worksheets("パターンC") 'パターンCシートA列の最終行の次の空白を取得
    lngLastDataRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
lngFirstFormuraRow = 3 '3列目から始める
lngLastFormuraRow = lngLastDataRow + lngFirstFormuraRow - 2 '最終行から3行を足す
Goukei = lngLastDataRow + lngFirstFormuraRow - 1
OrderLastF = Range("F3").End(xlUp).Select
OrderLastK = Range("K3").End(xlUp).Select

With ThisWorkbook.Worksheets("集計") 'パターンCシートの最終行まで集計シートに計算式を自動入力

    .Range("A" & lngFirstFormuraRow & ":A" & lngLastFormuraRow).Formula = "=Match(Substitute($h" & lngFirstFormuraRow & ",""-"",""""),DWH!$E:$E,0)"

〜〜〜〜〜〜〜〜〜〜 省 略 〜〜〜〜〜〜〜〜〜〜

    .Range("AD" & lngFirstFormuraRow & ":AD" & lngLastFormuraRow).Formula = "=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:" & OrderLastF & ",$F3,$K$3:" & OrderLastK & "),"""")" '計上オーダーごとの請求額合計"

 
色々模索していますが、
実行時エラー1004
アプリケーション定義またはオブジェクト定義のエラーです。
が出ます。
 
おそらく、
 
OrderLastF = Range("F3").End(xlUp).Select
OrderLastK = Range("K3").End(xlUp).Select

 
このセル範囲が取れないのかなとは思ってはいるのですが、
色々調べてもわかりません。
『引数としてRange型を取得』する方法なのかなと思い調べてみたのですが出来ませんでした。
本来ならば、
 
=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:" & OrderLastF & ",$F3,$K$3:" & OrderLastK & "),"""")
 
の赤字部分がない形にしたいのですがセル範囲を指定する方法がわかりません。
よろしくおねがいします。

回答
投稿日時: 19/07/02 11:58:17
投稿者: 半平太

>OrderLastF = Range("F3").End(xlUp).Select
>OrderLastK = Range("K3").End(xlUp).Select
  
1.左辺に「何」を入れようとしているんですか?
  
  意図が読めないのですが・・
 
  テストしてみたら、右辺からはTRUEが返ってましたが。
 
2.ちなみに、二つも必要なんですか?(F列とK列は別のものになり得るのですか?)

投稿日時: 19/07/02 13:04:32
投稿者: vaioyuki

ありがとうございます。
 
=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:$F$3003,$F3,$K$3:$K$3003),"""")
 
上記式の赤い部分を入れたいと思っています。
こちらは余裕を見越しても3003行なのですが、実際にはセルに入力されているところまでで計算を終えたいです。
F行とK行が必要なのはSUMIF関数で検索したいところと計算したいところの行です。

回答
投稿日時: 19/07/02 14:20:47
投稿者: 半平太

聞いた通りに返答して頂くと有難いのですが・・
 
仕方ないので推測で書きます。
 
OrderLastF = Range("F3004").End(xlUp).Row
OrderLastK = OrderLastF

投稿日時: 19/07/02 15:48:32
投稿者: vaioyuki

すいません。
聞かれた通りに答えたつもりだったのですが、
F3004までではなく、データの最終行を求めたいのです。
現状はデータが入らないセルにも計算式が入っていて容量的にもとても大きいものになっています。
F3からCTRL+Shift+↓をしたようにしたいです。

回答
投稿日時: 19/07/02 16:44:52
投稿者: sk

引用:
Dim OrderLastF As Long
Dim OrderLastK As Long

Dim rngFirstCell As Excel.Range
Dim rngLastCell As Excel.Range
Dim rngArea As Excel.Range
Dim strArg1 As String
Dim strArg2 As String
Dim strArg3 As String
Dim strFormula As String
 
引用:
OrderLastF = Range("F3").End(xlUp).Select
OrderLastK = Range("K3").End(xlUp).Select

引用:
.Range("AD" & lngFirstFormuraRow & ":AD" & lngLastFormuraRow).Formula = "=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:" & OrderLastF & ",$F3,$K$3:" & OrderLastK & "),"""")"

With Worksheets("集計")
     
    Set rngFirstCell = .Cells(lngFirstFormuraRow, "F")
    Set rngLastCell = .Cells(.Rows.Count, "F").End(xlUp)
    Set rngArea = .Range(rngFirstCell, rngLastCell)
     
    strArg1 = rngArea.Address(True, True, xlA1)
    strArg2 = rngFirstCell.Address(False, True, xlA1)
     
    Set rngArea = Nothing
    Set rngLastCell = Nothing
    Set rngFirstCell = Nothing
     
    Set rngFirstCell = .Cells(lngFirstFormuraRow, "K")
    Set rngLastCell = .Cells(.Rows.Count, "K").End(xlUp)
    Set rngArea = .Range(rngFirstCell, rngLastCell)
     
    strArg3 = rngArea.Address(True, True, xlA1)
     
    Set rngArea = Nothing
    Set rngLastCell = Nothing
    Set rngFirstCell = Nothing
     
    strFormula = "=IF(COUNTIF($F$2:$F2," & strArg2 & ")=0,SUMIF(" & strArg1 & "," & strArg2 & "," & strArg3 & "),"""")"
    Debug.Print strFormula
     
    Set rngFirstCell = .Cells(lngFirstFormuraRow, "AD")
    Set rngLastCell = .Cells(lngLastFormuraRow, "AD")
    Set rngArea = .Range(rngFirstCell, rngLastCell)
    Debug.Print rngArea.Address(True, True, xlA1)
     
    rngArea.Formula = strFormula
             
    Set rngArea = Nothing
    Set rngLastCell = Nothing
    Set rngFirstCell = Nothing
     
End With
------------------------------------------------------------------
 
いちいち Range 型の変数を介在させると
こういうことになるのですが。
 
それぞれのセル範囲の参照やアドレス取得等の処理に関しては、
別途ユーザー定義関数を設けた方がよいと思います。
 
引用:
F3からCTRL+Shift+↓をしたようにしたいです。

その方法だと F3 より下のセルが全て空白セルだった場合
困ったことになるので、まずその発想は捨てるべきです。

回答
投稿日時: 19/07/02 17:04:24
投稿者: 半平太

>F3からCTRL+Shift+↓をしたようにしたいです。
 
初めに、そう言って貰えれば、
 
>OrderLastF = Range("F3").End(xlUp).Select
                ↓
 OrderLastF = Range("F3").End(xlDown).Row ’←と答えられた。下方へなのだから
 
でもまだ、これに答えてもらってないし
      ↓
>(F列とK列は別のものになり得るのですか?)
 
更に、こんな情報も追加された。
     ↓
>現状はデータが入らないセルにも計算式が入っていて容量的にもとても大きいものになっています。
 
そうなると、頭に浮かぶのは次の2点
 
1.「F3からCTRL+Shift+↓」をしたって、
   数式が入っていれば、セルが空白セルでも止まらない。
 
2.その数式は多分非効率な式で、何かにつけ足を引っ張る存在ではないか?
 
1については、以下で対応可能
  OrderLastF = Columns("F").Find(What:="*", LookIn:=xlValues, _
               LookAt:=xlPart, SearchDirection:=xlPrevious).Row
 
2については、最終行に入力されている数式を一つ見せてもらった方がよさそう。

回答
投稿日時: 19/07/02 17:11:48
投稿者: WinArrow
投稿者のウェブサイトに移動

半平太さんのレスにもありますが
 

引用:

OrderLastF = Range("F3").End(xlUp).Select
OrderLastK = Range("K3").End(xlUp).Select

 
↑のコードはおかしいですよ!1!
 
おかしな点1
なぜ、Selectメソッドが出てくるのか?
 
おかしな点2
なぜ、End(xlUp)・・セルF3より上方向に探すのか?
 
 
3つ目は、疑問です。
若し
OrderLastFとOrderLastKの値が値が違ったら?
と考えたことありますか?
 

回答
投稿日時: 19/07/02 17:15:42
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
>このセル範囲が取れないのかなとは思ってはいるのですが、
>色々調べてもわかりません。
 
調べ方が悪い
ステップ実行して、変数にどのような値が履いているか調べれば
一発で解決方法がわかります。
 
まず先入観を捨てましょう。

投稿日時: 19/07/02 17:45:07
投稿者: vaioyuki

皆様、ありがとうございます。
よく読んでまたお返事させていただきます。
 
簡単に行くかな?と思ってたけど、なかなか深いです。(T-T)

回答
投稿日時: 19/07/03 08:20:40
投稿者: WinArrow
投稿者のウェブサイトに移動

 
>簡単に行くかな?と思ってたけど
  
そうです‥‥難しく考えすぎです。
  
もっと、楽に考えるために、ヒントを掲載します。
  
Sub sample()
 Dim LastRow As Long
 Dim SHIKI As String
 Const oSHIKI As String = "=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF($F$3:$F$xxxx,$F3,$K$3:$K$xxxx),"""")"
   
     With ActiveSheet
         LastRow = .Range("F" & .Rows.Count).End(xlup).Row
         If LastRow >= 3 Then
             LastRow = 3
         End If
         '数式編集
        SHIKI = Replace(oSHIKI, "xxxx", LastRow)
     End With
 End Sub
   
 
 

投稿日時: 19/07/04 13:52:36
投稿者: vaioyuki

遅くなり申し訳ありません。
みなさま、ありがとうございました。
 
別件で躓いたところがあり、そこで
 

Sub keisan()
Dim lngLastDataRow As Long
Dim lngFirstFormuraRow As Long
Dim lngLastFormuraRow As Long

With ThisWorkbook.Worksheets("パターンC") 'パターンCシートA列の最終行の次の空白を取得
    lngLastDataRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
lngFirstFormuraRow = 3 '3列目から始める
[color=red]lngLastFormuraRow = lngLastDataRow + lngFirstFormuraRow[/color] - 2 '最終行から3行を足す

 
ここで最終行まで指定しているものを活用すればいいのか!!ということに気づき、
下手くそかと思いますが、
 
    .Range("AD" & lngFirstFormuraRow & ":AD" & lngLastFormuraRow).Formula = "=IF(COUNTIF($F$2:$F2,$F3)=0,SUMIF(F$3:F$" & lngLastFormuraRow & ",F3,K$3:K$" & lngLastFormuraRow & "),"""")" '計上オーダーごとの請求額合計"

 
こちらで落ち着きました。(^^;)
もっとスマートな書き方があるかと思いますが、今の私に理解できる精一杯です(苦笑)。
 
ありがとうございました!!