• 締切済み

VBAでの複雑な関数の作動方法

VBAのプログラミングで関数「=LEN($A$1)-LEN(SUBSTITUTE($A$1,"1",""))」を反映する方法について質問です。 以下プログラミングなら作動するのですが Sub Sample() Dim Wb As Workbook For Each Wb In Application.Workbooks Wb.Worksheets("Sheet1").Range("B1").Formula = "=LEN(A1)" Next Wb End Sub 上記のような複雑な関数になると「"」のせいなのかエラーが出て動作しません。 VBAプログラミングでは作動不可能なんでしょうか？

みんなの回答

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.2

１回限りの確定で値を出してよいのなら（データが変わり連動を考えなくてよいのなら） ActiveWorkbook.Worksheets("Sheet1").Range("B2") = Len(Range("A2")) - Len(Replace(Range("A2"), "1", "")) のようにすることもできます。 引数のセル番地の表現がＲａｎｇｅ（”Ａ１”）かＣｅｌｌｓ（１，１）式に変わること。 エクセル関数ではあるが、ＶＢＡでは別の関数を使うべきことがあること 関数でよく行われる、式の複写の変わりに、繰り返し処理をするように、式を組み立てるので、絶対番地かどうかは、ほとんどの場合気にしないでよい などがあります。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.1

