If 2 conditions based in 2 different cells in the same Row, Please correct my code

Question

I need to add a 2nd condition to my code - but I'm getting an error. I'm not sure how to make it work.

If Len = 3 and UPC is blank, then move the Group Code left 1 column. I've got the action part resolved. its the second condition that has me stumped.

I've tried using 'And' in the same line with the 2 conditions. I've tried what you see below.

The Group codes are in Column C. the UPCs are column E If cells in C, len = 3 AND UPC in same row, column E = "" (blank) then.. proceed otherwise skip.

I added:

Dim UPC As Range
Set UPC = Range("E2:E" & LastRow)
If UPC.Value = "" Then

Here is the code:

'Moving Group Codes
Dim cell As Range
Dim UPC As Range
Set UPC = Range("E2:E" & LastRow)
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
        If UPC.Value = "" Then  '<<<<<<this is where i get my error.
        cell.Cut Destination:=cell.Offset(columnoffset:=-1)
        End If
    End If
Next cell

enter image description here

Is there a question here? If you have a compound condition which needs to be satisfied that can be expressed as "if x and y then do something..." or as "if x then if y then do something..." With both of these constructs, both x and y must be true in order to proceed to do something. (Tip: The reference to yesterday's problem is not very helpful. If that adds context to the question, I'm completely clueless because I haven't seen the referenced question. Perhaps this inquiry should be moved to "yesterday's question, or perhaps you should comprehensively restate the question here.) — Yossi G., Jun 19 '23 at 18:03

score 1 · Accepted Answer · answered Jun 19 '23 at 19:52

Try this:

Dim ws As Worksheet, cell As Range
Dim UPC As Range

Set ws = ActiveSheet
Set UPC = ws.Range("E2:E" & LastRow)
For Each cell In Intersect(ws.Range("C:C"), ws.UsedRange).Cells
    If Len(cell.Value) = 3 Then
        If Len(cell.EntireRow.Columns("E").Value) = 0 Then  '<< check ColE value on same row
            cell.Cut Destination:=cell.Offset(columnoffset:=-1)
        End If
    End If
Next cell

If 2 conditions based in 2 different cells in the same Row, Please correct my code

1 Answers1