Multiple And Condition is Returning Wrong Answer

[YellowLabPro]

I have a multiple IF/And condition below. In the current case I am testing, the value in the offset is 0, so this should return the value "Out of Stock", but is executing the "Then" portion of the IF condition and returning "Get Image".

[vba]'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If greater than zero, "Get Image"

If cel.Value.Offset(1, 0).Value Like cel.Value & "*" And cel.Offset(0, 3).Value = "found" _
And cel.Offset(1, 12).Value > 0 Then
cel.Offset(1, 3).Value = "Get Image"

'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If less than 1, then Note: "Out of Stock"

Else
If cel.Value.Offset(1, 0).Value Like cel.Value & "*" And cel.Offset(0, 3).Value = "found" _
And cel.Offset(1, 12).Value < 1 Then
cel.Offset(1, 3).Value = "Out of Stock"
End If
[/vba]

It should evaluate to Positive, Positive, Negative.

Mousing over the part of the expression below in the editor, intellisense gives me the value of "0".
[vba]
cel.Offset(1, 12)
[/vba]

[Bob Phillips]

Something missing here Doug.

[YellowLabPro]

Yes, sorry... thought I had it in there.... I just finished updating the post.

[Bob Phillips]

Very odd. It works fine for me.

[YellowLabPro]

You're kidding.... hmmmmm-I wonder if it is a memory thing.
When I have large amounts of data heavily laiden instructions don't execute until I reboot.

Can I look at anything in the Immediate window or Locals window that could give me better insight? The results are consistent down the entire sheet, although incorrect.

I cannot reboot, or don't want to if at all possible. I am downloading images from my server, I have about another 15 hours to go and hate to have to sync them.

-----------------------------------------------------------------------

Bob,
After I resolve this I need to solve for the bigger issue, I have just been setting up the framework currently to work out the details. This relates back to what I was working on that you were helping me with yesterday. I am taking a different approach, I failed to identify an important piece of criteria initally.
Instead of grouping the records that have a qty greater than zero, I really need to identify which records of the group have a qty greater than zero and then identify these individually.

What I need to do is compare the value(s) of Sheet1 which do not contain any details about the records, to the values of Sheet2 which do possess the details of the records.
So for a specific example-
3256305BW on Sheet1
Locate this value on Sheet2, if found, look below it at all of its subordinate values, 3256305BW10, 3256305BW11.... and do something.

We did something similar awhile back where we identified all the values of a group within a range and processed these records w/in the group, sort of a subset of the entire range and then moved onto the next group.

We did this w/ For i = iStart to iEnd.
I just cannot see how to use this w/ the expression-
[VBA]If cel.Value.Offset(1, 0).Value Like cel.Value & "*" [/VBA]
I know it is there... just can't solidify it

Would you have time to assist me w/ this?



But one thing is consistent between each approach.

[Bob Phillips]

Doug,

Can you reduce the code to a much simpler workbook and see if it still fails?

On the other bit, wouldn't it be something like

[vba]

For i = StartRow To LastRow
iStart = i
j = 0
Do
j = j + 1
fEnded = Not Cells(i, col).Offset(j, 0).Value Like Cells(i, col).Value & "*"
Loop Until fEnded
iEnd = iStart + j - 1
i = iEnd
'the data is then in Cells(iStart, col).Resize(iEnd - iStart +1)
Next i
[/vba]

[YellowLabPro]

Bob,
I had been waiting to see if you would have time to help. So been studying the old code you gave me. Well, you had already responded well over an hour ago.... never got an update on my email client, rrrrrghhh, oh well gave me a chance to grind through the old code. Thanks.

Yes that looks like you are on the right track.

[YellowLabPro]

Hate to ask, feel like I should know the answer, but don't. Would I wrap your code around mine to incorporate it?

[Bob Phillips]

Yeah. My code was just an outline to show you how to get at the group of related cells.

The commented bit is where you would add you action specific code.

[YellowLabPro]

I altered two variables, StartRow and LastRow to iStartRow and iLastRow.
From setting it up and watching in the Locals window along w/ generally not having a good feeling about this- I think I have butchered it.
I clearly don't understand it. I do comprehend what the idea is behind it, not the parts of it.

[vba]
Sub Macro2()
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim c As Range, colA As Range, cel As Range, rng As Range
Dim i As Long, Lrow As Long, iStartRow As Long, iLastRow As Long
Dim iStart As Long, iEnd As Long, fEnded As Long, j As Long, col As Long

