=IF(A2="Davolio","OK","Not OK")
檢查A2儲存格的內容是否包含「Davolio」，若是則顯示OK，否則顯示Not OK
計算欄位數量
Cells(5, "J") = Range("C2").End(xlDown).Row
在J5儲存格顯示C欄位共有幾筆資料
VBA UserForm TextBox 按Tab跳換的順序可由
「性質分類」-->「其他」-->「TabIndex」後輸入數字排序
VBA使用選項按鈕的程式碼：
If Me.OptionButton1.Value = True Then
Cells(r, "E") = "先生"
ElseIf Me.OptionButton2.Value = True Then
Cells(r, "E") = "女士"
End If
首先把兩個選項按鈕框選起來並在「字母順序」-->「GroupName」設定群組名稱
若r=2，選取一號選項時將在E2儲存格輸入先生，選取二號選項時將輸入女士
Cells(r, "A") = Range("C1").End(xlDown).Row - 1
輸入資料時在A欄位輸入編號，計算結果為C欄位資料筆數(包含頂端列的名稱分類)-1
---
刪除資料
Private Sub CommandButton7_Click()
If Range("C2") = "" Then
r = 2
Else
r = Range("C1").End(xlDown).Row
End If
Cells(r, "C").Select
Rows(r).Columns("A:I").Select
Selection.ClearContents
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
r = r - 1
Cells(r, "C").Select
End Sub
會刪除最後一筆資料，但若C欄最後一格沒資料不知道會怎麼樣
有設定格式化條件重複的內容儲存格會紅底，因此若輸入了一筆沒有名字的資料似乎會判斷格式化那格有內容，因此判定最後一筆(?
----
儲存格字體紅色、藍色
Cells(r, "A").Select
With Selection.Font
.Color = -16776961 '紅色
.TintAndShade = 0
End With
Cells(r, "A").Select
With Selection.Font
.Color = -4165632 '藍色
.TintAndShade = 0
End With
----
備註的選項與輸入欄位輸出
If Me.OptionButton3.Value = True Then
Cells(r, "H") = "已面交 " + Me.TextBox4
ElseIf Me.OptionButton4.Value = True Then
Cells(r, "H") = "已郵寄 " + Me.TextBox4
ElseIf Me.OptionButton5.Value = True Then
Cells(r, "H") = "待面交 " + Me.TextBox4
ElseIf Me.OptionButton6.Value = True Then
Cells(r, "H") = "待面交 " + Me.TextBox4
End If
----
TERA補量計算檔案
Public Sub 清除()
Application.ScreenUpdating = False
Cells(6, "B") = "選擇祭司武器"
Cells(31, "B") = "選擇元素使武器"
Range("B7,B32").Value = "選擇強化數值"
Range("B8,B33").Value = "選擇武器恢復效果"
Range("B9,B34").Value = "選擇+攻速%"
Range("B10,B35").Value = "選擇-CD%"
Range("B11,B36").Value = "選擇武器刻印"
Range("B12,B37").Value = "選擇手套恢復效果"
Range("B13,B38").Value = "選擇+攻速%"
Range("B14,B39").Value = "選擇手套刻印"
Range("B16,B41").Value = "選擇項鍊"
Range("B17,B18,B42,B43").Value = "選擇(恢復)戒指"
Range("B19,B44").Value = "選擇套裝"
For i = 21 To 24
Cells(i, "B") = "選擇水晶"
Next
For i = 46 To 49
Cells(i, "B") = "選擇水晶"
Next
Range("B26,B51").Value = "選擇恢復效果"
Range("B27,B52").Value = "選擇使用"
Range("D6,D31").Value = "關閉綠鈦彈"
For i = 9 To 13
Cells(i, "F") = "選擇紋章"
Next
For i = 19 To 25
Cells(i, "F") = "選擇紋章"
Next
For i = 37 To 48
Cells(i, "F") = "選擇紋章"
Next
Application.ScreenUpdating = True
End Sub
-----
設定好資料的個人按鈕
Public Sub 係金ㄟ()
Application.ScreenUpdating = False
Call 清除
Cells(6, "B") = "鐵匠卡拉希勒權杖"
Cells(31, "B") = "凱利班魔棒"
Cells(11, "B") = "女神職權II"
Cells(19, "B") = "5套裝"
Cells(44, "B") = "3+2套裝"
Range("B12,B37").Value = "10.5%"
Range("B8,B33").Value = "18%"
Range("B9,B34").Value = "18%"
Range("B10,B35").Value = "14.4%"
Range("B13,B38").Value = "4.5%"
Range("B14").Value = "強力的野獸速擊II"
Range("B16").Value = "凱利班的項鍊：恢復"
Range("B17,B18").Value = "凱利班戒指"
Cells(42, "B") = "扭曲的時空戒指"
Range("B39").Value = "選擇手套刻印"
Range("B41").Value = "時空項鍊：恢復"
For i = 21 To 24
Cells(i, "B") = "治癒之手II"
Next
For i = 46 To 49
Cells(i, "B") = "快速逆流II"
Next
Cells(10, "F") = "HP恢復量增加20%"
Cells(11, "F") = "治癒祈禱XII-HP恢復量增加20%"
Cells(12, "F") = "治癒之光IX-HP恢復量增加15%"
Cells(20, "F") = "冷卻時間減少25%"
Cells(22, "F") = "冷卻時間減少60%"
Range("F25,F43").Value = "冷卻時間減少40%"
Range("F37,F38,F41").Value = "冷卻時間減少20%"
Range("F39").Value = "冷卻時間減少50%"
Application.ScreenUpdating = True
End Sub
------
製作下拉式選單
選取資料驗證-->清單-->選取清單欄位
使用VLOOKUP回傳選單欄位對應的資料、值
=VLOOKUP(選單儲存格,對照的資料欄位(最少兩欄),2(回傳第二欄的資料),FALSE(完全相符才回傳)
沒有留言:
張貼留言