>即時新聞-熱門

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/02/15

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/

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 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 數學整數

說明 : 大小寫有規定

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 鈕 / 再設參數

 
妹咕數位學園歡迎網友們來信指教 妹咕信箱