我试图将原始代码更改为一些新的代码行。
因此,如果c=某些文本,同样,如果高度介于某个数字之间,则分配一个值。但是当我运行新代码时,VBA会给出一个错误: Object变量而不是set...Help!
关于原始代码,请参阅下面的原始代码,该代码运行良好:
Sub TheMiddleMan()
Dim ws As Worksheet, lr As Long, c As Range, rng As Range, x As Long
Set ws = ActiveSheet
Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
For Each c In ws.Range("K2:K" & lr)
If c = "C BOX (6""" & " WALL)" Or _
c = "C BASE (6""" & " WALL)" Or _
c = "C COLLAR (6""" & " WALL)" Or _
c = "D BOX (6""" & " WALL)" Or _
c = "SAN MH(5""" & " WALL)" Or _
c = "MH 72""" & " DIA(8""" & " WALL)" Then
c = c & " " & c.Offset(, 1)
End If
Next c
For Each c In rng
x = Val(c)
Select Case x
Case 12 To 19
c.Offset(, -8).Value = "F22122J"
Case 20 To 32
c.Offset(, -8).Value = "F22123J"
Case 33 To 42
c.Offset(, -8).Value = "F22124J"
End Select
Next
End Sub下面是新代码--新代码:
Sub TheMiddleMan()
Dim ws As Worksheet, lr As Long, c As Range, rng As Range, x As Long, d As Range
Set ws = ActiveSheet
Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
For Each d In ws.Range("K2:K" & lr)
If d = "C BOX (6""" & " WALL)" Or _
d = "C BASE (6""" & " WALL)" Or _
d = "C COLLAR (6""" & " WALL)" Or _
d = "D BOX (6""" & " WALL)" Or _
d = "SAN MH(5""" & " WALL)" Or _
d = "MH 72""" & " DIA(8""" & " WALL)" Then
d = d & " " & d.Offset(, 1)
End If
Next
For Each c In rng
For Each d In ws.Range("K2:K" & lr)
x = Val(c)
Select Case x
Case 12 To 19
If d = "C BOX (6""" & " WALL)" Then
c.Offset(, -8).Value = "F22122J"
End If
If d = "C Base (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21123J"
End If
Case 20 To 32
If d = "C BOX (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21122J"
End If
If d = "C Base (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21123J"
End If
Case 33 To 42
If d = "C BOX (6""" & " WALL)" Then
d.Offset(, -8).Value = "F21122J"
End If
If d = "C Base (6""" & " WALL)" Then
d.Offset(, -8).Value = "F21123J"
End If
End Select
Next
Next结束子对象
更新D列的代码(它本身运行良好):
Sub ZackCase()
Dim ws As Worksheet, c As Range, rng As Range, x As Long
Set ws = ActiveSheet
Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
For Each c In rng
x = Val(c)
Select Case x
Case 12 To 19
c.Offset(, -8).Value = "F22122J"
Case 20 To 32
c.Offset(, -8).Value = "F22123J"
Case 33 To 42
c.Offset(, -8).Value = "F22124J"
End Select
Next结束子对象
发布于 2022-01-06 21:42:56
一个可能的答案..。
试试这个:
Sub TheMiddleMan2()
Dim ws As Worksheet, lr As Long, c As Range, rng As Range, x As Long, d As Range
Set ws = ActiveSheet
Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
For Each c In rng
For Each d In ws.Range("K2:K" & lr)
x = Val(c)
Select Case x
Case 12 To 19
If d = "C BOX (6""" & " WALL)" Then
c.Offset(, -8).Value = "F22122J"
End If
If d = "C BASE (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21123J"
End If
Case 20 To 32
If d = "C BOX (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21122J"
End If
If d = "C BASE (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21123J"
End If
Case 33 To 42
If d = "C BOX (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21122J"
End If
If d = "C BASE (6""" & " WALL)" Then
c.Offset(, -8).Value = "F21123J"
End If
End Select
Next
Next
For Each d In ws.Range("K2:K" & lr)
Debug.Print d
Debug.Print "C BOX (6""" & " WALL)"
If d = "C BOX (6""" & " WALL)" Or _
d = "C BASE (6""" & " WALL)" Or _
d = "C COLLAR (6""" & " WALL)" Or _
d = "D BOX (6""" & " WALL)" Or _
d = "SAN MH(5""" & " WALL)" Or _
d = "MH 72""" & " DIA(8""" & " WALL)" Then
d = d & " " & d.Offset(, 1)
End If
Next
End Sub我所做的改变:
Select Case语句中进行测试之前更改列K。行d = d & " " & c.Offset(, 1)的d = d & " " & d.Offset(, 1) (您刚刚更新了吗?)我现在没有看到它,但我发誓我得到了这个对象变量不设置问题)。不管怎么说,在我理解你的要求的时候,它似乎对我起了作用。
更新答复
Sub TheMiddleMan2()
Dim ws As Worksheet, lr As Long, c As Range, rng As Range, x As Long, d As Range
Set ws = ActiveSheet
Set rng = ws.Range("L2", Cells(Rows.Count, "L").End(xlUp))
lr = ws.Cells(Rows.Count, 11).End(xlUp).Row
'-------------------------------------
' Removed the previous c loop
'-------------------------------------
For Each d In ws.Range("K2:K" & lr)
x = Val(d.Offset(0, 1).Value)
Set c = d.Offset(0, -7) ' For ease of use and updating, I set c to be the cell in Column D. I know, it's a bad naming convention.
Select Case x
Case 12 To 19
If d = "C BOX (6""" & " WALL)" Then
c.Value = "BOX 12 to 19" ' I set all the values to be explicit so I know where in the Case Statement the end result came from.
End If
If d = "C BASE (6""" & " WALL)" Then
c.Value = "BASE 12 to 19"
End If
Case 20 To 32
If d = "C BOX (6""" & " WALL)" Then
c.Value = "BOX 20 to 32"
End If
If d = "C BASE (6""" & " WALL)" Then
c.Value = "BASE 20 to 32"
End If
Case 33 To 42
If d = "C BOX (6""" & " WALL)" Then
c.Value = "BOX 33 to 42"
End If
If d = "C BASE (6""" & " WALL)" Then
c.Value = "BASE 33 to 42"
End If
End Select
Next
For Each d In ws.Range("K2:K" & lr)
Debug.Print d
Debug.Print "C BOX (6""" & " WALL)"
If d = "C BOX (6""" & " WALL)" Or _
d = "C BASE (6""" & " WALL)" Or _
d = "C COLLAR (6""" & " WALL)" Or _
d = "D BOX (6""" & " WALL)" Or _
d = "SAN MH(5""" & " WALL)" Or _
d = "MH 72""" & " DIA(8""" & " WALL)" Then
d = d & " " & d.Offset(, 1)
End If
Next
End Sub这是我这边的样子:

