Excel VBA質問箱 IV

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

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


1247 / 13644 ツリー ←次へ | 前へ→

【75453】L列にデータが入ってたらI列に記入 じん 14/4/3(木) 12:05 質問[未読]
【75454】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/3(木) 13:22 発言[未読]
【75455】Re:L列にデータが入ってたらI列に記入 じん 14/4/3(木) 16:39 質問[未読]
【75456】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/3(木) 17:01 発言[未読]
【75457】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/3(木) 17:04 発言[未読]
【75458】Re:L列にデータが入ってたらI列に記入 じん 14/4/3(木) 17:55 質問[未読]
【75459】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/3(木) 19:19 発言[未読]
【75466】Re:L列にデータが入ってたらI列に記入 じん 14/4/7(月) 13:33 質問[未読]
【75467】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/7(月) 17:43 発言[未読]
【75468】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/8(火) 10:07 発言[未読]
【75460】Re:L列にデータが入ってたらI列に記入 kanabun 14/4/4(金) 9:38 発言[未読]
【75465】Re:L列にデータが入ってたらI列に記入 じん 14/4/7(月) 11:49 お礼[未読]

【75453】L列にデータが入ってたらI列に記入
質問  じん  - 14/4/3(木) 12:05 -

引用なし
パスワード
   質問させてください。
資料を読み込んで一つにまとめるというマクロを組んでいます。

資料が何枚もあるので次々と次のセルに記入していくことになります。
そこで、仮にL列に360とはいっていたらI列には351-400という数字を入れたいのですが、なかなかうまくいきません。
色々、調べてここまで書いたのですが、思った操作になりません。


Dim i As Integer
Dim intL As Variant

 
i = 3
 
Do
i = i + 1
intL = SH2.Cells(i, 12)
If intL = "" Then
Exit Do
End If

  If intL >= 501 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "501 -   "
   
  ElseIf intL >= 451 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "451 - 500"

  ElseIf intL >= 401 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "401 - 450"
   
  ElseIf intL >= 351 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "351 - 400"
   
  ElseIf intL >= 301 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "301 - 350"
   
  ElseIf intL >= 251 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "251 - 300"
   
  ElseIf intL >= 201 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "201 - 250"
   
  ElseIf intL >= 151 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "151 - 200"
   
  ElseIf intL >= 100 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "101 - 150"
  
  ElseIf intL <= 100 Then
   SH2.Cells(Rows.Count, 9).End(xlUp).Offset(1) = "   - 100"
   

End If
Loop While intL <> ""

ご教示願います。

【75454】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/3(木) 13:22 -

引用なし
パスワード
   ▼じん さん:

>そこで、仮にL列に360とはいっていたらI列には351-400という数字を入れたいのですが、なかなかうまくいきません。
>色々、調べてここまで書いたのですが、思った操作になりません。

具体的にどんなことが「思った操作」にならないのですか?

L列に連続してデータが入っているなら、
↓こう(配列内でI列用の文字列を作成)したほうが速いと思いますが。

Sub Try1()
  Dim i As Long
  Dim r As Range
  Dim v
  
  With ActiveSheet
    Set r = Range("L3", .Cells(.Rows.Count, "L").End(xlUp)) 'L列データ範囲
    v = r.Value
    ReDim sa(1 To UBound(v), 0) As String
    For i = 1 To UBound(v)
      If Not IsEmpty(v(i, 1)) Then
        Select Case v(i, 1)
         Case Is >= 501: sa(i, 0) = "501 - "
         Case Is >= 451: sa(i, 0) = "451 - 500"
         Case Is >= 401: sa(i, 0) = "401 - 450"
         Case Is >= 351: sa(i, 0) = "351 - 400"
         Case Is >= 301: sa(i, 0) = "301 - 350"
         Case Is >= 251: sa(i, 0) = "251 - 300"
         Case Is >= 201: sa(i, 0) = "201 - 250"
         Case Is >= 151: sa(i, 0) = "151 - 200"
         Case Is >= 101: sa(i, 0) = "101 - 150"
         Case Else:   sa(i, 0) = "  - 100"
        End Select
      End If
    Next
    
    .Range("I3").Resize(UBound(v)).Value = sa
  End With
End Sub

【75455】Re:L列にデータが入ってたらI列に記入
質問  じん  - 14/4/3(木) 16:39 -

