123
返回列表 發帖
travelntravel

Rank: 2Rank: 2
11# 跳轉到 »
發表於 2017-1-10 17:16 | 只看該作者
回覆 10# JL*

似係唔識跳去下一頁（with or for 有問題多）

TOP

JL*

Rank: 2Rank: 2
12#
發表於 2017-1-10 18:14 | 只看該作者
回覆 11# travelntravel
  1. With ws
  2.     If Right(.Name, 4) = "_PDT" Then
  3.     ....
  4.     ElseIf Right(.Name, 4) = "_L&D" Then
  5.     ....
  6.     End If
  7. End With
複製代碼
等於
  1. If Right(ws.Name, 4) = "_PDT" Then
  2. ....
  3. ElseIf Right(ws.Name, 4) = "_L&D" Then
  4. ....
  5. End If
複製代碼
1. With/For 唔會幫你自動去 activate 其他 sheets
2. 如果你現在第七張 sheet 係 active，你又無 activate 其他 sheets，ActiveSheet 就係指第七張 sheet

TOP

travelntravel

Rank: 2Rank: 2
13#
發表於 2017-1-12 14:24 | 只看該作者
回覆  travelntravel 等於1. With/For 唔會幫你自動去 activate 其他 sheets
2. 如果你現在第七張 sheet 係 ...
JL* 發表於 2017-1-10 18:14


    Dim ws As Worksheet, x As Integer, i As Integer

x = 7

For i = x To Worksheets.Count
Set ws = Worksheets(i)


呢個唔係叫佢由第7至n 張都咁做嗎？

請問點先可以做到上述效果? thx

TOP

mfai

Rank: 3Rank: 3Rank: 3
14#
發表於 2017-1-12 15:31 | 只看該作者
本帖最後由 mfai 於 2017-1-12 15:39 編輯

其實睇你想做d乜
唔係active worksheet 都改到内容

    For i = 1 To Worksheets.Count
       Worksheets(i).Name = Worksheets(i).Name & "AA"
    Next i

無activate 都改到 worksheet 名

TOP

狂蜂一號

Rank: 2Rank: 2
15#
發表於 2017-1-12 16:11 | 只看該作者
回覆 13# travelntravel

"呢個唔係叫佢由第7至n 張都咁做嗎？" - No!

All codes please.

TOP

travelntravel

Rank: 2Rank: 2
16#
發表於 2017-1-12 17:24 | 只看該作者
回覆  travelntravel

"呢個唔係叫佢由第7至n 張都咁做嗎？" - No!

All codes please. ...
狂蜂一號 發表於 2017-1-12 16:11


oh no, 原來係咁。

All code already. 除左個d " ..."（pdt 同l&d 唔同尾的處理方法）

TOP

狂蜂一號

Rank: 2Rank: 2
17#
發表於 2017-1-12 19:09 | 只看該作者
本帖最後由 狂蜂一號 於 2017-1-12 22:50 編輯

回覆 16# travelntravel

如果 "... " 無郁咗 worksheet 或 counter (留意 JL* 師兄嘅回覆)，只看 #1 帖的 code, Excel (Worksheets.Count - 7 + 1) 次，不過全部做晒落 ActiveSheet 度。
假如 ActiveSheet 符合 "_PDT"，咁唔理做幾多次，都只會入去嗰度。如果郁咗 worksheet 或 counter 咁就難講嘞！所以要睇晒全部 code.

TOP

travelntravel

Rank: 2Rank: 2
18#
發表於 2017-1-13 09:57 | 只看該作者
回覆  travelntravel

如果 "... " 無郁咗 worksheet 或 counter (留意 JL* 師兄嘅回覆)，只看 #1 帖的 co ...
狂蜂一號 發表於 2017-1-12 19:09



    ... 無郁過sheet, 只係改pdt or l&d sheet入面的框同顏色。

咁如果想做到由sheet 7 to n 都check sheet name, then 因應pdt or l&d 而做唔同野，應該點寫？

點先識做完第7張，then active shhet 變第8， and then active sheet 變第9 咁每張check ? thx a lot

TOP

狂蜂一號

Rank: 2Rank: 2
19#
發表於 2017-1-13 21:26 | 只看該作者
回覆 18# travelntravel

Sub border()
     
Dim ws As Worksheet, x As Integer, i As Integer

ThisWorkbook.Activate 'make sure Worksheets.Count and Worksheets(i) refer to ThisWorkbook

x = 7

For i = x To Worksheets.Count
Set ws = Worksheets(i)
With ws

If Right(.Name, 4) = "_PDT" Then 'PDT <> pdt

....

ElseIf Right(.Name, 4) = "_L&D" Then

....

Else '?

End If

End With
Next i
End Sub

Sub PS()
   'If Not OK Then
      'We need all codes and the structure of your workbook
   'Else
      '...
   'End If
End Sub

TOP

travelntravel

Rank: 2Rank: 2
20#
發表於 2017-1-16 10:46 | 只看該作者
都係只識做current sheet


Sub border()
   
    Dim ws As Worksheet, x As Integer, i As Integer
   
    ThisWorkbook.Activate

x = 7

For i = x To Worksheets.Count
Set ws = Worksheets(i)
With ws

If Right(ActiveSheet.Name, 4) = "_PDT" Then

    Range("BA25:BA44,BA46:BA65,BA67:BA86,BA88:BA107,BA109:BA128,BA230:BA249,BA251:BA270,BA272:BA291,BA293:BA312,BA314:BA333,BA335:BA354,BA456:BA475,BA477:BA496,BA498:BA517,BA519:BA538,BA540:BA559").Select
    Range("BA540").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

ElseIf Right(ActiveSheet.Name, 4) = "_L&D" Then

    Range("BA24:BA43,BA45:BA64,BA66:BA85,BA87:BA106,BA108:BA127,BA129:BA148,BF24:BF43,BF45:BF64,BF66:BF85,BF87:BF106,BF108:BF127,BF129:BF148").Select
    Range("BA24").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End If
End With
Next
End Sub

TOP

123
返回列表