发布于 2022-01-07 12:24:18
使用另一个工作表为每个项目配置代码。

然后用匹配来找到它们
Option Explicit
Sub TheMiddleMan()
Dim ws As Worksheet, wsConfig As Worksheet
Dim k As Range, ar, s As String, h As String
Dim x As Single, lr As Long, n As Long, i As Long
Dim r As Long, c As Long, bHasL As Boolean
Dim rngItems As Range, v As Variant
Set ws = Sheets(1)
Set wsConfig = Sheets(2)
With wsConfig
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rngItems = .Range("A1:A" & lr)
End With
'MsgBox lr - 1 & " items found"
With ws
lr = ws.Cells(Rows.Count, "K").End(xlUp).Row
For Each k In .Range("K2:K" & lr)
s = Trim(k.Value2)
h = k.Offset(, 1) ' col L height
x = Val(h) ' remove "
' has item got height, romove for match
If Right(s, Len(h) + 1) = " " & h Then
s = Left(s, Len(s) - Len(h) - 1)
bHasL = True
Else
bHasL = False
End If
' match
v = Application.Match(s, rngItems, 0)
If Not IsError(v) Then
' select column according to quantity
Select Case Val(x)
Case 12 To 19: c = 2
Case 20 To 32: c = 3
Case 33 To 42: c = 4
Case Else: c = 0
End Select
If c > 0 Then
' get code from config
k.Offset(, -7) = wsConfig.Cells(v, c)
End If
' add col L if not already
If bHasL = False Then
k.Value2 = k.Value2 & " " & h
End If
n = n + 1
End If
Next
End With
MsgBox n & " rows matched", vbInformation
End Subhttps://stackoverflow.com/questions/70612041
复制相似问题