引用なし
パスワード
   データを読み込むとL列に
350とか240とかの数値が入ります。
仮にL列に350と入ったらI列に"301〜350"を表示させたいのです。

L列に記入されててI列が空欄の場所に表示させたいのです。

記入していただいたマクロでは反応しませんでした‥

【75456】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/3(木) 17:01 -

引用なし
パスワード
   ▼じん さん:

>データを読み込むとL列に
>350とか240とかの数値が入ります。
データを読み込むのは手動ですか?
どこかにあるデータをL列にコピーするのですか? そうすると、L列は
データを呼び込む前は空白ですか?


>仮にL列に350と入ったらI列に"301〜350"を表示させたいのです。
>
L列に1セル以上変更があったら動作するようなマクロは ワークシートの
Changeイベントを捉えて動くマクロをつかえばいいです。

>L列に記入されててI列が空欄の場所に表示させたいのです。
「I列が空欄の場所」という条件は初めて聴きました。
すると、「データを読み込む」まえにも、L列、I列には データがあるという
ことですか?

>
>記入していただいたマクロでは反応しませんでした‥
上のマクロは セルの入力に反応して動くマクロではないので、実行したいときに
手動で「マクロの実行」させるか、ボタンに登録しておいてそのボタンをクリック
して実行するマクロです。

情況がよく分かりませんので、
1. マクロ実行前のシートレイアウトと、
2. いつ実行するのか(自動で実行するのか/ 手動で実行するのか も)
3. マクロ実行後のシートレイアウト
を教えてください。

例 マクロ実行前
   I列      L列
1  301〜350   350
2
3  201〜250   210
4
5
6
7

↑こういう状態で、L4 に 120 と数値が入ったら、自動で
↓となるように、マクロを実行させたい。

例 マクロ実行前
   I列      L列
1  301〜350   350
2
3  201〜250   210
4  101〜150   120
5
6
7

【75457】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/3(木) 17:04 -

引用なし
パスワード
   訂正

下の
> 例 マクロ実行前

マクロ動作後、のまちがいでした。

【75458】Re:L列にデータが入ってたらI列に記入
質問  じん  - 14/4/3(木) 17:55 -

引用なし
パスワード
   ▼kanabun さん:
>▼じん さん:
>

>データを読み込むのは手動ですか?
>どこかにあるデータをL列にコピーするのですか? そうすると、L列は
>データを呼び込む前は空白ですか?

マクロで読み込んでます。
マクロで読み込み、転記用シートにコピー
転記用シートにコピーされたデータを本シートに転記していく感じです。
L列は自動で転記されていきます。

>「I列が空欄の場所」という条件は初めて聴きました。
>すると、「データを読み込む」まえにも、L列、I列には データがあるという
>ことですか?

すいません。
そういうことです。


>情況がよく分かりませんので、
>1. マクロ実行前のシートレイアウトと、

>2. いつ実行するのか(自動で実行するのか/ 手動で実行するのか も)
このマクロ自体は転記用シートから本シートに転記し終わった後に起動させます。

>3. マクロ実行後のシートレイアウト


>例 マクロ実行前
>   I列      L列
>1  301〜350   350
>2  201〜250   230
>3  201〜250   210 ←ここまで前回のデータが入っています。
>4         240 ←ここから今回転記したデータです。
>5         300
>6         100
>7         250
>
Iが空白でL列に文字が入ってたらマクロを起動して
>
>例 マクロ実行前
>   I列      L列
>1  301〜350   350
>2  201〜250   230
>3  201〜250   210
>4  101〜150   120
>5  251〜300   300
>6    〜100   100
>7  201〜250   250


説明不足で申し訳ございません。
このような感じです。

【75459】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/3(木) 19:19 -

引用なし
パスワード
   ▼じん さん:

>>2. いつ実行するのか(自動で実行するのか/ 手動で実行するのか も)
>このマクロ自体は転記用シートから本シートに転記し終わった後に起動させます。
>
>>3. マクロ実行後のシートレイアウト
>
>
>>例 マクロ実行前
>>   I列      L列
>>1  301〜350   350
>>2  201〜250   230
>>3  201〜250   210 ←ここまで前回のデータが入っています。
>>4         240 ←ここから今回転記したデータです。
>>5         300
>>6         100
>>7         250
>>
>Iが空白でL列に文字が入ってたらマクロを起動して
>>
>>例 マクロ実行前
>>   I列      L列
>>1  301〜350   350
>>2  201〜250   230
>>3  201〜250   210
>>4  101〜150   120
>>5  251〜300   300
>>6    〜100   100
>>7  201〜250   250

