Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2919 / 13646 ツリー ←次へ | 前へ→

【65278】もっと簡単にしたいのですが??? 八家九僧陀 10/5/4(火) 22:45 質問[未読]
【65279】Re:もっと簡単にしたいのですが??? かみちゃん 10/5/4(火) 22:59 発言[未読]
【65280】Re:もっと簡単にしたいのですが??? 八家九僧陀 10/5/4(火) 23:34 発言[未読]
【65281】Re:もっと簡単にしたいのですが??? かみちゃん 10/5/4(火) 23:40 発言[未読]
【65294】Re:もっと簡単にしたいのですが??? 八家九僧陀 10/5/5(水) 18:44 お礼[未読]
【65295】Re:もっと簡単にしたいのですが??? かみちゃん 10/5/5(水) 18:51 発言[未読]
【65282】Re:もっと簡単にしたいのですが??? りん 10/5/5(水) 9:19 発言[未読]

【65278】もっと簡単にしたいのですが???
質問  八家九僧陀  - 10/5/4(火) 22:45 -

引用なし
パスワード
   賞与支給VBAを作成しています。
賞与の源泉徴収には前月分の課税対象所得額を取り込む必要があり、以下のVBAをマクロ記録で記録したものに加工して作成したのですが、以下についてご教示ください。
使用Book:賞与処理.xls - Sheet("賞与メニュー")、Sheet("前月分")
     格納庫.xls - Sheet("平成22年")
'Sheet("平成22年")には1年間(1〜12月まで)の全社員の給与データが格納されています。

mykey:Sheet("賞与メニュー").Range("P14") →Sheet("平成22年")の支給日データ(日付型)を支給日が重複しないコンボリストに作成してP14で選択させています。

1.AutoFilterのCriterialが支給月のたびに(賞与支給月の前月分)変わるので、変数mykeyで取り込もうとしましたが、うまくいきません。VBAに直接"H22.03.26"と記述して実行するとうまくできるのですが、mykeyにするとうまくいきません。mykey自体が空なのか、日付型として認識せずにAutoFilterできないのか?、解決策をご教示ください。
2.Application.DisPlayAlerts=Falseを記述しないと「クリップボードに大きな情報があります。・・・」と表示され、「はい」を選択しないと先に進みません。Application.DisPlayAlerts=Falseと記述しておけばよいのですが、クリップボードを経由しないですむ記述をご教示ください。
3.ActiveWindow.WindowState=xLMaximized
 Application.Windowstate=xLMaximized と記述しないと、画面が最小のままで、そのwindowに左右並列表示の片割れ状態で表示されたままになってしまうため、この記述をしています。このまま記述しておいてもよいのですが、解決策はありませんか?

以上3点について、以下のVBAから解決策をご教示ください。
なお手書きのため大文字、小文字が混在、また単純なスペルミスがあろうかと思いますがご容赦ください。(VBAに直接"H22.03.26"と入力するとうまく実行されます)

Sub 前月分データ取得()
Worksheets("賞与メニュー").select
Dim mykey As Variant 'mykeyは日付データです
mykey = Range("p14").Value

 Application.ScreenUpdating=False

Worksheets("前月分").Cells.Clearcontents ’データ抽出前に前回分をクリア
Range("A1").Select

Const cnsFind_SYOUYOFILE As String="C:\Windows\デスクトップ\給料\格納庫.xls"
Const cnsFind_Sheet As String="平成22年"

Workbooks.Open(cnsFind_SYOUYOFAILE)
Worksheets.("平成22年").Select

Selection.Autofilter Field:=1,Criterial:=mykey
'mykey部分に"H22.3.26"と直入力だとうまくいく
Rows("2:200").Select '抽出後、抽出データをsheet"前月分"に行複写するため
Selection.Copy
Range("a2").Select '黒く反転部分する部分を解除するため

Application.WindowState=xlNormal
Windows.Arrange ArrangeStyle:=xlvertical
'sheet"平成22年"が表示されているので、Sheet"前月分"を前面に表示させるため
Windows("賞与処理.xls").Activate
Worksheets("前月分").Activate
Range("a1").Select '抽出データのコピー先セル

Application.DisPlayAlerts=False

Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone,SkipBlanks:=False,Transpose:=False

Workbook("格納庫.xls").Close False

Range("A:B,E:BZ,CB:CL").Select
Selection.Delete Shift:=xlToLeft
Range("a1").Select
'90列中、必要なのはC列社員ID,D列氏名、CA列課税対象所得額の3列のみのためその他は列削除する

ActiveWindow.WindowState=xLMaximized
Application.Windowstate=xLMaximized
'賞与処理.xls - Sheet("前月分")を画面に全体表示するため

