我目前正在开发一个 excel 应用程序，其中有一个宏，通过单击按钮操作，它可以重置表格中某些单元格内的数值。
在这个表中有 3 列；“安装数量 (n)”、“所需数量 (m)”和“锁配置”。
我需要发生的是，当单击按钮时，“拟合数量（n）”列中每行的数值被重置以匹配同一行的“所需数量（m）”列中显示的值.
但是，如果该行的“锁定配置”列中的单元格值设置为“锁定”，我希望单击按钮后“拟合数量（n）”值保持不变。
我希望这是有道理的！这是我目前对此编码的尝试：
Public Sub Reset_Quantity_Fitted()
'Macro to make quantity fitted equal to quantity required
Dim rng As Range
Dim cell As Range
Set rng = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
For Each cell In rng.Cells
If rng.Offset(, 5) = "Locked" Then
cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
Else
cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Required (m)").DataBodyRange
End If
Next cell
End Sub
此方法是由该站点上的另一位用户推荐的，但是在运行此代码时，我收到以下错误：
运行时错误“13”：类型不匹配
谁能帮我找出这段代码有什么问题？
这段代码应该做你要问的：
Sub Test()
Dim x As Long
'Set reference to your table. Have hard-coded the sheet name and table name in.
Dim MyTable As ListObject
Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table_Sheet1")
'These will be the numerical index numbers of the columns in your table.
'This assumes your "Locked" column is in the table and has the header "Locked".
Dim Required As Long, Fitted As Long, Locked As Long
Required = MyTable.ListColumns("Quantity Required (m)").Index
Fitted = MyTable.ListColumns("Quantity Fitted (n)").Index
Locked = MyTable.ListColumns("Locked").Index
'Look at each row in the table. Am using `x` rather than `Each Cell`
'as the row number of the cell may differ from the row location in the table
'e.g. If your table starts on row 2, then first row after the header is row 3 - row 3 as Cell, but row 1 in table.
For x = 1 To MyTable.Range.Rows.Count
'If Locked column doesn't say "Locked" then copy value from
'Fitted to Required columns, otherwise do nothing.
If MyTable.DataBodyRange.Cells(x, Locked) <> "Locked" Then
MyTable.DataBodyRange.Cells(x, Fitted) = MyTable.DataBodyRange.Cells(x, Required)
Else
'Do Nothing.
'The ELSE and comment aren't really needed - just here to show nothing happens.
End If
Next x
End Sub
回答您的Set问题 -Set用于分配对对象的引用。
发现this other question可以更好地回答。考虑下面的代码：
Sub Test2()
Dim MyCellValue As String
Dim MyCellReference As Range
'Will only contain the string value held in A3 as
'the default property of a cell is the value.
MyCellValue = Sheet1.Range("A3")
MyCellValue = 3 'This changes the value of MyCellValue.
'MyCellValue.Font.Bold = True 'Doesn't compile as "Invalid Qualifier"
'Holds a reference to A3.
Set MyCellReference = Sheet1.Range("A3")
MyCellReference = 1 'This changes the value held in cell A3.
MyCellReference.Font.Bold = True
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句