[發問] 不特定選區的資料互換

jesscc

我想在J欄選取一個不特定的區域(譬如J13:J17)，然後跟不同欄但同列的E13:E19資料互換。自己寫了一段，但沒作用，能幫忙看一下該如何改嗎?
test.rar (14.01 KB)

下載次數: 14

2019-12-2 10:13

准提部林

Private Sub CommandButton1_Click()
Dim xB As Range, Arr
With Selection
     If .Columns.Count > 1 Or .Column <> 10 Then Exit Sub
     If Intersect(Me.UsedRange, .Cells).Address <> .Address Then Exit Sub
     Set xB = Range(Replace(.Address, "J", "E"))
     Arr = .Value
     .Value = xB.Value
     xB.Value = Arr
End With
End Sub

jcchiang

回復 6# jesscc

沒有看到實際的狀況無法知道原因
但測試過選一列(個)儲存格是可以互換的

jesscc

回復  jesscc

再試試
Private Sub CommandButton1_Click()

Set frow = Selection.SpecialCells(xlC ...
jcchiang 發表於 2019-12-2 16:39

j大，改用上述寫法，在測試檔裡可以正常運作，但我用在實際的工作檔裏發生了一個問題:如果只有選取一列(個)儲存格會出現"溢位"的偵錯，您知道這是哪裡有問題嗎?

hcm19522

https://blog.xuite.net/hcm19522/twblog/588682462

jcchiang

回復 3# jesscc

再試試
Private Sub CommandButton1_Click()

Set frow = Selection.SpecialCells(xlCellTypeVisible)
lrow = frow.Row + frow.Count - 1
rng1 = Range("J" & frow.Row, "J" & lrow)
rng2 = Range("E" & frow.Row, "E" & lrow)

Range("J" & frow.Row, "J" & lrow) = rng2
Range("E" & frow.Row, "E" & lrow) = rng1

End Sub

jesscc

回復 2# jcchiang
這樣不行，非選取區域的資料也會跟著互換

jcchiang

回復 1# jesscc

試試看
Private Sub CommandButton1_Click()

Set frow = Selection.SpecialCells(xlCellTypeVisible)(1)
Set lrow = Selection.SpecialCells(xlCellTypeLastCell)
rng1 = Range("J" & frow.Row, "J" & lrow.Row)
rng2 = Range("E" & frow.Row, "E" & lrow.Row)

'A = rng1
'rng1 = rng2
'rng2 = A

Range("J" & frow.Row, "J" & lrow.Row) = rng2
Range("E" & frow.Row, "E" & lrow.Row) = rng1

End Sub