offset (n,m) n表上下移動，n負值向下；m表左右移動，m負值向左
cells(i,j) i 表row; j 表column
Sub IsWorkBookOpen() 'Written by www.ozgrid.com
'Test to see if a Workbook is open.
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("Personal.xls")
If wBook Is Nothing Then 'Not open
MsgBox "Workbook is not open", _
vbCritical,"OzGrid.com"
Set wBook = Nothing
On Error GoTo 0
Else 'It is open
MsgBox "Yes it is open", _
vbInformation,"OzGrid.com"
Set wBook = Nothing
On Error GoTo 0
End If
End SubSub DoesWorkBookExist() 'Written by www.Ozgrid.com Test to see if a Workbook exists
Dim i As Integer
With Application.FileSearch
.LookIn = "C:\MyDocuments"
'* represents wildcard characters
.FileName = "Book*.xls"
If .Execute > 0 Then 'Workbook exists
MsgBox "There is a Workbook."
Else 'There is NOt a Workbook
MsgBox "The Workbook does not exist"
End If
End With
End SubSub A_Sample004()
Dim myRng As Range
Dim mySht As Worksheet
Set mySht = Worksheets(2) '任意的表
'準備到此為止
Set myRng = mySht.UsedRange
'MsgBox myRng.Address
'Range("a1").CurrentRegion.Select '選取工作表有資料所有儲存格
Selection.Borders.LineStyle = xlContinuous '為所有儲存格畫格線
myRng = [b2].CurrentRegion.Select
Range("b8").Select
Columns("B").Select '選取 B 欄
Selection.NumberFormatLocal = "0.0" '設定選取範圍的數字格式
Columns("c").NumberFormatLocal = "0.00" '設定C欄的數字格式
Set myRng = Nothing '物件的釋放
Set mySht = Nothing
Set RngHead = Range("A2") '設定第一筆資料從A2開始
DataCunt = Range("A65536").End(xlUp).Row '由A欄的第65536儲存格往上跳至最後有資料的列號 例如跳至第 20 列, 而標題在第一列, 所以可判斷有 19 列資料
Application.ScreenUpdating = False '關閉螢幕在程式執行時間內的變化, 直至程式完成顯示結果為止, 這個方法可讓龐大資料的處理時間縮短
For i = RngHead.Row To DataCunt '讓 i 從資料首列的列號跑到資料總列數
Range("A" & i).NumberFormatLocal = "G/通用格式"
Range("A" & i).Value = Val(Range("A" & i).Value) '(A欄)將儲存格設為〔通用格式〕，再將文字轉為數值，注意：如果資料有前導０，例如 0198，會變成 198
Range("F" & i).NumberFormatLocal = "G/通用格式"
Range("F" & i).Value = Val(Range("F" & i).Value)
'也可自訂格式，例如：0.00 或 #,##0.00， 以本例的資料，可設定為：+0.00;[紅色]-0.00
'(F欄)將儲存格設為〔通用格式〕，再將文字轉為數值，
Range("G" & i).NumberFormatLocal = "G/通用格式"
Range("G" & i).Value = Val(Range("G" & i).Value) '(G欄) → 與Ｆ欄同
Next i
'Set myRng = Columns("a")
'myRng = [A1].CurrentRegion.Select
'ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
Set myRng = ActiveSheet.Range("a1").End(xlDown).Offset(0, 0) '設定連續儲存格範圍下面的儲存格
'ActiveSheet.Range("a1").End(xlDown).Select '選取連續欄中的最後一個儲存格
'Set myRange = ThisWorkbook.Sheets(1).Range("IV3").End(xlToLeft) '表示第三列的最後一欄
'Set myRng = Range([A1], [A65536].End(xlUp)) '設定連續儲存格範圍的儲存格--有資料的區域
'Set myRng1 = Range("A1")
'Set myRng = Range("A1:A3")
'For Each cell In Range([A1], [A65536].End(xlUp))
'For Each cell In myRng
'cell.Value = "KK" & cell.Value
'cell.Value = "kk" & cell.Value
'cell.Value = Mid(cell.Value, 6, 3) '自第6字元起
'cell.NumberFormatLocal = "G/通用格式"
'cell.NumberFormatLocal = "@" '設定為文字格式，才不會自動轉為日期格式
'Range("A" & i).NumberFormatLocal = "G/通用格式"
'cell.Value = Replace(cell.Value, "/", "-", 1, 1, 1)
'cell.Value = Replace(cell.Value, "kk", "", 1, 1, 1)
'MsgBox "***" & myRng.Address
'Debug.Print "***" & myRng.Address '沒印出東東 要在即時運算視窗才看得到
'Debug.Print "***" & cell.Value
'Next
myRng.Cells.NumberFormatLocal = "@"
mycol = myRng.Column
myrow = myRng.Row
mycell1 = Range("a" & Str(myrow))
mycell2 = Range("a" & Str(myrow + 3))
MsgBox mycol & "***" & mycell1
'ActiveSheet.Range("b2").AutoFill Range(Cells(10, 2), Cells(13, 2))
myRng.Select
Selection.AutoFill Destination:=Range(Cells(51, 1), Cells(60, 1)) 'OK的
'Selection.AutoFill Destination:=Range(mycell1:mycell2) ' not ok
'ActiveSheet.Range("a1").End(xlDown).AutoFill Range(Cells(1, 1), Cells(13, 1)) 'range 出問題
'With myRange1
' MsgBox .Value
'End With
End Sub
留言列表
