マンガでよめる痔のこと・薬のこと

いつもお世話になっております。
エクセル2013のVBAで質問です。
例えばJ192セルに =SUM($M192:$AX192) と入力されていたとします。
これの範囲の幅(38列分)はそのままで、下記のコードで得た end_col の列番号(CD)の位置まで列をずらすような指定の方法はありますか?ただし、end_col は一定ではありません。

VBAで位置をずらして、このような数式に範囲を変えたいです。 =SUM($AS192:$CD192)

Sub test()
Dim end_col As Long
Cells(3, Cells(3, Columns.Count).End(xlToLeft).Column).EntireColumn.Select
end_col = Selection.Column
End Sub

すいませんが、ご教授ください。

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

A 回答 (3件)

こんにちは。



課題としては、R1C1形式での参照を利用するのが、直球と思います。
(行を指定せずとも相対参照できますし、、、。)
パッと思い付いた内の、状況によって使い分けできるようなものだけ、4例挙げてみます。
お奨めはSub Re8896257c、現在の数式の参照先を基準にしていますので応用が利きます。
Sub Re8896257dだけはA1形式での参照です。

Sub Re8896257a() ' 列数38固定、38-1=37列手前の列から始まる範囲をR1C1参照する
Dim end_col As Long
  end_col = Cells(3, Columns.Count).End(xlToLeft).Column
  Range("J192").FormulaR1C1 = "=SUM(RC" & end_col - 37 & ":RC" & end_col & ")"
End Sub

Sub Re8896257c()  ' 列数可変、現在の[数式の参照先]の列数を基準に範囲をR1C1参照する
Dim end_col As Long
Dim cnt_col As Long
  end_col = Cells(3, Columns.Count).End(xlToLeft).Column
  With Range("J192")
    cnt_col = .Precedents.Areas(1).Columns.Count
    .FormulaR1C1 = "=SUM(RC" & end_col - cnt_col + 1 & ":RC" & end_col & ")"
  End With
End Sub

Sub Re8896257d()  ' 列数可変、現在の[数式の参照先]の最右の列位置を基準にOffsetで範囲をA1参照する
Dim end_col As Long
Dim fml_end_col As Long
Dim ref As String
  end_col = Cells(3, Columns.Count).End(xlToLeft).Column
  With Range("J192")
    With .Precedents.Areas(1)
      fml_end_col = .Columns(.Columns.Count).Column
      ref = .Offset(, end_col - fml_end_col).Address(RowAbsolute:=False, ColumnAbsolute:=True)
    End With
    .Formula = "=SUM(" & ref & ")"
  End With
End Sub

Sub Re8896257j()  ' 列数可変、現在の[数式文字列]から先頭列・最後列を求めR1C1参照する
Dim end_col As Long
Dim fml_1st_col As Long
Dim fml_end_col As Long
Dim fml As String
  end_col = Cells(3, Columns.Count).End(xlToLeft).Column
  With Range("J192")
    fml = .FormulaR1C1
    fml_1st_col = Val(Mid(fml, InStr(fml, "C") + 1))
    fml_end_col = Val(Mid(fml, InStrRev(fml, "C") + 1))
    .FormulaR1C1 = "=SUM(RC" & fml_1st_col + end_col - fml_end_col & ":RC" & end_col & ")"
  End With
End Sub
    • good
    • 0
この回答へのお礼

できました。ありがとうございました。
今回はおすすめのを採用することにしました。

お礼日時:2015/01/25 20:45

こんにちは。



マクロで、数式をいじるのは、面倒極まりないですね。
最初から、マクロで値を出したほうが楽です。
以下は、常識的なエラー対処はされています。

