• ベストアンサー

VBAで変数の取扱い方を教えてください。(初心者です)

現在アクティブになっているセルに、下記のようにリストAというブックのシートAの任意のセルの式(文字)を取得したいと考えています。 下記ではR22C2となっていますが、この部分に任意のセルを取り込めるように変数を使いたいのですが、どのように変数を入れ込めばよいのか分かりません。(R1C1形式でなくA1形式でも構わないのですが) ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!R22C2" また、取得先のセルが空っぽの時の表示が0になってしまうのですが、無表示にすることは可能でしょうか? 初歩的な質問かも知れませんがよろしくお願いいたします。

質問者が選んだベストアンサー

  • ベストアンサー
  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.3

こんにちは。 >リストAというブックのシートAの任意のセルの式(文字)を取得したいと考えています。 たぶん、ブックのシートの値を取り出すという意味だと思います。 このコードの欠点は、一度、セルに式を置いて、Excel側のリンク機能を使わないといけないことですね。式を置かずに、取り出す方法もありますが、それはご要望次第です。また、この類のコードは、ユーザー定義関数に変換できません。以下のコードは、A1方式でも、R1C1方式でも、どちらでも可能です。 また、取得先のセルが空っぽ(Empty)と「0」とを区別できませんので、「0」の時は、消去するようにしました。 なお、ブック名が見つからないときは、メッセージが出ますが、シート名が違うときは、Excelからダイアログが出ます。 Sub GetDatafromBook() '他のブックから値を取り出す式の入力 Dim myRow As Variant, myCol As Variant, FileName As String Dim SheetName As String, ModeA1 As String    FileName = "リストA"  'ファイル名  SheetName = "シートA"  'シート名  myRow = 22  '行数  myCol = 2   '列数  ModeA1 = "" 'A1方式、こちらに入れると、こちらが優先される。    If Len(Dir(FileName)) = 0 Then MsgBox "ブック名が違います。", 64: Exit Sub  If Len(ModeA1) = 0 Then  'R1方式  ActiveCell.FormulaR1C1 = _  "='[" & FileName & "]" & SheetName & "'!R" & myRow & "C" & myCol    Else  'A1方式  ActiveCell.FormulaLocal = _  "='[" & FileName & "]" & SheetName & "'!" & ModeA1    End If  '式が0の場合は、中身が削除され。(0とEmtpyの区別はない)  If ActiveCell.Value = 0 Then ActiveCell.ClearContents  '値に変換   'ActiveCell.Value = ActiveCell.Value End Sub

maidoookini2005
質問者

お礼

ありがとうございました。 みなさんのご回答を元に悩みが解決しました。 詳しく書いていただきとても参考になりました。

その他の回答 (2)

  • hana-hana3
  • ベストアンサー率31% (4940/15541)
回答No.2

