I am trying to change the original code to some new lines of code
so If c = certain text, also if the height is between certain number, a value gets assigned. But when I run the new code, VBA gives me an error: Object Variable not set...Help!
See below for the original code, which works fine original code:
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
And below is the new code New code:
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
End Sub
codes for updating column D (this is working just fine by itself):
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
End Sub
CodePudding user response：
One possible answer...
Try this:
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
Changes I made:
- There was inconsistency in the string where you use "BASE" vs. "Base", and changed that.
- I flipped the order of the two primary loops because if you did them in the orignal order, you change column K before you can do the test within you
Select Casestatement. - The line
d = d & " " & c.Offset(, 1)should bed = d & " " & d.Offset(, 1)(did you just update this? I don't see it now, but I swear I was getting this as the object var not set problem).
Anyway, it seems to work on my end as I understand your request.
Updated Answer
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
And here's what it looks like on my end:
