我是VBA的新手,是从今天早上开始的,当时遇到了具有约3万行的电子表格。
我有两个工作表:
我希望Excel检查“ tohere”单元格C1中的单元格中是否存在“ tohere”单元格中的数字,如果存在,则将“ fromhere”列B中相应行的值复制到“ tohere”中,单元格B1;然后针对C2等再次执行此操作。如果“ fromhere”工作表中没有此类数字,则无需对此行执行任何操作。
我尝试了这段代码:
Dim i As Long
Dim tohere As Worksheet
Dim fromhere As Worksheet
Set tohere = ThisWorkbook.Worksheets("tohere")
Set fromhere = ThisWorkbook.Worksheets("fromhere")
For i = 1 To 100
If fromhere.Range("C" & i).Value <> tohere.Range("C" & i).Value Then
Else: fromhere.Cells(i, "B").Copy tohere.Cells(i, "B")
End If
Next i
对于第一个相等的单元格(在我的情况下为4),它会执行我想要的操作,然后停下来,不进一步查找。
我尝试使用Cells(i, "C")
相同的东西。使用i = i + 1
afterThen
没有帮助。
我觉得问题出在我的手机上,但我不知道如何解决。
这是我的示例“ fromhere”列表的样子(您会注意到C列中缺少一些数字):
这是我在“ tohere”列表中得到的示例:
到达“ fromhere”中没有“ 5”的位置,并在此位置停止。
PS:i = 1 To 100
只是为了测试它。
这应该做你的工作。运行这个,让我知道。
Sub test()
Dim tohere As Worksheet
Dim fromhere As Worksheet
Dim rngTohere As Range
Dim rngfromHere As Range
Dim rngCelTohere As Range
Dim rngCelfromhere As Range
'Set Workbook
Set tohere = ThisWorkbook.Worksheets("tohere")
Set fromhere = ThisWorkbook.Worksheets("fromhere")
'Set Column
Set rngTohere = tohere.Columns("C")
Set rngfromHere = fromhere.Columns("C")
'Loop through each cell in Column C
For Each rngCelTohere In rngTohere.Cells
If Trim(rngCelTohere) <> "" Then
For Each rngCelfromhere In rngfromHere.Cells
If UCase(Trim(rngCelTohere)) = UCase(Trim(rngCelfromhere)) Then
rngCelTohere.Offset(, -1) = rngCelfromhere.Offset(, -1)
Exit For
End If
Next rngCelfromhere
End If
Next rngCelTohere
Set tohere = Nothing
Set fromhere = Nothing
Set rngTohere = Nothing
Set rngfromHere = Nothing
Set rngCelTohere = Nothing
Set rngCelfromhere = Nothing
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句