excel vba For 多迴圈問題

vba excel vba #excel #vba

msmplayv121068 2021-06-08 11:31:11 ‧ 3374 瀏覽

分享至

測試檔https://drive.google.com/file/d/1rXeDabdjFp_z51J7U4Dc1_PPnVII4nCm/view?usp=sharing

大師們好~想請教我在網路上查到我需要使用的For迴圈寫法，但我找不到方法怎麼將多個迴圈精簡寫法，可以請大師們幫我看看嗎?

主要需求是從50~79列對應6~35列，如果50~79列該列有值時，將該列儲存格文字插入對應6~35列儲存格註解，例如:
50列有值，將50列有值儲存格插入第6列對應儲存格註解
51列有值，將51列有值儲存格插入第7列對應儲存格註解
52列有值，將52列有值儲存格插入第8列對應儲存格註解
以此類推.....

Sub 多列註解()
For i = 5 To [MQ50].End(xlToLeft).Column
Cx = Cells(50, i)
If Cx > "" Then
With Cells(6, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ51].End(xlToLeft).Column
Cx = Cells(51, i)
If Cx > "" Then
With Cells(7, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ52].End(xlToLeft).Column
Cx = Cells(52, i)
If Cx > "" Then
With Cells(8, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ53].End(xlToLeft).Column
Cx = Cells(53, i)
If Cx > "" Then
With Cells(9, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ54].End(xlToLeft).Column
Cx = Cells(54, i)
If Cx > "" Then
With Cells(10, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ55].End(xlToLeft).Column
Cx = Cells(55, i)
If Cx > "" Then
With Cells(11, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ56].End(xlToLeft).Column
Cx = Cells(56, i)
If Cx > "" Then
With Cells(12, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ57].End(xlToLeft).Column
Cx = Cells(57, i)
If Cx > "" Then
With Cells(13, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ58].End(xlToLeft).Column
Cx = Cells(58, i)
If Cx > "" Then
With Cells(14, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ59].End(xlToLeft).Column
Cx = Cells(59, i)
If Cx > "" Then
With Cells(15, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ60].End(xlToLeft).Column
Cx = Cells(60, i)
If Cx > "" Then
With Cells(16, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ61].End(xlToLeft).Column
Cx = Cells(61, i)
If Cx > "" Then
With Cells(17, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ62].End(xlToLeft).Column
Cx = Cells(62, i)
If Cx > "" Then
With Cells(18, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ63].End(xlToLeft).Column
Cx = Cells(63, i)
If Cx > "" Then
With Cells(19, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ64].End(xlToLeft).Column
Cx = Cells(64, i)
If Cx > "" Then
With Cells(20, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ65].End(xlToLeft).Column
Cx = Cells(65, i)
If Cx > "" Then
With Cells(21, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ66].End(xlToLeft).Column
Cx = Cells(66, i)
If Cx > "" Then
With Cells(22, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ67].End(xlToLeft).Column
Cx = Cells(67, i)
If Cx > "" Then
With Cells(23, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ68].End(xlToLeft).Column
Cx = Cells(68, i)
If Cx > "" Then
With Cells(24, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ69].End(xlToLeft).Column
Cx = Cells(69, i)
If Cx > "" Then
With Cells(25, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ70].End(xlToLeft).Column
Cx = Cells(70, i)
If Cx > "" Then
With Cells(26, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ71].End(xlToLeft).Column
Cx = Cells(71, i)
If Cx > "" Then
With Cells(27, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ72].End(xlToLeft).Column
Cx = Cells(72, i)
If Cx > "" Then
With Cells(28, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ73].End(xlToLeft).Column
Cx = Cells(73, i)
If Cx > "" Then
With Cells(29, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ74].End(xlToLeft).Column
Cx = Cells(74, i)
If Cx > "" Then
With Cells(30, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ75].End(xlToLeft).Column
Cx = Cells(75, i)
If Cx > "" Then
With Cells(31, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ76].End(xlToLeft).Column
Cx = Cells(76, i)
If Cx > "" Then
With Cells(32, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ77].End(xlToLeft).Column
Cx = Cells(77, i)
If Cx > "" Then
With Cells(33, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ78].End(xlToLeft).Column
Cx = Cells(78, i)
If Cx > "" Then
With Cells(34, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next

For i = 5 To [MQ79].End(xlToLeft).Column
Cx = Cells(79, i)
If Cx > "" Then
With Cells(35, i)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Cx
End With
End If
Next
End Sub

japhenchen iT邦超人 1 級 ‧ 2021-06-08 11:45:59 檢舉

你可以把程式碼放在＜＞裡嗎？

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-08 12:51:26 檢舉

j大~放好了!哈

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-06-08 16:40:31

最佳解答

請參閱 : Excel VBA Loop - Easy Excel Macros
關鍵字 : Double Loop

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-08 20:44:18 檢舉

r大~我對語法好像真的~無法欸天啊

achan iT邦研究生 3 級 ‧ 2021-06-09 08:11:06 檢舉

r大給的已經是很簡單的範例了

achan iT邦研究生 3 級 ‧ 2021-06-09 09:06:03 檢舉

題外話
1.註解要加上去，需先區分原本有無註解作法不同
2.[MQ列].End(xlToLeft).Column 你要判斷最後有值欄位是否該改成[MR列].End(xlToLeft).Column ?
3.Looping is one of the most powerful programming techniques 很基礎但也是最重要的入門技巧

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-10 07:49:05 檢舉

謝謝r大跟a大~我再來研究研究~哈

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-12 12:57:47 檢舉

r大~可以求救一下嗎?就是我怎麼試還是不成功，你可以幫我看看到底為什麼嗎?

Sub 多列註解()
Dim Cx As String
Dim i As Integer, j As Integer, c As Integer

For i = 3 To 32
    For c = 40 To 69
        For j = 5 To 355

        Cx = Cells(c, j)
        If Cx > "" Then
        With Cells(i, j)
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:=Cx
        End With
        End If

        Next j
    Next c
Next i
End Sub

rogeryao iT邦超人 7 級 ‧ 2021-06-12 13:50:05 檢舉

Private Sub CommandButton1_Click()
Dim Cx As String
Dim i As Integer, j As Integer
For i = 3 To 33
    For j = 5 To 355
        Cx = Cells(i + (40 - 3), j)
        If Cx <> "" Then
        With Cells(i, j)
        ' 刪除註解
        .ClearComments
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:=Cx
        End With
        End If
    Next j
Next i
End Sub

登入發表回應

blanksoul12

iT邦研究生 5 級 ‧ 2021-06-08 15:15:56

50-79 放 array, 兩個 for 行完二維陣列便可以了吧

回應 3
分享
檢舉

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-08 15:42:27 檢舉

b大~可以提供寫法嗎?因為我真的搞不懂耶其實~

blanksoul12 iT邦研究生 5 級 ‧ 2021-06-09 08:31:13 檢舉

看得明邏輯嗎? 不明便 F8 續行看

For i = 1 To 10
    For y = 1 To 10
        Cells(i, y) = x
        x = x + 1
    Next
Next

vba 是要多做多看,不要挑自己想看的,什麼都要看一下

msmplayv121068 iT邦新手 4 級 ‧ 2021-06-10 07:45:50 檢舉

嗯嗯~我放假再來研究看看，謝謝b大~

登入發表回應

我要發表回答

立即登入回答

參賽組數

1064 組

團體組數

40 組

累計文章數

22207 篇

完賽人數

602 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js

IT邦幫忙