大家好歡迎來發言
>即時新聞-熱門
2011年3月9日星期三
2011年2月25日星期五
EXCEL VBA 資料驗證不限筆數
Sub 驗證()
Dim row_s, row_e, col_s, col_e
row_s = ActiveCell.Row
row_e = ActiveCell.SpecialCells(xlLastCell).Row
col_s = ActiveCell.Column
col_e = ActiveCell.SpecialCells(xlLastCell).Column
For i = 0 To row_e - row_s - 1
For j = 0 To col_e - col_s
If ActiveCell.Value > 0 And ActiveCell.Value < 100 Then
Else
錯誤
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(0, -j).Select
ActiveCell.Offset(1, 0).Select
Next
End Sub
Sub 錯誤()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
End Sub
Sub hjju()
'
' hjju Macro
' Teacher 在 2011/2/26 錄製的巨集
'
'
Range("C3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub
excel vba 任意檔案分類
Public myi, myname, myfield
Sub 執行()
myfield = InputBox("請輸入欄號", "中華工程顧問")
排序
分類
For myi = 1 To Sheets.Count - 1
myname = Sheets(myi).name
篩選
資料移轉
排序
Next
還原排序
End Sub
Sub 分類()
Dim newname
my_n = myfield & "2"
Range(my_n).Select
While ActiveCell.Value <> Empty
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
newname = ActiveCell.Value
Sheets.Add
Sheets(Sheets.Count - 1).name = newname
Sheets(Sheets.Count).Select
ActiveCell.Offset(1, 0).Select
End If
Wend
End Sub
Sub 篩選()
Sheets(Sheets.Count).Select
my_n = myfield & "2"
Range(my_n).Select
new_col = ActiveCell.Column
Selection.AutoFilter
Selection.AutoFilter Field:=new_col, Criteria1:=Sheets(myi).name
End Sub
Sub 排序()
my_n = myfield & "1"
Sheets(Sheets.Count).Select
Range("A1:I2500").Sort Key1:=Range(my_n), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
Sub 資料移轉()
複製
開新檔案
貼上
儲存
還原篩選
End Sub
Sub 複製()
Range("a1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
End Sub
Sub 開新檔案()
Workbooks.Add
End Sub
Sub 貼上()
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub 儲存()
ActiveWorkbook.SaveAs Filename:= _
"C:\new\" & myname & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub
Sub 還原篩選()
Range("A1").Select
Sheets(Sheets.Count).Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
Sub 還原排序()
Sheets(Sheets.Count).Select
Range("A1").Select
Range("A1:I2500").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
EXCEL VBA 檔案分類
Public myi, myname
Sub 執行()
排序
分類
For myi = 1 To Sheets.Count - 1
myname = Sheets(myi).name
篩選
資料移轉
排序
Next
還原排序
End Sub
Sub 分類()
Dim newname
Range("C2").Select
While ActiveCell.Value <> Empty
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
newname = ActiveCell.Value
Sheets.Add
Sheets(Sheets.Count - 1).name = newname
Sheets(Sheets.Count).Select
ActiveCell.Offset(1, 0).Select
End If
Wend
End Sub
Sub 篩選()
Sheets(Sheets.Count).Select
Range("C2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=Sheets(myi).name
End Sub
Sub 排序()
Sheets(Sheets.Count).Select
Range("A1:I2500").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
Sub 資料移轉()
複製
開新檔案
貼上
儲存
還原篩選
End Sub
Sub 複製()
Range("a1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
End Sub
Sub 開新檔案()
Workbooks.Add
End Sub
Sub 貼上()
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub 儲存()
ActiveWorkbook.SaveAs Filename:= _
"C:\new\" & myname & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub
Sub 還原篩選()
Range("A1").Select
Sheets(Sheets.Count).Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
Sub 還原排序()
Sheets(Sheets.Count).Select
Range("A1").Select
Range("A1:I2500").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
EXCEL VBA 資料分類
Public myi
Sub 執行()
排序
分類
For myi = 1 To Sheets.Count - 1
篩選
資料移轉
排序
Next
還原排序
End Sub
Sub 分類()
Dim newname
Range("C2").Select
While ActiveCell.Value <> Empty
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
newname = ActiveCell.Value
Sheets.Add
Sheets(Sheets.Count - 1).name = newname
Sheets(Sheets.Count).Select
ActiveCell.Offset(1, 0).Select
End If
Wend
End Sub
Sub 篩選()
Sheets(Sheets.Count).Select
Range("C2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=Sheets(myi).name
End Sub
Sub 排序()
Sheets(Sheets.Count).Select
Range("A1:I2500").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
Sub 資料移轉()
Range("a1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets(myi).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets(Sheets.Count).Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
Sub 還原排序()
Sheets(Sheets.Count).Select
Range("A1").Select
Range("A1:I2500").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke, DataOption1:=xlSortNormal
End Sub
2011年2月24日星期四
EXCEL VBA 合併工作表
Sub 整合資料()
新工作表
轉移
End Sub
Sub 新工作表()
Sheets(1).Select
Range("a1").Select
Sheets.Add
End Sub
Sub 轉移()
Dim i
For i = 2 To Sheets.Count
Sheets(i).Select
If i = 2 Then
Range("a1").Select
Else
Range("a2").Select
End If
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets(1).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Next
Range("a1").Select
End Sub
EXCEL VBA 自動流水號
Sub 流水號()
Dim mystart, myend, myrow, myrow1
myrow = ActiveCell.Row
ActiveCell.Offset(0, 1).Select
myrow1 = Selection.End(xlDown).Row
myend = myrow1 - myrow + 1
ActiveCell.Offset(0, -1).Select
mystart = ActiveCell.Value
If mystart <> Empty Then
ActiveCell.Value = mystart
myend = myend - 1
Else
ActiveCell.Value = 1
mystart = 0
End If
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=myend + mystart, Trend:=False
End Sub
2011年2月23日星期三
請大家多多指教 我寫的VBA書
EXCEL VBA 書 主要是教大家用錄的巨集 加修改程式
很好用喔 請大家多多指教
看! 就是比你早下班: 50個Excel VBA高手問題解決法
作者 : 楊玉文
出版社 :松崗電腦圖書資料股份有限公司
2011年1月20日星期四
EXCEL _VBA(檔案列表)
Sub 檔案列表()
Dim myPath As String
Dim myFileName As String
Dim i As Long
myPath = ThisWorkbook.Path & "\"
myFileName = Dir(myPath, 0)
i = 1
Do While Len(myFileName) > 0
Cells(i, 1) = myFileName
myFileName = Dir()
i = i + 1
Loop
k = 1
For j = 1 To i
If Right(Cells(j, 1), 3) = "xls" Then
Cells(k, 2) = Cells(j, 1)
k = k + 1
End If
Next
Columns(1).Delete
End Sub
Sub 指定檔案()
Dim myFs As FileSearch
Dim myPath As String
Dim myClc As New Collection
Dim i As Long
Set myFs = Application.FileSearch
myPath = ThisWorkbook.Path
With myFs
.NewSearch
.LookIn = myPath
.FileType = msoFileTypeAllFiles
.Filename = "*.xls"
.SearchSubFolders = True
If .Execute(SortBy:=msoSortByFileName) > 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like myPath & "*" Then
On Error Resume Next
myClc.Add .FoundFiles(i), .FoundFiles(i)
On Error GoTo 0
End If
Next
End If
End With
Set myFs = Nothing
With myClc
If .Count > 0 Then
For i = 1 To .Count
Cells(i, 1) = Right(.Item(i), Len(.Item(i)) - Len(myPath) - 1)
Next
Else
End If
End With
Set myClc = Nothing
End Sub
2011年1月17日星期一
網頁設計常用工具
測試網頁區
測試網頁載入時間 - webwait.com
網頁按鈕產生器 - http://www.buttonator.com/
CSS 網頁按鈕 : http://www.pagetutor.com/button_designer/index.html
網頁按鈕 : http://dabuttonfactory.com/
網頁配色工具 : http://0to255.com/
網頁配色 : http://www.colorotate.org/
标签: DREAMWEAVER
EXCEL VBA 工作表重新命名
以下用於更新工作表2010為2011的命名
Sub 重新命名()
For i = 1 To Sheets.Count
x = Sheets(i).Name
s = Len(x)
x1 = Left(x, 3) & "1" & Right(x, s - 4)
Sheets(i).Name = x1
Next
End Sub
2010年12月13日星期一
EXCEL _ VBA資料拆解
Sub 資料比對()
Dim my_rng, my_end
Sheets.Add
Workbooks.Open Filename:="T2 Digit Code.xls"
my_end = ActiveCell.SpecialCells(xlLastCell).Row
my_rng = "C4:C" & my_end
Range(my_rng).Select
Selection.Copy
Windows("FW11 2nd CUST FCST - 021210.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("T2 Digit Code.xls").Close
刪除空白
End Sub
Sub 刪除空白()
Dim my_start, my_end
my_end = ActiveCell.SpecialCells(xlLastCell).Row
For i = 1 To my_end
If ActiveCell.Value <> Empty Then
ActiveCell.Offset(1, 0).Select
Else
Selection.Delete Shift:=xlUp
End If
Next
Range("A1").Select
End Sub
Sub 合併篩選()
Dim my_rng, i, y
Sheets(1).Select
y = ActiveCell.SpecialCells(xlLastCell).Row
For i = 1 To y
my_rng = "A" & i
' 資料移轉
Sheets(Sheets.Count).Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=Sheets(1).Range(my_rng).Value
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
'新活頁簿
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
If Range("A2").Value <> Empty Then
'儲存活頁簿
x = Range("c2").Value
ActiveWorkbook.SaveAs Filename:= _
x & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
'關閉活頁簿
ActiveWindow.Close
Else
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
' 結束篩選
Sheets(Sheets.Count).Select
Range("A1").Select
Selection.AutoFilter
Next
End Sub
2010年12月9日星期四
after effect 形狀 express 位置 , 大小 旋轉
表達式設計(形狀設計位置 大小 旋轉)
1 File (功能表)/ new(新增) / project(專案)
2 Composition(合成) / new Composition(新增合成)
3設定尺寸 , 影格 , 規格 / 確定 ( ntsc 320x 240 , 00:00:8:00)
4 layer (功能表) / new (新增) / Shape (形狀)
5 選取時間軸 / ADD (polystar )/ ADD(FILL,RGB) / 選取postion
6點選 ANIMATION(動畫) / ADD EXPRESSION(表示式) , 快速鍵 : SHIFT + ALT + =
輸入position表示式的內容
my_hold = .01; //最小停留時間
min_value = [0.01*thisComp.width, 0.01*thisComp.height];
max_value = [thisComp.width, thisComp.height];
seedRandom(index,true);
my_dur = random(my_hold, 1);
my_seed = Math.floor(time/my_dur);
my_start = my_seed*my_dur;
seedRandom(my_seed,true);
start_value = random(min_value,max_value);
seedRandom(my_seed,true);
end_value = random(min_value,max_value);
ease(time,my_start,my_start + my_dur, start_value, end_value);
5 選取Shape (形狀圖形) / CTRL + D / 改圖形色彩(可以複製N個)
也可以改為
輸入Scale表示式的內容
my_hold = .01; //最小停留時間
min_value = [0.01*thisComp.width, 0.01*thisComp.height];
max_value = [0.2*thisComp.width, 0.2*thisComp.height];
seedRandom(index,true);
my_dur = random(my_hold, 1);
my_seed = Math.floor(time/my_dur);
my_start = my_seed*my_dur;
seedRandom(my_seed,true);
start_value = random(min_value,max_value);
seedRandom(my_seed,true);
end_value = random(min_value,max_value);
ease(time,my_start,my_start + my_dur, start_value, end_value);
輸入roation的表示式內容
my_count = 0; //次數計算宣告
my_value = 0; //旋轉值宣告
seedRandom(index,true)
while (my_count < time){
my_value = my_value+ random(30);
my_count = my_count + thisComp.frameDuration;
}
rotation + my_value
标签: AFTER EFFECTS
after effect 定位 express
表達式設計(定位)
1 File (功能表)/ new(新增) / project(專案)
2 Composition(合成) / new Composition(新增合成)
3設定尺寸 , 影格 , 規格 / 確定 ( ntsc 320x 240 , 00:00:8:00)
4 File (功能表) / import (匯入) / file (影片檔案)
5 取您需求的檔案格式 / open (開啟) / 快速鍵CTRL + I(檔案尺寸320*240)
6 將影片拖曳至時間軸 / 按 P 鍵(設定POSITION位置)
7 點選 ANIMATION(動畫) / ADD EXPRESSION(表示式) , 快速鍵 : SHIFT + ALT + =
8 輸入表示式的內容 : [(thisComp.width/2),(thisComp.height/2)]+[Math.sin(time)*80,Math.cos(time)*30]
Width : 寬度 height : 高度 math : 函數 sin:正弦 cos : 餘弦
也可以這樣寫
x = random(thisComp.width);
y = random(thisComp.height);
[x,y]
或是這樣寫
x = random()*thisComp.width;
y = random()*thisComp.height;
[x,y]
說明 : 大小寫有規定
表達式設計一(亂數定位)
1 File (功能表)/ new(新增) / project(專案)
2 Composition(合成) / new Composition(新增合成)
3設定尺寸 , 影格 , 規格 / 確定 ( ntsc 320x 240 , 00:00:8:00)
4 File (功能表) / import (匯入) / file (影片檔案)
5 取您需求的檔案格式 / open (開啟) / 快速鍵CTRL + I(檔案尺寸320*240)
6 將影片拖曳至時間軸 / 按 P 鍵(設定POSITION位置)
7 點選 ANIMATION(動畫) / ADD EXPRESSION(表示式) , 快速鍵 : SHIFT + ALT + =
8 輸入表示式的內容 :
holdTime = 1; // 停留位置時間
seed = Math.floor(time/holdTime);
seedRandom(seed,true);
random([thisComp.width, thisComp.height])
seedRandom() : 隨機函數 random(0,720)系統亂數 Math.floor 數學整數
說明 : 大小寫有規定
标签: AFTER EFFECTS
2010年12月7日星期二
aftereffect 文字動畫
文字預設動畫設計
1 File (功能表)/ new(新增) / project(專案)
2 File (功能表) / import (匯入) / files (二個圖檔案)
3 取您需求的檔案格式 / open (開啟) / done (結束)
4 Composition(合成) / new Composition(新增合成)
5設定尺寸 , 影格 , 規格 / 確定 ( ntsc 640 x 480 , 00:00:05:00) / 拖曳素材至timeline(時間軸)
6 選取LAYER (圖層) /NEW / TEXT / 輸入文字內容 / OK
7 選取右側 effect & preset / 選取animation presets / 選取 TEXT / 選取FILL& STROKE / 套用 pluse blue / 拖曳到時間軸
8 選取右側 effect & preset / 選取animation presets / 選取 TEXT / 選取miscellaneous(雜項)/ 套用 squeeze (擠) / 拖曳到時間軸
3D文字動畫設計
1 File (功能表)/ new(新增) / project(專案)
2 Composition(合成) / new Composition(新增合成)
3設定尺寸 , 影格 , 規格 / 確定 ( ntsc 640 x 480 , 00:00:05:00) / 拖曳素材至timeline(時間軸)
4 選取上圖層 / effect / generate / ramp(漸層色) / effect / generate / GRID(網格) / 設定size from = slider , mode = overlay(覆蓋)
5 按一下3D鈕 / 設定3D參數
6 layer / NEW / TEXT / 輸入文字內容 / OK
7 選取3D 鈕 / 再設參數
标签: AFTER EFFECTS


