[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『再び質問すみません。』（ちぃさん）
いつもお世話になります。
また解らないことがでてきてしまい考えても解決できないので質問に参りました。
[[20140708195505]] での追加質問になります。
宜しくお願いします。
どうしてもご教授いただいたコードを使い転記始まり行を指定したいのですが思うように実行できません。
現在は少しコードを変更し
Sub Chili2()
'//転記元のブックに書く
Dim r As Range
Dim c
Dim tbl
'
'//転記元シートを開いた状態で行う処理
Set r = Union(Range("A2,C2,E2,G2"), _
Range("P9,F11,H11,J11,L11,N11,P11,R11"), _
Range("R9"))
ReDim tbl(1 To r.Count)
Dim i As Long
i = 1
For Each c In r
tbl(i) = c.Value
i = i + 1
Next c
'
'//転記先のパスとシート
Const 転記先BOOK As String = "C:\Users\Owner\Desktop\Book2.xlsx"
Const 転記先SHET As String = "Sheet1"
'
'//ブックが開かれていた場合の処理
On Error Resume Next
Open 転記先BOOK For Append As #1
Close #1
If Err > 0 Then
MsgBox "転記先ブックが読み取り専用です。読取専用を解除してから再度実施してください。"
Exit Sub
End If
On Error GoTo 0
'
'//転記シートの処理
Workbooks.Open 転記先BOOK
With Workbooks(Dir(転記先BOOK))
With .Sheets(転記先SHET)
.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl)) = tbl
If c.Row < 3 Then '←追加部分１
Set c = .Range("B3") '←追加部分２
End If '←追加部分３
.Range("B" & Rows.Count).End(xlUp).Offset(, -1) = Now() '←追加部分４
End With
Application.DisplayAlerts = False
.Save
.Close
Application.DisplayAlerts = True
End With
MsgBox "転記しました"
End Sub
としているのですが「追加部分１」の所で「実行エラー424」がでてしまいます。
知識が乏しく思いついたコードが[[20141116093241]] で教えていただきましたものでしたがご教授いただいたコードには[[20141116093241]] で教えていただいた形では対応できないみたいです。
未熟で申し訳ないのですが、お助け頂けないでしょうか。
どうか宜しくお願いいたします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
仕様を明確にした方がいいですよ？
まぁ、推測は出来ます。
＞ With .Sheets(転記先SHET)
＞ .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl)) = tbl
＞ If c.Row < 3 Then '←追加部分１
＞ Set c = .Range("B3") '←追加部分２
＞ End If '←追加部分３
＞ .Range("B" & Rows.Count).End(xlUp).Offset(, -1) = Now() '←追加部分４
＞ End With
＞ Application.DisplayAlerts = False
↓
With .Sheets(転記先SHET)
Set c = .Range("B" & Rows.Count).End(xlUp).Offset(1)
If c.Row < 3 Then
Set c = .Range("B3")
End If
End With
c.Resize(, UBound(tbl)) = tbl
c.Offset(, -1) = Now()
Application.DisplayAlerts = False
（半平太） 2014/12/06(土) 18:51
希望しているように転記できました。
未熟すぎる私に少し教えていただけないでしょうか。
マクロはまだよく理解できておらず皆様が記述されているものをネットで少しづつしらべて少しづつ理解しているつもりではあるのですが。。。。。
正直いって
.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl)) = tbl
コチラ全く仕様が理解できておりません。
理解ができていないまま転記の日時を記録しようとしたので
.Range("B" & Rows.Count).End(xlUp).Offset(, -1) = Now() '←追加部分４
↑このようなお恥ずかしいものを付けてしまったのですが（照）
よく見かける書き方に
For ○○ = 1 To Cells(〜省略
とか
Set ○○ = Range(〜省略
とか色々見かけるのですが
.Range("B" & Rows.Count〜省略 が始まる前に何もなく 省略〜UBound(tbl)) = tbl と 最後に = tbl がついていてなんで？と考えても意味がまったくわからずです。
どうしてこのような形になっているのでしょう？
もしよかったら教えて頂けないでしょうか。
宜しくお願いいたします。
（ちぃさん） 2014/12/06(土) 19:47
＞.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl)) = tbl
＞コチラ全く仕様が理解できておりません。
それですか。
１．まず「配列tblに格納されている値をどこかに入れたい」訳ですよね？
そう云う希望がないと何もスタートしません。当たり前ですね(^_^;)
２．次に、どこに(範囲）それを入れるのかを考えますよね？(つまり「＝」の左辺）
左辺になるべきは、Rangeオブジェクトで、横長の範囲です（tblの要素の数だけ右に長いもの) ※Valueプロパティは省いても作動しますが、省略しない方がいいと云われています。
さて、目標のオブジェクトの先頭はB列であることは決まっていますが、何行目かは動的に決まります。 つまり、B列の最下行から上方向に見て行って、最初にぶつかるデータセルの真下のセルです。 それをVBAで表現すると
＞.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl))
↑~ ~~~~~~~￣￣~↑~￣￣ ￣↑~~~~~ ~~~~~↑~~ ￣↑~~~~~~~~~↑~~~~~~
１ 2 3 4 5 6
１はピリオドでこの左にその親オブジェクトが省略されています。(今回は「転記先のシート」です） ２はB列の最下行のセルです。 Rowsの頭にピリオドがないので親のシートオブジェクトがエクセルにお任せになっています。 それは少し危ういコーディングでした。 今は、古い65536行と新しい1048576行が混在している時期であり 明確にしないと思わぬところで足をすくわれます。つまり、こうすべきと云うこと→ .Rows.Count
Rowsはこの場合、シートの行オブジェクトで、Countはその内包する行数です。
結局、.Range("B" & Rows.Count)は、.Range("B1048576")と云う単独セルと同じ意味になります。
３ その単独セルのEndプロパティで上方向を見ると、上にある最初のデータセル取得することが出来ます。
４ ・・で、書き込みはそのセルの直下からなので、OFFSETで一つ下のセルを取得します。
５ 一つのセルじゃ書き込み先としてはまずいので、その範囲をResizeプロパティを使ってtblの要素数だけ右に広げます
UBound()関数は、配列の最大値要素であり、必ずしも要素数とは限らないのですが、
ReDim tbl(1 To r.Count) としていますので、今回は同じです。
変数ｒに変更がないので、こうしても同じ意味になります。 ↓ .Resize(, r.Count)
＞理解ができていないまま転記の日時を記録しようとしたので
＞.Range("B" & Rows.Count).End(xlUp).Offset(, -1) = Now() '←追加部分４
＞↑このようなお恥ずかしいものを付けてしまったのですが（照）
いえ、それで合っています。自分で考えてやることが上達に繋がります（スマートさは２の次です）
＞よく見かける書き方に ＞For ○○ = 1 To Cells(〜省略 省略されると、なんだか分かりません。(何にでも成れちゃいます） 兎に角、そこで得られた数値までです。
＞省略〜UBound(tbl)) = tbl と 最後に = tbl がついていてなんで？ tblが無かったら、何をしようとしているか分からないです。 配列tblに格納されている値群をシート上に高速に転記する常套手段です。 一つ一つセルに書き込んでいては遅いのです。まぉ、13セルくらいでは全然差は出てこないでしょうけど・・・・
（半平太） 2014/12/06(土) 21:13
非常に事細かく説明していただき感謝いたします。
無知な私でも理解しやすいです。
＞ 省略されると、なんだか分かりません。
質問しておきながら省略いてしまい大変失礼な書き込みをお許しください。
確かに For は色々意味ありますよね（汗）
質問がわかりにくかったですね（汗）
でも （半平太）さんから回答いただいた
＞１はピリオドでこの左にその親オブジェクトが省略されています。(今回は「転記先のシート」です）
が私が知りたかった内容です（嬉）
＞5. 一つのセルじゃ書き込み先としてはまずいので、その範囲をResizeプロパティを使ってtblの要素数だけ右に広げます
UBound()関数は、配列の最大値要素であり、必ずしも要素数とは限らないのですが、
ReDim tbl(1 To r.Count) としていますので、今回は同じです。
変数ｒに変更がないので、こうしても同じ意味になります。 ↓ .Resize(, r.Count) ？？？？（汗） ＞ReDim tbl(1 To r.Count) ↑この部分はなんとなくわかるのですが ＞変数ｒに変更がないので、こうしても同じ意味になります。 ↓ .Resize(, r.Count) ｒに変更がないので ↑。。。。。。。これが難しいです。（汗） 「ｒに変更」ってどんな事なのでしょうか（泣）
それと素人的な質問なのですが
＞c.Resize(, UBound(tbl)) = tbl
この部分が End With の後にあるのが解らないです。
With 〜 End With の間にないから別の処理になりそうなような。。。。
でもいけているのが不思議です。
＞c.Offset(, -1) = Now()
↑この部分も難しいです。
なぜ c. だけでいけるのですか？
本当に度々申し訳ありません。
もしよければ教えていただけないでしょうか。
宜しくお願いいたします。
（ちぃさん） 2014/12/06(土) 22:25
＞１はピリオドでこの左にその親オブジェクトが省略されています。(今回は「転記先のシート」です） ＞が私が知りたかった内容です（嬉）
その上のWith句に書かれているオブジェクトを見ればすぐ知ることが出来ます。
↓
＞ With .Sheets(転記先SHET)
＞ .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(tbl)) = tbl
＞UBound()関数は、配列の最大値要素であり、必ずしも要素数とは限らない 極端な話、ReDim tbl(200 to 201) だったら、 Ubound(tbl)関数は、201を返しますが、そこに含まれる個数は、たったの2個です。
＞＞変数ｒに変更がないので、こうしても同じ意味になります。 ＞ ↓ ＞ .Resize(, r.Count) ＞ｒに変更がないので ＞↑。。。。。。。これが難しいです。（汗） ＞「ｒに変更」ってどんな事なのでしょうか（泣） rは、Dim r As Range と定義されていますので、Range型オブジェクト変数ですよね？ 「変数」なんですから、どの範囲にでも(いつでも）変更できちゃいます。
例えば、後で Set r = range("A1:D2") なんて使い回されたら、
その後は r.Count は8個になっちゃいます。
＞＞c.Resize(, UBound(tbl)) = tbl ＞この部分が End With の後にあるのが解らないです。 ＞With 〜 End With の間にないから別の処理になりそうなような。。。。 ＞でもいけているのが不思議です。 ＞なぜ c. だけでいけるのですか？
変数cは、前の方で Set c = .Range("B3") となっているので、
Sheets(転記先SHET)のB列の書き出すべきセルとして確定させたオブジェクト(への参照)です。
With句で修飾される必要はありません。
その例では、Rangeオブジェクトですが、シートオブジェクトでこれを考えてみますね。 二つのブック(A,B)に同じ名前のシート(Sheet1)があるとします。 WorkSheet型のオブジェクト変数「Ws」を定義して、
With Books("A")
Set Ws = .Sheets("Sheet1")
End With
としたら、あとはWsを使いたいシーンでは、単独でWsが使えます。 Aブックのサポートは不要です。自分自身でその情報を持っています。 BブックのSheet1になりはしないかなんて心配はサラサラないです。 (後でそうなるように参照先を変更すれば話は別ですよ。勿論）
ついでに言っておきますが、With句の使い方が分からなければ、使う必要はありません。
一つ一つチャンと修飾すればいいです。こんな風に。
↓
Set Ws =Books("A").Sheets("Sheet1")
※上の例ではわざとWithを使いましたが、本当にこの一行だけなら、使う方が不適切と云えます。
（半平太） 2014/12/06(土) 23:51
なるほど。
＞ 極端な話、ReDim tbl(200 to 201) だったら、
＞Ubound(tbl)関数は、201を返しますが、そこに含まれる個数は、たったの2個です。
たしかに。
＞変数cは、前の方で Set c = .Range("B3") となっているので、
＞Sheets(転記先SHET)のB列の書き出すべきセルとして確定させたオブジェクト(への参照)です。
＞With句で修飾される必要はありません。
これは知りませんでした。
勉強になります。
＞ついでに言っておきますが、With句の使い方が分からなければ、使う必要はありません。
＞一つ一つチャンと修飾すればいいです。こんな風に。
↓
Set Ws =Books("A").Sheets("Sheet1")
たしかどこかにWithは主語が省略されているという意味とか書いてたの見た気がしてきました（汗）
＞※上の例ではわざとWithを使いましたが、本当にこの一行だけなら、使う方が不適切と云えます。
この書き込みを見て思い出しました。
ありがとうございます。
私のために貴重な時間を使っていただき本当にありがとうございました。
感謝いたします。
またわからないことが出てきましたら質問に来たいとおもいます。
ありがとうございました。
（ちぃさん） 2014/12/07(日) 00:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.