Application.ScreenUpDating=True

End Sub

【65279】Re:もっと簡単にしたいのですが???
発言  かみちゃん E-MAIL  - 10/5/4(火) 22:59 -

引用なし
パスワード
   こんにちは。かみちゃん です。

全体のシート内容がよくわからないのですが、

>1.AutoFilterのCriterialが支給月のたびに(賞与支給月の前月分)変わるので、変数mykeyで取り込もうとしましたが、うまくいきません。VBAに直接"H22.03.26"と記述して実行するとうまくできるのですが、mykeyにするとうまくいきません。mykey自体が空なのか、日付型として認識せずにAutoFilterできないのか?、解決策をご教示ください。

格納庫.xls - Sheet("平成22年")
のシートレイアウトを教えてください。
特に
Selection.Autofilter Field:=1,Criterial:=mykey
に対応する列は、
2010/3/26 という日付型(シリアル値)ではなく、
H22.3.26 という文字列 なのか?

次に、
賞与処理.xls - Sheet("賞与メニュー")
の P14 セルはどのような値が入っているのか教えてください。
mykey = Range("p14").Value
というコードになっていますが、
2010/3/26 という日付型(シリアル値)ではなく、
H22.3.26 という文字列 なのか?
それとも、表示形式で設定しているのか、

mykey = Range("p14").Text
とするとどのような値が myKey に入るのか?

>2.Application.DisPlayAlerts=Falseを記述しないと「クリップボードに大きな情報があります。・・・」と表示され、「はい」を選択しないと先に進みません。Application.DisPlayAlerts=Falseと記述しておけばよいのですが、クリップボードを経由しないですむ記述をご教示ください。

どこのコードでそのメッセージが出るのかは把握されていますか?

> Rows("2:200").Select '抽出後、抽出データをsheet"前月分"に行複写するため
> Selection.Copy
> Range("a2").Select '黒く反転部分する部分を解除するため
>
> Application.WindowState=xlNormal
> Windows.Arrange ArrangeStyle:=xlvertical
>'sheet"平成22年"が表示されているので、Sheet"前月分"を前面に表示させるため
> Windows("賞与処理.xls").Activate
> Worksheets("前月分").Activate
> Range("a1").Select '抽出データのコピー先セル

の部分は

Rows("2:200").Copy Workbooks("賞与処理.xls").Worksheets("前月分").Range("A1")

という1行にまとめられ、クリップボードを経由しません。

>3.ActiveWindow.WindowState=xLMaximized
> Application.Windowstate=xLMaximized と記述しないと、画面が最小のままで、そのwindowに左右並列表示の片割れ状態で表示されたままになってしまうため、この記述をしています。このまま記述しておいてもよいのですが、解決策はありませんか?

まったく確認していませんが、
必要ないのではないでしょうか?

【65280】Re:もっと簡単にしたいのですが???
発言  八家九僧陀  - 10/5/4(火) 23:34 -

引用なし
パスワード
   ▼かみちゃん さん:
早速応答いただきありがとうございます。
>
>格納庫.xls - Sheet("平成22年")
>のシートレイアウトを教えてください。
Sheet("平成22年")には、1年間(1月から12月、賞与月8月と12月)の全社員の給与データが格納されています。
A列〜CL列のリストデータです。
A1〜CL1は見出し、A列は支給日が格納され、[H22.03.26][H22.01.29]等、書式設定-ユーザー定義で"gee.yy.mm.dd"と日付データとして設定しています。

>次に、
>賞与処理.xls - Sheet("賞与メニュー")
>の P14 セルはどのような値が入っているのか教えてください。
>mykey = Range("p14").Value
>というコードになっていますが、
>2010/3/26 という日付型(シリアル値)ではなく、
>H22.3.26 という文字列 なのか?
>それとも、表示形式で設定しているのか、
>
>mykey = Range("p14").Text
>とするとどのような値が myKey に入るのか?

P14には、入力規則でリストから選択するように設定しています。そのリストはSheet("平成22年")A列のデータをDictinaryを使って(あるサイトから盗用したものですが)重複しないデータリストとして作成したもので、P14のリストデータとsheet("平成22年")A列のデータは同じだと思い込んでいるのですが!

>
>>2.については、Rows("2:200").Copy Workbooks("賞与処理.xls").Worksheets("前月分").Range("A1")を試してみます。

>>3.ActiveWindow.WindowState=xLMaximized
>> Application.Windowstate=xLMaximized と記述しないと、画面が最小のままで、そのwindowに左右並列表示の片割れ状態で表示されたままになってしまうため、この記述をしています。このまま記述しておいてもよいのですが、解決策はありませんか?
 AutoFilterするための格納庫.xls - Sheet("平成22年")の表示(開き方?)の仕方が悪いのではと思っているのですが?