Set wsSource = Workbooks("Missing Images List.xls").Worksheets("MissingImages")
Set wsTarget = Workbooks("Complete_Upload_File_Green.xls").Worksheets("EC Products")
Set colA = wsTarget.Columns("A")
Lrow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = wsSource.Range("A4:A" & Lrow)
For Each c In rng
On Error Resume Next
Set cel = colA.Find(c.Value)
cel.Offset(0, 3).Value = "Found"
For i = iStartRow To iLastRow
iStart = 4
j = 0
Do
j = j + 1
fEnded = Not Cells(i, col).Offset(j, 0).Value Like Cells(i, col).Value & "*"


'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If greater than zero, "Get Image"
If cel.Value.Offset(1, 0).Value Like cel.Value & "*" And cel.Offset(0, 3).Value = "found" _
And cel.Offset(1, 12).Value > 0 Then
cel.Offset(1, 3).Value = "Get Image"

'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If less than 1, then Note: "Out of Stock"
Else
If cel.Value.Offset(1, 0).Value Like cel.Value & "*" And cel.Offset(0, 3).Value = "found" _
And cel.Offset(1, 12).Value < 1 Then
cel.Offset(1, 3).Value = "Out of Stock"
End If

If Err.Number = 91 Then
c.Offset(0, 4).Value = "Deprecated Product"
End If
End If
On Error GoTo 0
Loop Until fEnded
iEnd = iStart + j - 1
i = iEnd
'the data is then in Cells(iStart, col).Resize(iEnd - iStart +1)
Next i
Next c
End Sub
[/vba]

[Bob Phillips]

Doug, why have you added the For Each loop on top of the For i = start to end?

and where doe the range c get set?

[YellowLabPro]

I had set up a For Each Loop previously to look at all the values on the wsSource that will relate to the values on the wsTarget sheet.

The c range is on wsSource. The index values are in the c range.

How I was thinking about this in plain english was to take each value on Sheet1,(wsSource), and find the same value on Sheet2, (wsTarget).
Mark that value on wsTarget as found, if not found then mark it on wsSource as deprecated.

Then I recalled how you handled these subsets of values in a previous procedure. So I started to think about how to process the items related in the small groups.
That is how I got to where we are.
Why I put anything where is no intelligent reason, honestly. It was trying to combine what I sort of know to what you had shown me before.
I guess I made a mess of it now.

[Bob Phillips]

Okay, I think I get it.

Not sure if this is right, not having the data. I think the 'Found' text may be being added to the wrong sheet, but maybe we have progress

[vba]

Sub Macro2()
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim c As Range, colA As Range, cel As Range, rng As Range
Dim i As Long, Lrow As Long, iStartRow As Long, iLastRow As Long
Dim iStart As Long, iEnd As Long, fEnded As Long, j As Long, col As Long

Set wsSource = Worksheets("MissingImages") 'Workbooks("Missing Images List.xls").Worksheets("MissingImages")
Set wsTarget = Worksheets("EC Products") 'Workbooks("Complete_Upload_File_Green.xls").Worksheets("EC Products")
Set colA = wsTarget.Columns("A")
Lrow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = wsSource.Range("A4:A" & Lrow)
For Each c In rng
On Error Resume Next
Set cel = colA.Find(c.Value)
cel.Offset(0, 3).Value = "Found"
iStart = cel.Row
j = 0
Do
j = j + 1
fEnded = Not cel.Offset(j, 0).Value Like cel.Value & "*"

If Not fEnded Then
'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If greater than zero, "Get Image"
If cel.Value.Offset(1, 0).Value Like cel.Value & "*" And _
cel.Offset(0, 3).Value = "found" And _
cel.Offset(1, 12).Value > 0 Then
cel.Offset(1, 3).Value = "Get Image"

'Compares the value below the index ("Parent") value, if it finds it and is a match,
'then look at its ("Child")quantity. If less than 1, then Note: "Out of Stock"
ElseIf cel.Value.Offset(1, 0).Value Like cel.Value & "*" And _
cel.Offset(0, 3).Value = "found" And _
cel.Offset(1, 12).Value < 1 Then
cel.Offset(1, 3).Value = "Out of Stock"
End If
End If
Loop Until fEnded

If Err.Number = 91 Then
c.Offset(0, 4).Value = "Deprecated Product"
End If
On Error GoTo 0
Next c
End Sub
[/vba]

[YellowLabPro]

Thanks Bob!!!!!!
I will go through this here in a little bit.
I have to deal w/ the shop at the moment.

[Bob Phillips]

Well not tonight, snooze time over here.