Dim R Dim C R = 1 C = 1 ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!R" & R & "C" & C '空白の時に0表示を消す場合 ActiveCell.FormulaR1C1 = "=IF('[リストA.xls]シートA '!R" & R & "C" & C & "="""","""",'[リストA.xls]シートA '!R" & R & "C" & C & ")" 'A1型式 Dim Cel Cel = "A1" ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!" & Cel

maidoookini2005
質問者

お礼

ありがとうございました。 みなさんのご回答を元に悩みが解決しました。

  • papayuka
  • ベストアンサー率45% (1388/3066)
回答No.1

関数式の中で変数化したい部分を & でつなげればOKです。 Sub Test1()  For i = 1 To 10   ActiveCell.Value = "='[リストA.xls]シートA '!B" & i   ActiveCell.Offset(1, 0).Activate  Next i End Sub ちょっと解りづらいと思いますが、下記は IF で "" の時に "" にしてます。 "" を入れるのは """" のようにします。 Sub Test2()  For i = 1 To 10   ActiveCell.Value = "=IF('[リストA.xls]シートA '!B" & i & _             "="""","""",'[リストA.xls]シートA '!B" & i & ")"   ActiveCell.Offset(1, 0).Activate  Next i End Sub

maidoookini2005
質問者

お礼

ありがとうございました。 みなさんのご回答を元に悩みが解決しました。

maidoookini2005
質問者

補足

早速のご回答ありがとうございました。 ご提示頂いたプログラムで対処できそうです。 ところでFormulaR1C1の場合、相対位置を示すため変数が2つ必要ですがこのような場合はどのように表記するのでしょうか? それと折角ご回答頂いたのに恐縮ですが後のプログラムは意味がよく理解できませんでした。

関連するQ&A

  • ActiveCell.FormulaR1C1の変数

    ActiveCell.FormulaR1C1 に変数を用いたいのですがうまくいきません。 For i=0 To 5 ActiveCell.FormulaR1C1 = "=-R["&n&"]C[-2]-100" next i としたのですがどこが間違っているのでしょうか? R1の1を変数にしたいです。よろしくお願いします。

  • エクセル マクロ VBA について

    以下はセルB2.C2.D2.E2.F2をアクティブセルから右方向へ入力しています。ここでの入力とは"=" + "セルB2" というものです。一つずつ入力している為マクロが長くなります。短くシンプルなものにしたいです。ご教示お願いします。 ActiveCell.FormulaR1C1 = "=R2C2" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=R2C3" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=R2C4" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=R2C5" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=R2C6"

  • Excel VBAについて

    以下のコードをFor Nextでまわすには どうしたらよいでしょうか? Sub sample() Range("A2").Select ActiveCell.FormulaR1C1 = "=テスト!R[1]C" Range("A3").Select ActiveCell.FormulaR1C1 = "=テスト!R[3]C" Range("A4").Select ActiveCell.FormulaR1C1 = "=テスト!R[5]C" Range("A5").Select ActiveCell.FormulaR1C1 = "=テスト!R[7]C" Range("A6").Select ActiveCell.FormulaR1C1 = "=テスト!R[9]C" Range("A7").Select ActiveCell.FormulaR1C1 = "=テスト!R[11]C" End Sub

  • ワークシート関数にVBAでセルを指定するには?

    FormulaR1C1ではなく Range形式でVBAでワークシート関数をセルにいれるコードを書くにはどうすればいいでしょう? Sub Macro() ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" End Sub これを Sub Macro() ActiveCell.FormulaR1C1 = "=LEN(a1)" End Sub こうすると、 数式バーには、=LEN('a1')と入っており、 セルの値は#NAME? になります。 アポストロフィーが余計なのですがどうすれば取れますか?

  • エクセルVBAが書ける方へ

    お世話になります。 下記VBAはB19からB28までのデータをA11:12の横列に貼り付けさせる内容です。 Sub 特価() ' ' 特価 Macro ' ' Range("A11:A12").Select ActiveCell.FormulaR1C1 = "=R[8]C[1]" Range("B11:B12").Select ActiveCell.FormulaR1C1 = "=R[9]C" Range("C11:C12").Select ActiveCell.FormulaR1C1 = "=R[10]C[-1]" Range("F11:F12").Select ActiveCell.FormulaR1C1 = "=R[11]C[-4]" Range("H11:I12").Select ActiveCell.FormulaR1C1 = "=R[12]C[-6]" Range("T11:U12").Select ActiveCell.FormulaR1C1 = "=R[13]C[-18]" Range("X11:Y12").Select ActiveCell.FormulaR1C1 = "=R[14]C[-22]" Range("AD11:AE12").Select ActiveCell.FormulaR1C1 = "=R[15]C[-28]" Range("AN11:AO12").Select ActiveCell.FormulaR1C1 = "=R[16]C[-38]" Range("AW11").Select ActiveCell.FormulaR1C1 = "=R[17]C[-47]" Range("B23").Select End Sub これに下記内容を追加したいのですが教えて下さい。 ・B19からB28のデータは入力後消す。 ・貼り付けたら次に入力する時は下の行(A13:14)に貼り付ける。 ・同様にデータを消してA15:16に貼り付ける。以下、下の行に貼り付けていくようにする。 ・貼り付けたらカーソルはB19を選択した状態にする。 言葉足らずでしたら補足します。 何卒宜しくお願い致します。

  • ExcelのVBAのAutoFillの使い方について

    Excel97のマクロでAutoFillを使おうとしているのですが、エラーが出て実行できません。 何も表示されていないシートでコマンドボタンを押すと、AutoFillを使ってA1セル~E1セルとA2セル~E2セルに数字の1~5が表示されるようにしたいと考えています。 下記のようにマクロを書いたところ、A1~E5はうまくできたのですが、 「Selection.AutoFill Destination:=ActiveCell.Range("A2:E2"), Type:=xlFillSeries」 の行でエラーが発生します。 「実行時エラー:1004 RangeクラスのAutoFillメソッドが失敗しました。」と表示されます。 Private Sub CommandButton1_Click()   ActiveSheet.Range("A1").Select   ActiveCell.FormulaR1C1 = "1"   Selection.AutoFill Destination:=ActiveCell.Range("A1:E1"), Type:=xlFillSeries   ActiveSheet.Range("A2").Select   ActiveCell.FormulaR1C1 = "1"   Selection.AutoFill Destination:=ActiveCell.Range("A2:E2"), Type:=xlFillSeries End Sub どなたかエラーの原因を教えていただけないでしょうか? よろしくお願いいたします。

  • 同期の並び替えで、規則的ですが、数が多くて処理に困っています。何かいい方法はないでしょうか?

    同期の並び替えで、規則的ですが、数が多くて処理に困っています。何かいい方法はないでしょうか? 一通り最後までやったのですが、ものすごい数になってしまいました。 下記 Sub Macro1() Sheets("sheet2").Select 'sheet1のA君のデータ(C列の10,14,18,22~90)をsheet2(C列)に同期 Range("C8").Select ActiveCell.FormulaR1C1 = "=sheet1!R[2]C" Range("C9").Select ActiveCell.FormulaR1C1 = "=sheet1!R[5]C" Range("C10").Select ActiveCell.FormulaR1C1 = "=sheet1!R[8]C"      ・      ・      ・   Range("C31").Select ActiveCell.FormulaR1C1 = "=sheet1!R[71]C" 'sheet1のB君のデータ(D列の10,14,18,22~90)をsheet2(E列)に同期 Range("E8").Select ActiveCell.FormulaR1C1 = "=sheet1!R[2]C[-1]" Range("E9").Select ActiveCell.FormulaR1C1 = "=sheet1!R[5]C[-1]" Range("E10").Select ActiveCell.FormulaR1C1 = "=sheet1!R[8]C[-1]"      ・      ・      ・   Range("C31").Select ActiveCell.FormulaR1C1 = "=sheet1!R[71]C[-1]" 'sheet1のC君のデータ(E列の10,14,18,22~90)をsheet2(G列)に同期 Range("G8").Select ActiveCell.FormulaR1C1 = "=sheet1!R[2]C[-2]" Range("G9").Select ActiveCell.FormulaR1C1 = "=sheet1!R[5]C[-2]" Range("G10").Select ActiveCell.FormulaR1C1 = "=sheet1!R[8]C[-2]"      ・      ・      ・   Range("G31").Select ActiveCell.FormulaR1C1 = "=sheet1!R[71]C[-2]"       と続き 最終的には 'sheet1のV君のデータ(X列の10,14,18,22~90)をsheet2(AS列)に同期   Range("AS8").Select    ActiveCell.FormulaR1C1 = "=集計関連!R[2]C[-21]"      ・      ・      ・   Range("AS31").Select    ActiveCell.FormulaR1C1 = "=集計関連!R[71]C[-21]" End Sub と一応規則的に並び替えています。…が、途方もない数になってしまうのですが、何か省略したり…というような方法はないでしょうか? よろしくお願いいたします。

  • VLookupで参照する範囲を変数で渡したい

    マクロ内で開いた別ファイルにあるリストをVLookupで参照するマクロを作っています。 固定のファイルで行う場合には以下の内容で動いています。 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'070801.xls'!R1C1:R2430C6,6,FALSE)" このファイル名[070801.xls]とセル範囲(行数がファイルによって違う)のを変数で渡す必要があります。 #細かい部分は割愛しています。 Workbooks.Open Filename:=Fname, ReadOnly:=True 'ファイル読み込み Range("A1").CurrentRegion.Select 'データ全体選択 Selection.SpecialCells(xlCellTypeLastCell).Select '最終行検出 endRcell = ActiveCell.Row ********ここの間に入る処理 ********多分「Range(Cells(1, 1), Cells(endRcell, 6))」など ThisWorkbook.Activate Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],?????,6,FALSE)"                              ↑ここに指定する変数 2カ所について、どのように指定すればよいのか分かりません。 色々試してみたのですが・・・・ よろしくお願いいたします。

  • VBAでエラーになってしまう

    初心者です。 RangeクラスのSelectメゾットが失敗しました。となります。 このコードは、マクロの記録を行い、そのコードをコマンドボタンのコードにコピーしたものです。 初心者なので、マクロの記録からやりました。 どうか、わかる方がいらっしゃいましたら、教えてください。 Private Sub CommandButton4_Click() Sheets("印刷ページ").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("C6").Select ←ここが黄色くなり、エラーになります。 ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("D6").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("E6").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("B8").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("C8").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("D8").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("E8").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("F8").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("B10").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("C10").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("D10").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" Range("E10").Select ActiveCell.FormulaR1C1 = "='鈴木 一郎'!R[2]C" End Sub

  • VBAでVlookupを使って

    いつもお世話になっています。 VBAで現場検索登録シートと言うところに 一覧シートからVlookupを使って 表示させようと思い作りました。 エラーも何もでません。 ただ、表示されるものがちゃんと検索されるものと まったくされないものがあります。 Vlookupはマクロの記録から作ったものです。 原因がわかりません。 よろしくお願い致します。 '検索ボタン Private Sub CommandButton6_Click() '送り方 Range("C4:D4").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,22,FALSE)" Selection.Value = Selection.Value '封筒 Range("C5:D5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,23,FALSE)" Selection.Value = Selection.Value '得意先名ふりがな Range("C6:F6").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,6,FALSE)" Selection.Value = Selection.Value '得意先名 Range("C7:F8").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE)" Selection.Value = Selection.Value '現場名ふりがな Range("C9:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,9,FALSE)" Selection.Value = Selection.Value '現場名 Range("C10:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,7,FALSE)" Selection.Value = Selection.Value End Sub エラーも何もなく、ちゃんと動くことは動きます。 検索結果が、検索コードによって違うのです。 よろしくお願い致します

専門家に質問してみよう