【65281】Re:もっと簡単にしたいのですが???
発言  かみちゃん E-MAIL  - 10/5/4(火) 23:40 -

引用なし
パスワード
   こんにちは。かみちゃん です・

>>賞与処理.xls - Sheet("賞与メニュー")
>>の P14 セルはどのような値が入っているのか教えてください。
>>mykey = Range("p14").Value
>>というコードになっていますが、
>>2010/3/26 という日付型(シリアル値)ではなく、
>>H22.3.26 という文字列 なのか?
>>それとも、表示形式で設定しているのか、
>>
>>mykey = Range("p14").Text
>>とするとどのような値が myKey に入るのか?
>
>P14には、入力規則でリストから選択するように設定しています。そのリストはSheet("平成22年")A列のデータをDictinaryを使って(あるサイトから盗用したものですが)重複しないデータリストとして作成したもので、P14のリストデータとsheet("平成22年")A列のデータは同じだと思い込んでいるのですが!

以下のように聞いているのですが、ご確認いただけませんか?
そちらのブックが全く見えないので、要領を得ません。

>>mykey = Range("p14").Value
>>というコードになっていますが、
>>2010/3/26 という日付型(シリアル値)ではなく、
>>H22.3.26 という文字列 なのか?
>>それとも、表示形式で設定しているのか、
>>
>>mykey = Range("p14").Text
>>とするとどのような値が myKey に入るのか?

【65282】Re:もっと簡単にしたいのですが???
発言  りん E-MAIL  - 10/5/5(水) 9:19 -

引用なし
パスワード
   八家九僧陀 さん、おはようございます。

>P14には、入力規則でリストから選択するように設定しています。そのリストはSheet("平成22年")A列のデータをDictinaryを使って(あるサイトから盗用したものですが)重複しないデータリストとして作成したもので、P14のリストデータとsheet("平成22年")A列のデータは同じだと思い込んでいるのですが!
A列のデータと違うかもしれない(というか違うと思う)ので、何が入っているかを確認してみては。

Sub mykeyの確認()
  Dim mykey As Variant 'mykeyは日付データです
  '値を参照するだけならSelect(Activate)不要
  mykey = ThisWorkbook.Worksheets("賞与メニュー").Range("p14").Value
  MsgBox "Type = " & VarType(mykey) & vbCrLf & _
     "Value : " & mykey, vbInformation, "TYPE = 7が正当な日付データ"
End Sub
詳細はVarType関数のヘルプで確認してみてください。

あと、コードは標準モジュールに記述してありますか?

【65294】Re:もっと簡単にしたいのですが???
お礼  八家九僧陀  - 10/5/5(水) 18:44 -

引用なし
パスワード
   ▼かみちゃん さん、りんさん、ご教示ありがとうございました。

りんさんに教えていただいたmykeyの確認を、"賞与メニュー"のP14と"平成22年"のA列の適当なセルで確認しました。いづれも「TYPE=7、value21/3/26」と表示され、共に日付データでした。
また、かみちゃんさんに教えていただいた
Rows("2:200").Copy Workbooks("賞与処理.xls").Worksheets("前月分").Range("A1")を試したところ、バッチリうまくいきました。
肝心のmykeyの定義のところで、mykey = Range("p14").Textとしたところ完璧に、データコピーも画面の表示もうまくできました。

りんさん、データ型の確認方法、これからも盗用させていただきます。
ありがとうございました。

かみちゃんさん、おかげさまでスッキリしました。ありがとうございました。

ところで、今後、肝に銘じておくために、初歩的なことを教えてください。
セルへのデータ転記は、range("〇〇")=value,textbox〇〇.valueとバカのひとつ覚え的に思っていましたが、今回 Range("p14").valueではうまくいかず、valueをTextに修正しただけですべてうまくいきました。
[value]とした場合、[text]とした場合の違い、使い分け法を詳しく教えていただけませんか?

【65295】Re:もっと簡単にしたいのですが???
発言  かみちゃん E-MAIL  - 10/5/5(水) 18:51 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>[value]とした場合、[text]とした場合の違い、使い分け法

私も最近知りましたので、下手な説明より以下のコードを新規シートで試してみてください。

Sub test()
 Range("A1").Value = Date
 Range("A1").NumberFormatLocal = "[$-411]ge.m.d;@"
 
 MsgBox "Valueの場合-->" & Range("A1").Value & vbCrLf & _
     "Textの場合-->" & Range("A1").Text
End Sub

2919 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free