>このような感じです。
そういうことでしたか。となると、L列の処理を始める行は I列のデータの
最終行のつぎの行から、ということになりますね?

Sub 範囲セット()
  Dim i As Long
  Dim r1 As Range, r2 As Range
  Dim v
  
  With ActiveSheet
    Set r1 = .Cells(.Rows.Count, "I").End(xlUp).Offset(1)
    Set r2 = .Cells(.Rows.Count, "L").End(xlUp)
    v = Excel.Range(r1.Offset(, 3), r2).Value
    ReDim sa(1 To UBound(v), 0) As String
    For i = 1 To UBound(v)
      If Not IsEmpty(v(i, 1)) Then
        Select Case v(i, 1)
         Case Is >= 501: sa(i, 0) = "501 - "
         Case Is >= 451: sa(i, 0) = "451 - 500"
         Case Is >= 401: sa(i, 0) = "401 - 450"
         Case Is >= 351: sa(i, 0) = "351 - 400"
         Case Is >= 301: sa(i, 0) = "301 - 350"
         Case Is >= 251: sa(i, 0) = "251 - 300"
         Case Is >= 201: sa(i, 0) = "201 - 250"
         Case Is >= 151: sa(i, 0) = "151 - 200"
         Case Is >= 101: sa(i, 0) = "101 - 150"
         Case Else:   sa(i, 0) = "  - 100"
        End Select
      End If
    Next
    
    r1.Resize(UBound(v)).Value = sa
  End With
End Sub

↑はI列にデータが入っていないばあいを考慮に入れていません。

【75460】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/4(金) 9:38 -

引用なし
パスワード
   ▼じん さん:

>マクロで読み込み、転記用シートにコピー
>転記用シートにコピーされたデータを本シートに転記していく感じです。
>L列は自動で転記されていきます。
>

別案です。
他のところからデータをコピーして貼り付けると、貼り付けたとき、
Changeイベントというのが発生します。この方法は、これを利用するもの
です。
以下のコードを シート見出し(シートタブ)を右クリックして出てくる
「コードの表示」メニューで表示されるコード・ウィンドウに貼り付けて
L列にデータ貼付けてみてください。

'// L列にデータが追加/更新/削除があったら自動実行する
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long
  Dim r As Range, c As Range
  Dim sa As String
  
  Set r = Intersect(Target, Range("L:L"))
  If r Is Nothing Then Exit Sub
  
  Application.EnableEvents = False 'イベントを一時的に無視
  For Each c In r
    If IsEmpty(c.Value) Then
      c.Offset(, -3).ClearContents
    Else
      Select Case c.Value
       Case Is >= 501: sa = "501 - "
       Case Is >= 451: sa = "451 - 500"
       Case Is >= 401: sa = "401 - 450"
       Case Is >= 351: sa = "351 - 400"
       Case Is >= 301: sa = "301 - 350"
       Case Is >= 251: sa = "251 - 300"
       Case Is >= 201: sa = "201 - 250"
       Case Is >= 151: sa = "151 - 200"
       Case Is >= 101: sa = "101 - 150"
       Case Else:   sa = "  - 100"
      End Select
      c.Offset(, -3).Value = sa
    End If
  Next
  Application.EnableEvents = True 'イベント復活
End Sub

【75465】Re:L列にデータが入ってたらI列に記入
お礼  じん  - 14/4/7(月) 11:49 -

引用なし
パスワード
   お返事が遅くなりすいません。

説明不足で何度もご提示いただき、ありがとうございました。
お陰様で理想通りに動いてくれました。
本当にありがとうございました。

【75466】Re:L列にデータが入ってたらI列に記入
質問  じん  - 14/4/7(月) 13:33 -

