Airman
Lv 4
Airman 發問時間: 電腦與網際網路軟體 · 7 年前

VBA語法︰橫向連續遞增列當欄複製。

圖片參考:https://s.yimg.com/rk/AD04809788/o/167727891.png

列120

r = MTHCOUNT - StrRng + 1

列152

For j = 1 To r

With n_rag(j + 1, j +1).Resize(r, 1)

.Value = n_rag.Value

.Font.Size = 14

.NumberFormatLocal ="00"

.Borders.LineStyle =xlContinuous

End With

Next

With n_rag(1, 2).Resize(r, r)

.Font.Size = 14

.NumberFormatLocal ="00"

.Borders.LineStyle =xlContinuous

End With

For j = 1 To r

For k = 1 To r + 1

L = k – j

With n_rag(k, j + 1)

If L < 0 Then

.Value = n_rag(r+ k - j, 1).Value

.Font.ColorIndex= 10

ElseIf L = 0 Then

.Value = n_rag(r+ k - j, 1).Value

.Font.ColorIndex= 10

Else

.Value = n_rag(k- j, 1).Value

End If

End With

Next

Next

需求︰

以StrRng= 1,MTHCOUNT=120為例~

.Range("O6","O" & r + 5).Copy .Range("O" & r + 7)

.Range("O6","O" & r - 1 + 5).Copy .Range("P" & r + 8)

.Range("O6","O" & r - 119 + 6).Copy .Range("BC" & r*2 + 5)

.Range("O6","O" & r - 120 + 6).Copy .Range("BD" & r*2 + 6)

另將下列各單個儲存格標示40號底底色~

.Range("O" & r + 7)

.Range("P" & r + 8)

.Range("BC" & r*2 + 5)

.Range("BD" & r*2 + 6)

以上列152之後的程式碼應該如何增寫?

敬請各位大大賜教!謝謝!

2 個解答

評分
  • ?
    Lv 7
    7 年前
    最佳解答

    列152

    With n_rag(1, 2).Resize(2 * r + 1, r)

    .Font.Size = 14

    .NumberFormatLocal = "00"

    .Borders.LineStyle =xlContinuous

    EndWith

    Forj = 1 To r

    For k = 1 To 2 * r + 1

    l = k – j

    With n_rag(k, j + 1)

    If l < 0 Then

    .Value = n_rag(r + l, 1).Value

    .Font.ColorIndex = 10

    ElseIf l = 0 Then

    .Value = n_rag(r + l, 1).Value

    .Font.ColorIndex = 10

    ElseIf l < r + 1 Then

    .Value = n_rag(l, 1).Value

    ElseIf l = r + 1 Then

    .Value = n_rag(r + l, 1).Value

    .Font.ColorIndex = 10

    .Interior.ColorIndex = 40

    ElseIf l > r + 1 Then

    .Value = n_rag(r + l - r,1).Value

    End If

    End With

    Next

    Next

還有問題?馬上發問,尋求解答。