首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将If语句合并为Select

问将If语句合并为Select
EN

Stack Overflow用户
提问于 2022-01-06 18:41:00
回答 2查看 108关注 0票数 0

我试图将原始代码更改为一些新的代码行。

因此,如果c=某些文本,同样,如果高度介于某个数字之间,则分配一个值。但是当我运行新代码时,VBA会给出一个错误: Object变量而不是set...Help!

关于原始代码,请参阅下面的原始代码,该代码运行良好:

代码语言:javascript
复制
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

下面是新代码--新代码:

代码语言:javascript
复制
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列的代码(它本身运行良好):

代码语言:javascript
复制
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

结束子对象

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2022-01-06 21:42:56

一个可能的答案..。

试试这个:

代码语言:javascript
复制
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

我所做的改变:

  1. 在使用"BASE“的字符串中有不一致之处。“基本”,并改变了这一点。
  2. I翻转了两个主循环的顺序,因为如果按原语顺序执行,那么在Select Case语句中进行测试之前更改列K。行d = d & " " & c.Offset(, 1)的
  3. 应该是d = d & " " & d.Offset(, 1) (您刚刚更新了吗?)我现在没有看到它,但我发誓我得到了这个对象变量不设置问题)。

不管怎么说,在我理解你的要求的时候,它似乎对我起了作用。

更新答复

代码语言:javascript
复制
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

这是我这边的样子:

​

票数 0
EN

Stack Overflow用户

发布于 2022-01-07 12:24:18

使用另一个工作表为每个项目配置代码。

​

然后用匹配来找到它们

代码语言:javascript
复制
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 Sub
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/70612041

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档