[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『コマンドボタンキャプション設定』(ゆうな)
お世話になります
ご教示いただけたら幸いです
表題について簡単に書く方法教えてください
取りあえず 一枚一枚総当たり
したのを書いてみましたが
簡素化したいです
よろしくお願いいたします
Private Sub UserForm_Initialize()
Dim Ws As Worksheet
Dim i As Integer
Dim c As Integer 'データの最終行
Set Ws = Worksheets(1)
c = Ws.Range("A2").End(xlDown).Row
For i = 2 To c
If Cells(i, "A") Like "*仙台*" Then
CommandButton14.Caption = "仙台" End If If Cells(i, "A") Like "*東京*" Then CommandButton14.Caption = "東京" End If If Cells(i, "A") Like "*札幌*" Then CommandButton14.Caption = "札幌" End If If Cells(i, "A") Like "*福島*" Then CommandButton14.Caption = "福島" End If If Cells(i, "A") Like "*新潟*" Then CommandButton14.Caption = "新潟" End If If Cells(i, "A") Like "*名古屋*" Then CommandButton14.Caption = "名古屋" End If If Cells(i, "A") Like "*静岡*" Then CommandButton14.Caption = "静岡" End If If Cells(i, "A") Like "*京都*" Then CommandButton14.Caption = "京都" End If If Cells(i, "A") Like "*倉敷*" Then CommandButton14.Caption = "倉敷" End If If Cells(i, "A") Like "*福岡*" Then CommandButton14.Caption = "福岡" End If End If Next
Dim Ws1 As Worksheet
Dim i1 As Integer
Dim c1 As Integer 'データの最終行
Set Ws1 = Worksheets(2)
c1 = Ws1.Range("A2").End(xlDown).Row
For i1 = 2 To c1
If Cells(i1, "A") Like "*仙台*" Then
CommandButton15.Caption = "仙台" End If If Cells(i1, "A") Like "*東京*" Then CommandButton15.Caption = "東京" End If If Cells(i1, "A") Like "*札幌*" Then CommandButton15.Caption = "札幌" End If If Cells(i1, "A") Like "*福島*" Then CommandButton15.Caption = "福島" End If If Cells(i1, "A") Like "*新潟*" Then CommandButton15.Caption = "新潟" End If If Cells(i1, "A") Like "*名古屋*" Then CommandButton15.Caption = "名古屋" End If If Cells(i1, "A") Like "*静岡*" Then CommandButton15.Caption = "静岡" End If If Cells(i1, "A") Like "*京都*" Then CommandButton15.Caption = "京都" End If If Cells(i1, "A") Like "*倉敷*" Then CommandButton15.Caption = "倉敷" End If If Cells(i1, "A") Like "*福岡*" Then CommandButton15.Caption = "福岡" End If Next
Dim Ws 2As Worksheet
Dim i2 As Integer
Dim c2 As Integer 'データの最終行
Set Ws2 = Worksheets(1)
c2 = Ws2.Range("A2").End(xlDown).Row
For i2 = 2 To c2
If Cells(i2, "A") Like "*仙台*" Then
CommandButton16.Caption = "仙台" End If If Cells(i2, "A") Like "*東京*" Then CommandButton16.Caption = "東京" End If If Cells(i2, "A") Like "*札幌*" Then CommandButton16.Caption = "札幌" End If If Cells(i2, "A") Like "*福島*" Then CommandButton16.Caption = "福島" End If If Cells(i2, "A") Like "*新潟*" Then CommandButton16.Caption = "新潟" End If If Cells(i2, "A") Like "*名古屋*" Then CommandButton16.Caption = "名古屋" End If If Cells(i2, "A") Like "*静岡*" Then CommandButton16.Caption = "静岡" End If If Cells(i2, "A") Like "*京都*" Then CommandButton16.Caption = "京都" End If If Cells(i2, "A") Like "*倉敷*" Then CommandButton16.Caption = "倉敷" End If If Cells(i2, "A") Like "*福岡*" Then CommandButton16.Caption = "福岡" End If End If Next
End Sub
< 使用 Excel:Excel2013、使用 OS:Windows7 >
(マナ) 2018/03/12(月) 23:14
これは、 Worksheets(3) の間違いですか?
(マナ) 2018/03/12(月) 23:17
>A列セルを全部確認するのですか。
>一番下のセルだけではだめなのですか。
大凡 A2からA10あたりに 土地名の入ってる文章が有ります
場所はA列の不変場所になります
> >Set Ws2 = Worksheets(1)
>これは、 Worksheets(3) の間違いですか?
そうです 間違いです
(ゆうな) 2018/03/13(火) 11:14
Private Sub UserForm_Initialize()
'==変数の宣言など
Dim buf As Variant Dim i As Long, c As Long Dim フラグ As Boolean
'==処理
For c = 1 To 3 With Worksheets(c) フラグ = False For i = 2 To .Range("A2").End(xlDown).Row If フラグ Then Exit For For Each buf In Array("仙台", "東京", "札幌", "福島", "新潟", "名古屋", "静岡", "京都", "倉敷", "福岡") If .Cells(i, "A") Like "*" & buf & "*" Then Me.Controls("CommandButton" & 13 + c).Caption = buf フラグ = True Exit For End If Next buf Next i End With Next c End Sub
(もこな2) 2018/03/13(火) 12:07
CountIfを使ってみたり・・・
Private Sub UserForm_Initialize()
Dim arr As Variant Dim c As Long
For c = 1 To 3
With Worksheets(c)
For Each arr In Array("仙台", "東京", "札幌", "福島", "新潟", "名古屋", "静岡", "京都", "倉敷", "福岡")
If WorksheetFunction.CountIf(.Range("A2:A10"), "*" & arr & "*") >= 1 Then Me.Controls("CommandButton" & 13 + c).Caption = arr Exit For End If
Next arr
End With
Next c
End Sub (774) 2018/03/13(火) 14:19
処理方法が解らなくて
勉強になりました
有難う御座います
(ゆうな) 2018/03/13(火) 20:53
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.