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

Question

圖片參考：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之後的程式碼應該如何增寫?

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

Answer 1

最佳解答

列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

Answer 2

http://blog.xuite.net/hcm19522/twblog/204827848

參考 ~