'//
Sub TestSetFormula()
 Dim EndCol As Long
 Dim i As Long
 Dim r As Range
 Dim r1 As Range
 Dim r2 As Range
 Dim sAdr As String
 With ActiveSheet
  Set r = Range("J192")
  EndCol = .Cells(3, Columns.Count).End(xlToLeft).Column
  If EndCol < 2 Then MsgBox "データがありません。", 48: Exit Sub
  If Not r.HasFormula Then MsgBox r.Address & " には数式がありません。", 48: Exit Sub
  sAdr = r.DirectPrecedents.Address
  Set r1 = Range(sAdr)
  i = r1.Columns.Count  'または、38の固定した定数なら、リテラルに書くか、定数を置きます。
  If EndCol >= i Then
   Set r2 = .Cells(r1.Row, EndCol).Offset(, -i + 1).Resize(, i)
   r.FormulaLocal = "=SUM(" & r2.Address & ")"
  End If
 End With
End Sub
'//
    • good
    • 1
この回答へのお礼

できました。ありがとうございました。

お礼日時:2015/01/25 20:43

列の変更に対して、列を絶対参照にする必要性が意味ないと思うけど。

。。

Sub test()
Dim end_col As Long
Cells(3, Cells(3, Columns.Count).End(xlToLeft).Column).EntireColumn.Select
end_col = Selection.Column

Cells(192,10).Formula = "=SUM("& Cells(192,end_col - 37).Address(RowAbsolute:=False) & ":" & Cells(192,end_col).Address(RowAbsolute:=False) & ")"

End Sub
    • good
    • 0
この回答へのお礼

できました。ありがとうございました。

お礼日時:2015/01/25 20:42

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

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

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

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

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

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

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

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

Aベストアンサー

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

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

QExcel VBAの SUM関数について質問です。

Excel VBAの SUM関数について質問です。

SUM関数で合計の値を出す場合、

SUM(A1:A5)という関数で合計を出しますが

SUM(sheet2.cells(1,1),sheet2.cells(5,1))
と言ったようにCellsで指定はできないのでしょうか?

複数の列にVBAのプログラムで合計の値を出したいのです。

WorksheetFunction.SumではSUM(A1:A5)のように
自動計算にならないと思うので・・・

Aベストアンサー

例えば、Sheet2のA6セルに質問の数式を入力するのであれば次のようにします。
Worksheets("Sheet2").Cells(6, 1).Formula = "=SUM(" & Cells(1, 1).Address & ":" & Cells(5, 1).Address & ")"

Sheet1のような別シートのA1セルに質問の数式を入力するのであれば次のようにします。
Worksheets("Sheet1").Cells(1, 1).Formula = "=SUM(Sheet2!" & Cells(1, 1).Address & ":" & Cells(5, 1).Address & ")"

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

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....続きを読む

Qエクセルの範囲指定を規則的にずらしたいのですが良い方法はありますか?

エクセルの範囲指定を規則的にずらしたいのですが良い方法はありますか?
今、悩んでいるのはこんな感じです。=max(60;100)の次の範囲が(160;200)、(260;300)となるように100ずつずらして範囲指定がなされるにはどのような方法がありますか?最初の数式に条件を加えてコピーで作成したいのですが・・・・。どなたかご教授お願いします!

Aベストアンサー

=MAX(OFFSET(60:100,100*(ROW()-1),0))
でどうでしょうか。
OFFSET は、第一引数で指定した範囲をずらす関数です。
1行目のセルにこの数式を入れている場合: ROW() が1になるので、MAX(OFFSET(60:100,0,0)) になり、これはMAX(60:100)になります。
このセルを2行目にコピーすると、同じ数式でも2行目ではROW() が2になるので、MAX(OFFSET(60:100,100,0)) となり、「60:100」という範囲をOFFSET関数で下に100行ずらしたものがMAX関数の引数となるため、MAX(160:200) の意味になります。

一番最初を1行目以外にしたい場合は、「ROW()-1」のところを適宜修正してください。

QEXCELで検索した値の下のセルの値を表示したい

たとえば列で

1郵便番号
2123-4567
3電話番号
401-1234-5678
5趣味
6かくかく
7特技
8しかじか