引用なし
パスワード
   Sub 範囲セット()
  Dim i As Long
  Dim r1 As Range, r2 As Range
  Dim v
  
  With ActiveSheet
    Set r1 = .Cells(.Rows.Count, "I").End(xlUp).Offset(1)
    Set r2 = .Cells(.Rows.Count, "L").End(xlUp)
    v = Excel.Range(r1.Offset(, 3), r2).Value
    ReDim sa(1 To UBound(v), 0) As String
    For i = 1 To UBound(v)
      If Not IsEmpty(v(i, 1)) Then
        Select Case v(i, 1)
         Case Is >= 501: sa(i, 0) = "501 - "
         Case Is >= 451: sa(i, 0) = "451 - 500"
         Case Is >= 401: sa(i, 0) = "401 - 450"
         Case Is >= 351: sa(i, 0) = "351 - 400"
         Case Is >= 301: sa(i, 0) = "301 - 350"
         Case Is >= 251: sa(i, 0) = "251 - 300"
         Case Is >= 201: sa(i, 0) = "201 - 250"
         Case Is >= 151: sa(i, 0) = "151 - 200"
         Case Is >= 101: sa(i, 0) = "101 - 150"
         Case Else:   sa(i, 0) = "  - 100"
        End Select
      End If
    Next
    
    r1.Resize(UBound(v)).Value = sa
  End With
End Sub

こちらのマクロで動いたのは動いたのですが、
L列に一つだけしか入ってない場合
ReDim sa(1 To UBound(v), 0) As String
の部分で型が一致しませんと出ます‥

すいません、もう少しお付き合いください。

【75467】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/7(月) 17:43 -

引用なし
パスワード
   ▼じん さん:

>こちらのマクロで動いたのは動いたのですが、
>L列に一つだけしか入ってない場合
>ReDim sa(1 To UBound(v), 0) As String
>の部分で型が一致しませんと出ます‥
>
>すいません、もう少しお付き合いください。

これまでに メインの処理(Select Case〜End Select)部分はほとんど同じで
実行方法が異なる 2つのコードをお見せしました。ひとつは、

>Sub 範囲セット()

で、実行したいときに手動で[マクロの実行]をするものです。

もうひとつは、そのあと提案した

> '// L列にデータが追加/更新/削除があったら自動実行する
> Private Sub Worksheet_Change(ByVal Target As Range)

のほうで、こちらはシートに書いておくと、L列にデータが入力されたり
セル消去があったりしたとき「自動で」処理がなされるものです。
こちらのほうは対象セルが1つでもエラーにはなりません。

もし
> Private Sub Worksheet_Change(ByVal Target As Range)
ではまずいということであれば、

> Sub 範囲セット()

のほうを手直ししますが、
> Private Sub Worksheet_Change(ByVal Target As Range)
をまだお試しになっていないのなら、一度これも試されてみて、それから
どちらの方式でいくか、判断されてはどうでしょう。

【75468】Re:L列にデータが入ってたらI列に記入
発言  kanabun  - 14/4/8(火) 10:07 -

引用なし
パスワード
   2日間、アクセスできませんので、
Sub 範囲セット() 方式(手動実行)を値が1つのときに対応できるように
微修正したものです。

Sub 範囲セット2()
  Dim i As Long
  Dim r1 As Range, r2 As Range
  Dim v
  
  With ActiveSheet
    Set r1 = .Cells(.Rows.Count, "I").End(xlUp).Offset(1)
    Set r2 = .Cells(.Rows.Count, "L").End(xlUp)
    v = Excel.Range(r1.Offset(, 3), r2).Value
    '-----------------------------------------------------------
    If Not IsArray(v) Then   '★4行 追加
      ReDim v(1 To 1, 1 To 1)
      v(1, 1) = r1.Offset(, 3).Value
    End If
    '-----------------------------------------------------------
    ReDim sa(1 To UBound(v), 0) As String
    For i = 1 To UBound(v)
      If Not IsEmpty(v(i, 1)) Then
        Select Case v(i, 1)
         Case Is >= 501: sa(i, 0) = "501 - "
         Case Is >= 451: sa(i, 0) = "451 - 500"
         Case Is >= 401: sa(i, 0) = "401 - 450"
         Case Is >= 351: sa(i, 0) = "351 - 400"
         Case Is >= 301: sa(i, 0) = "301 - 350"
         Case Is >= 251: sa(i, 0) = "251 - 300"
         Case Is >= 201: sa(i, 0) = "201 - 250"
         Case Is >= 151: sa(i, 0) = "151 - 200"
         Case Is >= 101: sa(i, 0) = "101 - 150"
         Case Else:   sa(i, 0) = "  - 100"
        End Select
      End If
    Next
    
    r1.Resize(UBound(v)).Value = sa
  End With
End Sub

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