返回列表 上一主題 發帖

[發問] 昨天開始接觸VBA，想簡化程式...

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2016-6-2 16:26 | 只看該作者

[發問] 昨天開始接觸VBA，想簡化程式...

大大們好！
從這裡找到非常有用的資訊
拼拼貼貼之下完成了一個簡單的程式
現在有一個欄位工號需要做比對，比對正確後轉換成姓名帶入combobox
原本用case select完成，可是50筆資料我要一筆一筆輸入...實在眼花
程式如下
If IsNumeric(TextBox6.Text) Then
     Dim num As Integer
     num = TextBox6.Text
    Else
        If Not IsNumeric(TextBox6.Text) Then
         MsgBox "欄位輸入必須為數值"
        End If
    End If
    Set y = Sheets("CNC名單(All1)")
Select Case num
Case "1908"
    ComboBox1.Value = "黃xx"
Case "2029"
    ComboBox1.Value = "陳xx"
Case "2066"
    ComboBox1.Value = "林xx"
Case "2145"
    ComboBox1.Value = "陳yy"
Case "2167"
    ComboBox1.Value = "陳aa"
Case "2645"
    ComboBox1.Value = "劉bb"
Case "2732"
    ComboBox1.Value = "洪cc"

目前有一個sheet專門放這些清單
所以想從A2開始比對工號，比對正確後將此工號的B欄位姓名帶入combobox1的value
請問該如何進行~~~


有試過這樣寫
If IsNumeric(TextBox6.Text) Then
     Dim num As Integer
     num = TextBox6.Text
    Else
        If Not IsNumeric(TextBox6.Text) Then
         MsgBox "欄位輸入必須為數值"
        End If
    End If
    Set y = Sheets("CNC名單(All1)")
Select Case num
Case num
    ComboBox1.Value =  y.Range("B2", y.[B2].End(xlDown)).Value
-------------------------------------------------------------------------
腦袋實在有點打結，這樣寫法有問題嗎?
拜託各位大大指點~~~
收藏 分享

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
2#
發表於 2016-6-2 22:32 | 只看該作者
本帖最後由 luhpro 於 2016-6-2 22:34 編輯

回復 1# bsy4life
基本上對於一對一對應且不會重複的情形,
用 Dictionary 是一種滿容易達到需求的方式 :



== Module==
  1. Public dPeo