マクロの自動記録を用いて数式バーから代入すると、 ActiveCell.FormulaR1C1 = "=LEN(R1C1)-LEN(SUBSTITUTE(R1C1,""1"",""""))" となります。 「"」の所を見比べてみると、 ・数式内の"は"" ・数式内の""は"""" となってます。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • Word VBA

    ※行頭の下線はプログラムに関係ありません、インデントと解釈ください。 Excel VBAで既に開いているワークブックの一覧取得は Sub test() __Dim wb As Workbook __For Each wb In Workbooks ____Debug.Print wb.Name __Next wb End Sub で成功したのですが、 Word VBAで Sub test() __Dim wb As Object __For Each wb In CreateObject("Excel.Application").Workbooks ____Debug.Print wb.Name __Next wb End Sub では何も表示されません。 問題解決のための知識を与えてくださる方、宜しくお願いします。

  • 2つのvbaを統合したい

    はじめまして、vba初心者のものです。 よろしくお願いします。 以前こちらに質問させて頂いたことがあります。 その以前質問して回答して頂いた2つのソースを一つに統合したいです。 1、参照元ブックと貼り付けブックの2つが存在します。 2、参照元ブックの名前の指定する必要はあると思いますが、 貼り付け先ブックは新規作成にしたいです。 3、「▼質問タイトル：vba ブック間でシート名のコピーをするには」の動作は、 貼り付け先ブックの各シートに反映されるようにしたいです。 何卒よろしくお願いします。 「▼質問タイトル：vba ブック間でシート名のコピーをするには」 http://okwave.jp/qa/q8727280.html sub macro1() 　dim wb1 as workbook 　dim w2 as worksheet 　dim i as long ’２つのブックは既に開いている事 　set wb1 = workbooks("オリジナルブック.xlsm")　’拡張子まで正しく指定する事 　set w2 = workbooks("貼り付け先ブック.xlsx").worksheets(1)　’同上 ’準備 　w2.range("A:A").clearcontents 　w2.range("A:A").numberformat = "@" 　w2.range("A1") = "シート名一覧" ’転記 　for i = 1 to wb1.worksheets.count 　if wb1.worksheets(i).name = "Sheet1" then exit for 　w2.range("A65536").end(xlup).offset(1) = wb1.worksheets(i).name 　next end sub 「▼質問タイトル：セルの項目をシート名にしたい」 http://okwave.jp/qa/q8727637.html sub macro1() 　dim h as range 　dim s as long, i as long 　s = worksheets.count + 1 ’シートを作る 　for each h in range("C3:B" & range("C65536").end(xlup).row) 　worksheets.add after:=worksheets(worksheets.count) 　activesheet.name = h.value 　next ’別のブックにする 　for i = worksheets.count to s step -1 　worksheets(i).select false 　next i 　activewindow.selectedsheets.move end sub

  • エクセルＶＢＡで他のbookのセルcellsで参照

    エクセルＶＢＡで他のbookのセルの値（一定の範囲）を参照したいのですが、変数を使いたいため、cellsを使用したいのですがうまくいきません。方法はないでしょうか。 下記に例を示します。　rangeを使用すればすべてｏｋ（(2)(5)）（この場合はset文を使用しなくてもok(5)）。同じｂｏｏｋならｃｅｌｌｓ使用ｏｋ(4)。 他のｂｏｏｋをcells文使用する方法はないでしょうか（もちろんできれば、Thisbookの方もceｌｌsを使用したい）。 よろしくお願いします。 sub test() Dim ThisBook As Workbook Dim Workbook2 As Workbook 'マクロを実行しているワークブック Set ThisBook = ThisWorkbook '他のワークブック Set Workbook2 = Workbooks("test11.xlsx") ' 'ThisBook.Worksheets(1).Range("A1:B2").Value = Workbook2.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value　　'(1)だめ 'ThisBook.Worksheets(1).Range("A1:B2").Value = Workbook2.Worksheets(1).Range("a1:b2").Value　'(2)　OK 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:B2").Value = Workbooks("test11.xlsx").Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value　'(3)　だめ 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:B2").Value = Workbooks("test1.xlsm").Worksheets(1).Range(Cells(3, 3), Cells(4, 4)).Value　　'(4)だめ 'Workbooks("test1.xlsm").Worksheets(1).Range("A1:ii8000").Value = Workbooks("test11.xlsx").Worksheets(1).Range("a1:ii8000").Value　　'(5)　ｏｋ End Sub

  • VBA 実行時エラーで、"プロパティまたはメソッド

    ・Sheet1(コード) Private Sub CommandButton1_Click() Call aaa End Sub ・Module1(コード) Sub aaa() Dim wb As Workbook Dim ws As Worksheet Workbooks.Open ("c:\test.xls") Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") wb.ws.Range("A2").Value = "CCC" End Sub wb.ws.Range("A2").Value = "CCC"の部分で 以下の実行エラーが出ます。 ------------------------------------------------------------------------ 実行時エラー'438': オブジェクトは、このプロパティまたはメソッドをサポートしていません。 ------------------------------------------------------------------------ Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") の部分で特にエラーも出ないので、オブジェクトの取得は成功していると 思うのですが、WorkSheetオブジェクトのwsからRangeメソッドを呼ぶことが できません。 動かない原因と対策を教えてください！！

  • VBAの関数について

    VBA初心者です。 VLOOKUP関数の使い方がわかりません。 具体的に書かせてもらうと VBAの中にVLOOKUP関数を使いたいです 試験1ファイルのシート"sheet1"にある 検索値を使い 試験2ファイルの指定した検索範囲を 試験1ファイルのシート"sheet1"の指定した位置に数値を挿入することです。 例えばですが（この使い方も間違ってるかもしれません）コードを書いてみると 試験1の検索値はA5セルとします 試験2の検索範囲はB1～C20とします 検索範囲から取得した数値の位置はD5とします VLOOKUPに関してですが 検索値は変数を使い 検索値範囲も変数を使い 列番号はC列がいいので3で 検索方法はFALSEでお願いします sub test() dim a as range dim b as workbooks dim c as range set a = range("B1～C20") set b = workbooks("sheet1") set c = range("A5") workbooks("試験1").worksheets("sheet1").range("D5").value=workbooks("試験2").activesheet."=VLOOKUP(ここがわかりません)".value end sub そもそも変数やsetの使い方が間違ってるかもしれませんm(__)m コードを書いて貰えるととても助かります。 回答お願いします。

  • 「 VBA の 宣言 」 がない場合の問題点は ？

    下記例で、 「 宣言 」　なしでも、現在のところ、問題は発生してませんが、 今後、「 宣言 」　がなかった場合の　「 問題点の例 」　を教えて下さいませ。 ------------------------------- Sub ブックA*の全シートをコピー() Dim Wb As Workbook　'宣言 For Each Wb In Workbooks If Wb.Name Like "ブックA*.xls" Then With Workbooks("ブックB.xls") Wb.Worksheets _ .Copy after:=.Sheets(.Sheets.Count) End With End If Next Worksheets(Worksheets.Count).Activate MsgBox ActiveSheet.Index Worksheets("Sheet1").Select End Sub

  • エクセルVBAのオートフィルタについて

    いつもお世話になります。 エクセル2007でVBAでオートフィルタを操作したいのですが、 一部うまくいきません。 以下の様なコードを書いて 日付で絞り込みたいのですが、 何も抽出されません。 リストを見てみると、変数はちゃんと入っており OK ボタンを押すとその日付で抽出されます。 何故VBAでの操作では抽出されないのでしょうか。 ご存じの方がおられましたら、よろしくお願いします。 Sub test() Dim mydate As Variant Dim rng3 As Range Dim fmt As Variant Dim objList3 As ListObject Dim wb1 As Workbook Dim wb2 As Workbook Dim wb4 As Workbook Dim sh1 As Worksheet Dim sh2 As Worksheet Dim sh3 As Worksheet Dim sh4 As Worksheet Dim sh7 As Worksheet '----------------------------------------------------------------------- Set wb1 = Workbooks("301.xlsm") Set wb2 = Workbooks("1.xls") Set wb4 = Workbooks("2.xls") Set sh1 = wb1.Worksheets("@") Set sh2 = wb1.Worksheets("@@") Set sh3 = wb2.Worksheets("@@@") Set sh4 = wb2.Worksheets("@@@@") Set sh7 = wb4.Worksheets("@@@@@") '---------------------------------------------------------- sh2.Range("A1:z63").ClearContents With sh7 Set objList3 = .ListObjects("リスト1") fmt = .Range("A2").NumberFormatLocal mydate = Format(mydate, fmt) objList3.Range.AutoFilter Field:=7, Criteria1:=mydate objList3.Range.AutoFilter Field:=5, Criteria1:="test" Set rng3 = objList3.Range.SpecialCells(xlCellTypeVisible) rng3.Copy sh2.Range("A2") objList3.Range.AutoFilter Field:=5 objList3.Range.AutoFilter Field:=5, Criteria1:=">=190" Set rng3 = objList3.Range.SpecialCells(xlCellTypeVisible) rng3.Copy sh2.Range("A20") objList3.Range.AutoFilter Field:=5 objList3.Range.AutoFilter Field:=7 End With Application.CutCopyMode = False Set rng3 = Nothing Set fmt = Nothing Set objList3 = Nothing Set wb1 = Nothing Set wb2 = Nothing Set wb4 = Nothing Set sh1 = Nothing Set sh2 = Nothing Set sh3 = Nothing Set sh4 = Nothing Set sh7 = Nothing End Sub （一部省略しています）

  • VBAの繰りかえし処理について

    workbook１(以下wb1）のB3に入力した県名を含む行を、 workbook２から取り出し、wb1のB7以降に表示させたいと思っています （ちなみに県名はwb2のC列に入っています） 同じ県名が含まれる行が多いので、それらを繰り返し処理で 全て書き出したいと思い、以下のマクロを作りました。 Sub macro3() Dim c Dim wb1 As Workbook Dim wb2 As Workbook Dim k As Integer Dim firstAddress As String Application.ScreenUpdating = False Set wb1 = ActiveWorkbook Set wb2 = Workbooks.Open("G:\zyouhousyori\inn100best_full.csv") Set c = cell.Find(What:=Range("B3").Value) With wb2.Worksheets(1).Range("A1:A100") If Not c Is Nothing Then firstAddress = c.Address Do Set c = cell.FindNext(c) For k = 0 To 10 .Range("C100").End(xlUp).Offset(1).Copy _ wb1.Worksheets("sheet1").Cells(7 + k, 2) Exit For ★Loop While Not c Is Nothing And _ c.Address <> firstAddress End If End With Application.ScreenUpdating = True wb2.Close False End Sub しかし、実行すると★マークのついた所でエラーになってしまいます （対応するDoがありません、と出ます） VBA初心者なので、どこがどう違うのかいまいちわかりません； アドバイスお願いします。

  • VBAでご相談です！

    Excel2010使用。 VBA初心者です。 VBAでご相談させて下さい。 複数のファイルを１つにまとめる 作業をしたいと思い、ググったところ あるサイトで下記のコードを見つけました。 ただ、このコードでは、ファイルをダイアログから 選択する形になります。 これを、ファイルを指定した状態で実行させたいと思い、 自分で試してみたのですが、上手くいきませんでした。 同一フォルダ内には４つのファイルがあり、全て同じ様式の シートが複数あります。ただ、フォルダ名が毎月変更になります。 この同一フォルダ内のデータの中の特定のシートを一つのシートに まとめたいと考えているのですが、可能でしょうか？ 可能であれば、アドバイスいただけるとありがたいです。 Sub sample() Dim myPath As String Dim wb_A As Workbook, wb_B As Workbook Dim i As Long, s As Long myPath = Application.GetOpenFilename(("Excel ファイル (*.xls), *.xls"), , "データを蓄積するブックを選択して下さい。") If myPath = "False" Then Exit Sub Set wb_A = Workbooks.Open(myPath) myPath = Application.GetOpenFilename(("Excel ファイル (*.xls), *.xls"), , "データを取得するブックを選択して下さい。") If myPath = "False" Then Exit Sub Set wb_B = Workbooks.Open(myPath) With wb_B For i = 1 To .Worksheets.Count 'wb_Bループ For s = 1 To wb_A.Worksheets.Count 'wb_Aループ '同じ名前のシートがあるとき データコピー If .Worksheets(i).Name = wb_A.Worksheets(s).Name Then .Worksheets(i).Range("A1").CurrentRegion.Copy _ wb_A.Worksheets(i).Range("A65536").End(xlUp).Offset(1) Exit For End If '同じ名前のシートが無いとき シートコピー If s = wb_A.Worksheets.Count Then .Worksheets(i).Copy Before:=wb_A.Sheets(1) End If Next s Next i wb_B.Close False MsgBox "完了" End With End Sub ※長文、説明下手で申し訳ありませんが よろしくお願いします。 ＜参考URL＞ 　　http://www.excel.studio-kazu.jp/kw/20040709212700.html

  • VBAについてです。

    VBAについてです。 当方VBA初心者です。初歩的な質問があり投稿しました。 Workbooks("aaaa.xlsx").Worksheets("aaa").Range("A3:A50").Copy _ Workbooks("bbbb.xlsx").Worksheets("bbb").Range("A2") というコピーはできるのに Workbooks("aaaa.xlsx").Worksheets("aaa").Range(cells(3, 1), cells(50, 1)).Copy _ Workbooks("bbbb.xlsx").Worksheets("bbb").Range("A2") というコピーはどうしてできないのでしょうか？ また、最下行を判別して Workbooks("aaaa.xlsx").Worksheets("aaa").Range(cells(3, 1), cells(最下行, 1)).Copy _ Workbooks("bbbb.xlsx").Worksheets("bbb").Range("A2") という様に簡単に処理したいのですが、やはり無理なのでしょうか？ ブックが違うのですが、これをなんとかこのような処理にしたいと思っています。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する