1樓：匿名使用者
此vba比1樓效率高，呵呵。不用跑65536次，有多少個號就跑多少次。超出65536的也可以。
sub 限號()
i = 2
do while cells(i, 1) <> ""
車牌 = cells(i, 1)
尾號 = right(車牌, 1)
if worksheetfunction.isnumber(尾號) then
尾號 = mid(車牌, len(車牌) - 1, 1)end if
select case 尾號
case 1, 9, 0
cells(i, 2) = "週一"
case 2, 8, 5
cells(i, 2) = "週二"
case 3, 7
cells(i, 2) = "週三"
case 4, 6
cells(i, 2) = "周四"
end select
i = i + 1
loop
end sub
2樓：
不用vba
請在b2中輸入公式：=choose(if(or(right(a2,1)>"9",right(a2,1)<"0"),left(right(a2,2),1),right(a2,1))+1,"週一","週一","週二","週三","周四","週二","周四","週三","週二","週一")
向下複製填充即可
當然，用vba也不是不可以，但有點殺雞用牛刀的感覺
sub restrict_number()
dim car_license as range, last_num, weekday
for each car_license in range(range("a2"), range("a2").end(xldown))
last_num = right(car_license, 1)
if last_num < "0" or last_num > "9" then last_num = left(right(car_license, 2), 1)
select case last_num
case 1, 9, 0
weekday = "週一"
case 2, 8, 5
weekday = "週二"
case 3, 7
weekday = "週三"
case 4, 6
weekday = "周四"
case else
weekday = "不限"
end select
car_license.offset(0, 1) = weekday
next
end sub
3樓：余余
公式法：
b2公式下拉即可！
="週一","一","二","三","四","二","四","三","二","一")
vba方法，**私信給你了！
請注意查收~
把郵箱告訴我，重新寫乙個給你~change事件！
4樓：不可不衰
用函式就可以了
b2=choose(min(if(iserror(find(if(isnumber(right(a2)*1),right(a2)*1,mid(a2,len(a2)-1,1)*1),)),100,column($a:$d))),"週一","週二","週三","周四")
陣列函式 ctrl+shift+enter確認再來個vba的自定義函式吧 b2=aa(a2) 下拉即可function aa(a as range)dim s, b as range
for each b in a
s = right(b, 1)
if not isnumeric(s) then s = left(right(b, 2), 1)
select case s
case 1, 9, 0
aa = "週一"
case 2, 8, 5
aa = "週二"
case 3, 7
aa = "週三"
case 4, 6
aa = "周四"
end select
next
end function
5樓：匿名使用者
b2輸入公式
=text(int(find(mod(-lookup(,-left(right(a2,2),row($1:$2))),10),"xx19028537x46")/3),"周0[dbnum1]")
下拉公式
如何使用VBA進行公式自動填充，excel利用VBA自動填充公式
alt f11開啟vbe 雙擊你的工作表，在右邊貼入 private sub worksheet change byval target as range dim n n target.row if target.column 1 then if vba.isnumeric target.value...
EXCEL中用VBA提取符合條件的數
寫好了，測試通過了 option explicit sub dim arr,arr1,arr2,arr3 dim i,j arr1 array 1,0,3,6,9 arr2 array 1,4,7 arr3 array 2,5,8 i j65536 end xlup row j i while i ...
我想請教各位高手有關EXCEL借助VBA與入ACCESS資料庫的問題
可以使用巨集來實現，給你乙個簡單的例子。sub adotest dim adort as object dim strsql as string 建立ado記錄集物件 set adort createobject adodb.recordset 設定sql 語句 strsql select from...