と表示されているとします。
ここから郵便番号を検索し、郵便番号の下のセルの値を表示させるにはどうすればいいでしょうか?

また、範囲指定は同じでも、電話番号であればその下の値が表示される方法です。

VLOOKUPやHLOOKUPではかならず検索される場所が範囲の上端や左端なので悩んでいます。

よろしくお願いします。

Aベストアンサー

lookup系の関数では、検索して見つかった値が返ってきますが、見つ
けた場所を返す関数もあります。また、範囲内の特定の場所の値を返
す関数もあります。

=match(検索する値, 探す範囲, 探し方)
=index(範囲, x行目, y列目, 領域番号)

これで例えば
=match("郵便番号", A1:A8, 0)
とやると 1 が返ってきますので、もう1行下ってことで+1して
index関数に代入する
=index(A1:A8, match("郵便番号", A1:A8, 0)+1, 1, 1)
と、123-4567が返ってくるわけです。

表の端っこ以外のところで検索しなくちゃ行けない場合に、よく使う
方法ですので、覚えといて損はないですよ。

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すると関係ないセルの値が返るので、
場...続きを読む

Qcellsで特定の離れた範囲を選択する方法は?

例えば、A1からC4までと、E6からG10までというように、離れた範囲を同時に選択したい場合、cells(i,j)方式で指定するにはどうしたらいいのでしょうか?
Range(Cells(1, "A"): Cells(4, "C"), Cells(6, "E"): Cells(10, "G")).Select
など色々試しているもののうまくいきません。
どなたかご教授いただければ幸いです。

Aベストアンサー

#1の補足について。
#1のご回答のポイントは、カンマで区切って並べれば良いと言うことです。
だからCellsでやりたいならば、Rangeの中でCellsを使って出きると思ったが、上手く行かないので、取りあえず下記でやった
Sub test05()
Union(Range(Cells(1, "A"), Cells(4, "C")), Range(Cells(6, "E"), Cells(10, "G"))).Select
End Sub

QエクセルVBAで#N/Aのようなエラー値を含むセルの検出は

エクセルVBAでセルに#N/Aのようなエラー値を含む場合Ifを使った構文で制御したいのですが、エラー値であるかどうかを調査するにはどうすれば良いでしょうか。教えてください。

Aベストアンサー

ワークシート関数のISERRORを使えばよいようです。

#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL! のいずれでもTRUEが返ります。

エラーの種類を検出するには、ERROR.TYPE関数を使用します。ただし、ERROR.TYPE関数でエラーのないセルを参照すると、#N/A が返ります。

詳細は、キーワード「エラー」または「IS関数」でヘルプをご参照ください。

VBAを使う場合は、CVErr 関数でエラー値を検出できます。
(詳細は、キーワード「セルのエラー値」で。)

QEXCELで別のシートのデータを参照して返す方法

ちょっとしたことなのですが、うまく式が書けなくて悩んでいます。

作業用のシート1と参照用のシート2があり、
シート1のA列には
 あおき
 あべ
 いぐち
 いはら
などと文字列(名前)が入力されています。
こちらは今度の野球のスタメンだと思ってください。

シート2にはA列とB列があって、
わたなべ 55
いぐち  43
あおやま 67
いはら  41
などと、名前:背番号が羅列されています。こちらが参照用の全選手の背番号リストだと思ってください。

ここで、 シート1のB列に、A列の名前に対応した背番号を返したいのです。
なので、シート1のB列に
IF(シート1のA列の値=シート2のA列のいずれかの値)だったとき、
適合したシート2の行のB列の値を表示する
という式を入れたいのですが、どのように式を書けばよいでしょうか。

すみませんがどなたか教えてください。

Aベストアンサー

私もその場合はVLOOKUP関数を使用しています。

「B1」=VLOOKUP(A1,Sheet2!$A:$B,2,0)


人気Q&Aランキング