標題: 指定欄位取代字元 [打印本頁]

---

作者: EddieLiang    時間: 2016-4-19 11:17     標題: 指定欄位取代字元

小弟各種嘗試還是無解,想請高手協助一下
執行此程式會將所有sheet儲存格的111改為你好
可否指定抓sheet的J欄做修改?
感激不盡!!!!:)
Option Explicit

Sub LoopSheets()
Dim ws As Worksheet
For Each ws In Sheets
    ReplaceText "111", "你好", ws
Next
End Sub
Function ReplaceText(src As String, Rpl As String, sht As Worksheet)
    sht.Cells.Replace What:=src, Replacement:=Rpl, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Function

---

作者: 准提部林    時間: 2016-4-19 20:37

Sub LoopSheets()
Dim ws As Worksheet
For Each ws In Sheets
　　ws.[J:J].Replace "111", "你好", Lookat:=xlWhole
Next
End Sub

---

作者: EddieLiang    時間: 2016-4-20 14:46

回復 2# 准提部林

感謝準大無私提供,小弟也經常參考您的部落格自習.看到您在知識+的回覆真的是受益良多!!!!:victory:


Sub LoopSheets()
Dim ws As Worksheet
For Each ws In Sheets
　　ws.[J4:J20].Replace "111", "你好", Lookat:=xlWhole
Next
End Sub

準大小的遇到一個比較無解的問題,因為是J欄所以整欄都會是"你好"只好手動改成[J4:20],有無辦法判別I欄有值才對入呢?

---

作者: 准提部林    時間: 2016-4-20 17:47

本帖最後由 准提部林 於 2016-4-20 17:50 編輯

回復 3# EddieLiang


Sub LoopSheets()
Dim Sht As Worksheet, xR As Range
For Each Sht In Sheets
　　For Each xR In Sht.[J4:J20]
　　　　If xR & "" = "111" And xR(1, 0) <> "" Then xR = "你好"
　　Next
Next
End Sub

xR(1, 0)　往左一格，即是ｉ欄！

或：
Sub LoopSheets()
Dim Sht As Worksheet, xR As Range
For Each Sht In Sheets
    For Each xR In Range(Sht.[J4], Sht.[J65536].End(xlUp))　抓資料範圍
        If xR & "" = "111" And xR(1, 0) <> "" Then xR = "你好"
    Next
Next
End Sub
　

　

---

作者: EddieLiang    時間: 2016-4-21 11:41

回復 4# 准提部林

Sub format()
Dim Sht As Worksheet, xR As Range
For Each Sht In Sheets [[準大這下面兩列我沒辦法理解]]
    For Each xR In Range(Sht.[J4], Sht.[J65536].End(xlUp))  '抓資料範圍從J4開始  [[懇請指導]]
        If xR & "" <> "完成(finish)" And xR(1, 0) <> "" Then xR = "Wait for test"    '判斷j欄非完成字串,i欄有資料帶入Wait for test字串 [[這code經過您指導後改為非完成字串判斷改為waitfortest,非常受用非常感謝您]]
    Next
Next
   
    End Sub

---

作者: 准提部林    時間: 2016-4-21 12:26

回復 5# EddieLiang


Sht.[J65536].End(xlUp) 抓Ｊ欄最後一個非空儲存格

---

作者: EddieLiang    時間: 2016-4-25 11:08

回復 6# 准提部林

準大非常感謝您提供這非常受用的語法啊,看來這步應該可以吃很久了(誤;P )
請外想請教一下因worksheet有很多sheet可否指定module1只在sheet1運行呢?
試了以下幾種方始編譯都無法運行:Q

Sub format()

Dim Sht As Worksheet, xR As Range 'Worksheet.("sheet1")
For Each Sht In Sheets 'In Worksheet("sheet1")
    For Each xR In Range(Sht.[J4], Sht.[J65536].End(xlUp))  '抓資料範圍從J4開始
        If xR & "" <> "完成(finish)" And xR(1, 0) <> "" Then xR = "Wait for test" '判斷i欄有無資料帶入字串
    Next
Next
   
    End Sub

---

作者: 准提部林    時間: 2016-4-25 13:47

回復 7# EddieLiang


Sub format()
Dim xR As Range
With Sheets("Sheet1")
　　　For Each xR In Range(.[J4], .[J65536].End(xlUp))
　　　　　If xR & "" <> "完成(finish)" And xR(1, 0) <> "" Then xR = "Wait for test" '判斷i欄有無資料帶入字串
　　　Next
End With
End Sub

---

作者: EddieLiang    時間: 2016-4-26 11:04

回復 8# 准提部林

竟然是with小弟太淺了再次感謝準大熱心回復!!!!

---

作者: EddieLiang    時間: 2016-5-17 16:41

回復 9# EddieLiang

有查到繪製格線的語法為手動方式繪製格線

Sub 繪製外框線()
MsgBox “在所選取的儲存格範圍四周繪製外框線”
With Selection
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
End Sub


假設表格如下

A B C D E
1  2 3 4 5
6  7  8 9 1

固定左上A4則J欄則判斷最後一欄有值為繪製範圍,例如J20,可否使用判斷語法繪製格線?

---

作者: EddieLiang    時間: 2016-5-17 17:06

回復 10# EddieLiang

'選取以A1為基準，向下和向右所有連續資料的儲存格
ActiveSheet.Range("A4").End(xlDown).End(xlToRight).Select

'資質愚昧改為這樣卻無法運行
ActiveSheet.Range("A4").End(xlDown).End(xlToRight).Selection.Borders.LineStyle = xlContinuous '無法執行

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)