Excel VBA中的for循环仅在调试模式下运行
问题描述:
我试图找出为什么下面的代码仅在调试模式下工作......Excel VBA中的for循环仅在调试模式下运行
当我打我的“保存”按钮宏观节省每数据录入甚至如果它是相同的公司名称。当我调试宏进入for循环,并给我的消息“公司已经存在”。
我的错误在哪里?
Sub Copy_Values()
Dim sapcolleague As String, str1 As String, str2 As String, str3 As String
Dim i As Integer, ValueToFind As String, ValueToCheck As String
Dim totalAccRows As Integer
Dim accColumn As Integer
Dim currentAccRow As Integer
accColumn = 2
totalAccRows = Worksheets("DB").Cells(Rows.Count, accColumn).End(xlUp).Row
ValueToFind = Worksheets("Maintain").Range("F13").Value
For currentAccRow = 2 To totalAccRows
If Cells(currentAccRow, accColumn).Value = ValueToFind Then
MsgBox ("Company already exists!")
Exit Sub
End If
Next
Worksheets("DB").Range("A8").EntireRow.Insert
Worksheets("DB").Range("A8").Value =
Worksheets("Maintain").Range("F11").Value
Worksheets("DB").Range("B8").Value =
Worksheets("Maintain").Range("F13").Value
Worksheets("DB").Range("C8").Value =
Worksheets("Maintain").Range("F15").Value
str1 = Worksheets("Maintain").Range("F18").Value
str2 = Worksheets("Maintain").Range("F19").Value
str3 = Worksheets("Maintain").Range("F20").Value
sapcolleague = str1 & " " & str2 & " " & str3
Worksheets("DB").Range("D8").Value = sapcolleague
' This MsgBox will only show if the loop completes with no success
MsgBox "Successfully saved!"
End Sub
答
在If Cells(currentAccRow, accColumn).Value = ValueToFind Then
的Cells
没有父表,因此将是在运行时的ActiveSheet。
快速改写:
Option Explicit
Sub Copy_Values()
Dim sapcolleague As String, valueToFind As String, accColumn As Long
With Worksheets("DB")
accColumn = 2
valueToFind = Worksheets("Maintain").Range("F13").Value
If Not IsError(Application.Match(valueToFind, .Columns(accColumn), 0)) Then
MsgBox ("Company already exists!")
Exit Sub
End If
.Range("A8").EntireRow.Insert
.Range("A8:C8").Value = Array(Worksheets("Maintain").Range("F11").Value, _
valueToFind, _
Worksheets("Maintain").Range("F15").Value)
sapcolleague = Join(Array(Worksheets("Maintain").Range("F18").Value, _
Worksheets("Maintain").Range("F19").Value, _
Worksheets("Maintain").Range("F20").Value), Chr(32))
.Range("D8").Value = sapcolleague
End With
'Shouldn't it be saved somewhere around here?
' This MsgBox will only show if the loop completes with no success
MsgBox "Successfully saved!"
End Sub
+0
“保存”可能指的是复制“维护”表单中的信息并将其“保存”到“数据库”表单中。 – YowE3K
什么是保存和这个宏之间的联系? – sktneer
'如果单元格(currentAccRow,accColumn).Value = ValueToFind Then'这里单元格将引用哪个表单碰巧是活动表单。最好限定每个使用范围/单元格的工作表对象。调试时,您可能选择了不同的工作表。使用'If Worksheets(“DB”)。Cells(currentAccRow,accColumn).Value = ValueToFind Then' –
伟大的提示!我没有确定参考正确的工作表。你的提案奏效了! 非常感谢! –