複製代碼
== ThisWorkbook ==
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   
  4.   Set dPeo = CreateObject("Scripting.Dictionary")
  5.   lRow = 2
  6.   With Sheets("Sheet1")
  7.     While .Cells(lRow, 1) <> ""
  8.       dPeo(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
  9.       lRow = lRow + 1
  10.     Wend
  11.   End With
  12.   ufMain.Show
  13. End Sub
複製代碼
== UserForm ==
  1. Private Sub cbNo_Change()
  2.   lbName.Caption = dPeo(cbNo.Text)
  3. End Sub

  5. Private Sub UserForm_Initialize()
  6.   Dim vTmp
  7.   
  8.   For Each vTmp In dPeo
  9.     cbNo.AddItem vTmp
  10.   Next
  11.   cbNo.ListIndex = 0
  12. End Sub
複製代碼
時間因素這裡只能先簡易的表現 : 建立資料對應 與 查詢 實現的方式,
其他就請自己酌予修改套用囉...

工號索引.zip (10.49 KB)

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
3#
發表於 2016-6-3 08:19 | 只看該作者
本帖最後由 GBKEE 於 2016-6-3 08:22 編輯

回復 1# bsy4life
luhpro 提供的Dictionary 最簡便,也可用工作表函數 CountIf,Match 替代
  1. Private Sub TextBox6_Change()
  2.     Dim xNo As Variant, y As Range
  3.     Set y = Sheets("CNC名單(All1)").Range("a:a")
  4.     xNo = Application.CountIf(y, Val(TextBox6))  '傳回TextBox6相同的內容在y 的個數
  5.     Select Case xNo
  6.         Case 1   
  7.            'luhpro  上傳的工號索引.xls 名單內的儲存格有些是文字格式
  8.         'y.Value = y.Value   '轉為數字格式      
  9.             xNo = Application.Match(Val(TextBox6), y, 0)  '傳回TextBox6在y 的列號
  10.             ComboBox1 = y.Cells(xNo, "b")
  11.         Case 0
  12.             ComboBox1 = ""
  13.          Case Is > 1
  14.             ComboBox1 = "工號重複"
  15.     End Select
  16. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
4#
發表於 2016-6-3 09:43 | 只看該作者
回復 2# luhpro

謝謝 luhpro 大大
我的資質駑鈍，GBKEE大大的方式解決了我的問題
可是還是想瞭解一下，Dictionary的用法
可是我在置換後，執行程式發生
我才輸入一個數值
下面這行發生型態不符合
Label8.Caption = dPeo(TextBox6.Text)

module
  1. Public dPeo
  2. Sub 新增資料()
  3.     fcckeyin.Show
  4. End Sub
複製代碼
ThisWorkBook
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   Dim dPeo
  4.   
  5.   Set dPeo = CreateObject("Scripting.Dictionary")
  6.   lRow = 2
  7.   With Sheets("CNC名單(All)")
  8.     While .Cells(lRow, 1) <> ""
  9.       dPeo(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
  10.       lRow = lRow + 1
  11.     Wend
  12.   End With
  13.   fcckeyin.Show
  14. End Sub
複製代碼
userform
  1. Private Sub TextBox6_Change() '輸入工號讀取操作人
  2. TextBox6.IMEMode = fmIMEModeOff
  3.     If IsNumeric(TextBox6.Text) Then
  4.      Dim num As Integer
  5.      num = TextBox6.Text
  6.     Else
  7.         If Not IsNumeric(TextBox6.Text) Then
  8.          MsgBox "欄位輸入必須為數值"
  9.         End If
  10.     End If
  11.     Label8.Caption = dPeo(TextBox6.Text)  '本來使用combobox，不知道如何取代所以用label測試
  12. End Sub

  14. Private Sub fcckeyin_Initialize()
  15.   Dim vTmp
  16.   For Each vTmp In dPeo
  17.     TextBox6.Text = vTmp
  18.   Next
  19.   '本來此行是cbNo.ListIndex = 0 可是我是用textbox，所以此行拿掉
  20. End Sub
複製代碼
請問是不是我的寫法有誤?
謝謝各位大大~~
也謝謝GBKEE幫我解決問題唷~~!!

TOP

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
5#
發表於 2016-6-3 10:27 | 只看該作者
回復 3# GBKEE

不好意思，雖然問題解決了
但對這段程式碼還是沒有很理解
  1.   Dim xNo As Variant, y As Range  '這邊為什麼xNo要設定成 Variant類型呢?
  2.     Set y = Sheets("CNC名單(All1)").Range("a:a")  '這裡的a:a 不需要定義嗎? 那它代表的值為何?所有A行A列的資料嗎~~
  3.     xNo = Application.CountIf(y, Val(TextBox6))  '傳回TextBox6相同的內容在y 的個數<<這一段是要判斷我工號名單是否是唯一值嗎?
  4.     Select Case xNo
  5.         Case 1   
  6.            'luhpro  上傳的工號索引.xls 名單內的儲存格有些是文字格式  <我還不能載><
  7.         'y.Value = y.Value   '轉為數字格式       <這一行是什麼意思呢? 這有轉格式嗎?
  8.             xNo = Application.Match(Val(TextBox6), y, 0)  '傳回TextBox6在y 的列號  
  9.             ComboBox1 = y.Cells(xNo, "b")  
  10.         Case 0
  11.             ComboBox1 = ""
  12.          Case Is > 1
  13.             ComboBox1 = "工號重複"
複製代碼
麻煩GBKEE大大為我解說一下><
其實本來問題更多
然後才剛發現可以用excel的函數，實際測試完就比較清楚了
謝謝前輩~~

TOP

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
6#
發表於 2016-6-3 13:15 | 只看該作者
另外還有一個小問題...
我還做了一個載入清單的部分
因為有可能輸入者不是本人，所以不會知道工號
分開來用都是正常的
但只要先載入清單兩次以上
再去輸入工號查詢
ComboBox1 = y.Cells(xNo, "b")  
就會錯誤~~

  1. Private Sub CommandButton4_Click() '讀取操作人員
  2.         If OptionButton1.Value = True Then
  3.         Set x = Sheets("操作人員(早班)")
  4.         End If
  5.         If OptionButton2.Value = True Then
  6.         Set x = Sheets("操作人員(中班)")
  7.         End If
  8.         If OptionButton3.Value = True Then
  9.         Set x = Sheets("操作人員(晚班)")
  10.         End If
  11.         ComboBox1.List = x.Range("B2", x.[B2].End(xlDown)).Value
  12. End Sub
  13.                         'Private Sub CommandButton5_Click() '清除操作人員
  14.                         'TextBox6.SetFocus
  15.                         'ComboBox1.Value = ""
  16.                         'TextBox6.Text = ""
  17.                         'For j = ComboBox1.ListCount To 1 Step -1
  18.                         'Me.ComboBox1.RemoveItem j - 1
  19.                         'Next
  20.                         'End Sub
  21.                         
  22. Private Sub TextBox6_Change() '輸入工號讀取操作人
  23. TextBox6.IMEMode = fmIMEModeOff
  24.     If IsNumeric(TextBox6.Text) Then
  25.      Dim num As Integer
  26.      num = TextBox6.Text
  27.     Else
  28.         If Not IsNumeric(TextBox6.Text) Then
  29.          MsgBox "欄位輸入必須為數值"
  30.         End If
  31.     End If
  32.      Dim xNo As Variant, y As Range
  33.     Set y = Sheets("CNC名單(All)").Range("a:a")
  34.     xNo = Application.CountIf(y, Val(TextBox6))  '傳回TextBox6相同的內容在y 的個數
  35.     Select Case xNo
  36.         Case 1
  37.             xNo = Application.Match(Val(TextBox6), y, 0)  '傳回TextBox6在y 的列號
  38.             ComboBox1 = y.Cells(xNo, "b")   <<<偵測到此行
  39.         Case 0
  40.             ComboBox1 = ""
  41.          Case Is > 1
  42.             ComboBox1 = "工號重複"
  43.     End Select
  44. End Sub
複製代碼
我是不是把使用者想得太白目XD

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
7#
發表於 2016-6-4 05:22 | 只看該作者
回復 6# bsy4life

有錯誤,上傳檔案看看

Application.Match ,查無時傳回錯誤值,所以變數習慣 Type As Variant
這程式Application.Match 有搭配Application.CountIf 傳回的值,可避開錯誤值.
所以xNo可以不設定成 Variant類型.
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
8#
發表於 2016-6-4 08:00 | 只看該作者
回復 7# GBKEE

已附上檔案~~
生產實績效率運算0604.rar (116.23 KB)
謝謝前輩!!

TOP

luhpro

帖子
835 
主題
6 
精華
0 
積分
915 
點名
16  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-11-14 
9#
發表於 2016-6-5 00:39 | 只看該作者
本帖最後由 luhpro 於 2016-6-5 00:54 編輯
回復  luhpro
謝謝 luhpro 大大
我的資質駑鈍，GBKEE大大的方式解決了我的問題
可是還是想瞭解一下， ...
bsy4life 發表於 2016-6-3 09:43

個人看到的情形提出淺見供你參考:
1.
Module 上設定 Public dPeo 為 "全域變數", (整個檔案任何地方都是使用同一個物件或變數)
但你在 ThisWorkBook 又設了一個 Dim dPeo,
Dim 的 dPeo 則為 區域變數,
只有這個 Sub 看的到,
執行到 End Sub 後 -> 出去 Sub 就自動被消滅了,

而因與 Module 的 dPeo 變數名稱相同,
這個 Sub 中使用到的 dPeo 一律預設使用 Dim 出來的這個 "區域變數",
也就是這個 Sub 中的賦值作業結果在其他任一 Sub 完全無法使用)

你的 Label8.Caption = dPeo(TextBox6.Text) 會發生錯誤是因為 UserForm 這個 Sub 看到的 dPeo 是空的.
(可以在執行到此行發錯誤時點 "偵錯" 按鈕, 再於 "即時運算視窗" 下此指令:   debug.print dpeo.count     你會看到結果是 0 )
所以 ThisWorkBook 的 Dim dPeo 必須拿掉, 其他 Sub 才能正常使用到 ThisWorkBook 賦予 dPeo 的資料.

2. userform 中:
    If IsNumeric(TextBox6.Text) Then
....
    Else
        If Not IsNumeric(TextBox6.Text) Then  <- 這一行是多餘的 If  X  Then ... Else 指令的 Else 就是 當 Not X 的條件為 "真" 時執行.
...
        End If     <-  搭配 IF 的指令, 當 IF 拿掉時也要跟著拿掉

    End If
    Label8.Caption = dPeo(TextBox6.Text)  '本來使用combobox，不知道如何取代所以用label測試
'不知為何要用 combobox ?
當使用者在 TextBox 輸入內容後,
這裡應該只有唯一的值(用 Label 物件就可以了),
而不是有好幾個值的清單物件.
除非你想要讓使用者接著可以再在 combobox 點選 操作人 以再次變更 TextBox 的內容.
註 : 若有此需求, 可以用 combobox.AddItem ... 以增加清單項目
End Sub

最後... 8# 的檔案有設密碼, 程式看不到...

額外建議 :
CNC名單(All) 如果應該是包含全部  操作人員(早, 中, 晚班) 的話,
可以考慮用 Dictionary 在開檔時在三個 Sheet 分別抓取,
然後在 CNC名單(All) 上動態建立清單.

TOP

bsy4life

帖子
22 
主題
2 
精華
0 
積分
19 
點名
0  
作業系統
Win7 
軟體版本
2007 
閱讀權限
10 
註冊時間
2016-6-2 
最後登錄
2016-6-22 
10#
發表於 2016-6-6 10:40 | 只看該作者
回復 9# luhpro

不知為何要用 combobox ?
當使用者在 TextBox 輸入內容後,
這裡應該只有唯一的值(用 Label 物件就可以了),
而不是有好幾個值的清單物件.
除非你想要讓使用者接著可以再在 combobox 點選 操作人 以再次變更 TextBox 的內容.
註 : 若有此需求, 可以用 combobox.AddItem ... 以增加清單項目


因為現在還不確定程式會到現場供操作人使用
還是給辦公室的內勤人員使用
如果是給現場的使用，那輸入工號會是最快的方式
如果是內勤人員，也不太可能把每一個人的工號熟記
所以為了方便他們靈活運用，才設計成工號查詢，跟讀取清單兩種模式\
應該還會再把它變成掃條碼可以直接讀取的方式
還有還有
密碼是1215，抱歉忘了解開@@


謝謝luhpro 大大耐心的解答~~
你的建議我會試試看!!

TOP

        靜思自在 : 站在半路，比走到目標更辛苦。
返回列表 上一主題