[問題] VBA 從第二列,到最後一列
軟體: M$ OFFICE EXCEL
版本: 2010
請教一下,
原本我是寫這樣子
sheet.Range("A65536").End(xlUp)
那這樣子的寫法會從第一列找到最後一列
可是我第一列想要放標題
所以我改寫成這個樣子
sheet.Range("A2:A" & sheet.Range("A65536").End(xlUp))
但是這樣子的寫法執行起來好像怪怪的...冏
想請教前輩們,如果要從第二列開始執行,應該要怎麼寫呢?
謝謝指教了
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 59.120.19.22
※ 文章網址: http://www.ptt.cc/bbs/Office/M.1411369674.A.849.html
→
09/22 16:05, , 1F
09/22 16:05, 1F
→
09/22 16:34, , 2F
09/22 16:34, 2F
Set myRange = sheet.Range([A2], [A65536].End(xlUp))
我是這樣子寫,可是他說失敗T_T
※ 編輯: bernachom (59.120.19.22), 09/22/2014 16:35:48
→
09/22 16:44, , 3F
09/22 16:44, 3F
沒有耶@@
我只是要做從第一個SHEET的第二列到最後一列尋找這樣子而已
sheet.Range("A65536").End(xlUp)
因為上面這樣子寫他會從頭找到尾
可是我要從第二列開始,就卡關了..T_T
※ 編輯: bernachom (59.120.19.22), 09/22/2014 16:47:07
→
09/22 16:47, , 4F
09/22 16:47, 4F
→
09/22 16:48, , 5F
09/22 16:48, 5F
→
09/22 16:48, , 6F
09/22 16:48, 6F
我好像看懂你的意思了@@
我會去開啟特定的excel 裡面的sheet
所以你說的是對的,我會去指定某個sheet的
第二列到最後一列@@
※ 編輯: bernachom (59.120.19.22), 09/22/2014 16:52:18
→
09/22 16:51, , 7F
09/22 16:51, 7F
→
09/22 16:52, , 8F
09/22 16:52, 8F
→
09/22 16:58, , 9F
09/22 16:58, 9F
Option Base 0
Sub LoopThroughSheets()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("D:\REPLACE.xlsx") ' extension
Dim sheet As Excel.Worksheet
Set sheet = xlBook.Worksheets(1)
Dim ws As Worksheet
For Each ws In Sheets
Dim myRange As Range
Dim myReplace As Range
Set myRange = Range([A2], [A65536].End(xlUp)) '前面加SHEET會ERROR
Set myReplace = Range([B2], [B65536].End(xlUp)) '前面加SHEET會ERROR
myRange.Select
myReplace.Select
For RangeIndex = 1 To myRange.Row Step 1
' MsgBox (sheet.Cells(RangeIndex, 1))
ReplaceText sheet.Cells(RangeIndex, 1), sheet.Cells(RangeIndex, 2), ws
Next RangeIndex
Set myRange = Nothing
Next
MsgBox ("取代完成")
xlApp.Workbooks.Close
End Sub
Function ReplaceText(src As String, Rpl As String, sht As Worksheet)
sht.Cells.Replace What:=src, Replacement:=Rpl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Function
※ 編輯: bernachom (59.120.19.22), 09/22/2014 16:59:30
→
09/22 16:59, , 10F
09/22 16:59, 10F
→
09/22 17:00, , 11F
09/22 17:00, 11F
不好意思,請教一下
Set myRange = Range(Sheets("Sheet1").[A2],
Sheets("Sheet1").[A65536].End(xlUp))
我先試著寫這一行,結果他說陣列索引超出範圍,想請您請教一下T_T
謝謝了.
※ 編輯: bernachom (59.120.19.22), 09/22/2014 17:08:32
→
09/22 17:21, , 12F
09/22 17:21, 12F
→
09/22 17:26, , 13F
09/22 17:26, 13F
可是感覺就是怪怪的@@
好像變成他會找A1 A2 然後A3就不會了@@
我再試試看..
※ 編輯: bernachom (59.120.19.22), 09/22/2014 17:29:34
→
09/22 17:31, , 14F
09/22 17:31, 14F
有點不太明白,
因為我寫成這個樣子
Set myRange = sheet.Range("A2:A" & sheet.[A65536].End(xlUp).Row)
Set myReplace = sheet.Range("B2:B" & sheet.[B65536].End(xlUp).Row)
他應該會從第二列開始找,可是他還是一樣會去找第一列T_T
※ 編輯: bernachom (59.120.19.22), 09/22/2014 17:38:27
→
09/22 18:10, , 15F
09/22 18:10, 15F
→
09/22 18:11, , 16F
09/22 18:11, 16F
→
09/22 18:11, , 17F
09/22 18:11, 17F
→
09/22 18:16, , 18F
09/22 18:16, 18F
→
09/22 18:17, , 19F
09/22 18:17, 19F
→
09/22 18:21, , 20F
09/22 18:21, 20F
→
09/22 18:45, , 21F
09/22 18:45, 21F
→
09/22 18:45, , 22F
09/22 18:45, 22F
→
09/22 19:05, , 23F
09/22 19:05, 23F
→
09/22 19:07, , 24F
09/22 19:07, 24F
→
09/22 19:35, , 25F
09/22 19:35, 25F
→
09/22 19:36, , 26F
09/22 19:36, 26F
→
09/22 19:44, , 27F
09/22 19:44, 27F
→
09/22 19:45, , 28F
09/22 19:45, 28F