IIF Statement to pull true or false from 3 columns and put into a column called ship.

[Huffner1]

Hello,

I have a table called Analysis Parameters. The table has 3 columns South North East And West.



Sku Number

South

North

East

West



4276

Yes

No

Yes

Yes



10178

No

Yes

No

Yes



368758

Yes

Yes

Yes

Yes














I have another table called RL. This table has






Store Number

Sku Number

Ship



31

4276

South Yes/No



31

10178

North Yes/No



31

368758

East Yes/No









What I need to do, is as follows. I need to compare the Sku Numbers from both tables. If the Sku Numbers match in both tables, then I need to be able to put a yes or no into the Ship Column.The Yes/No will come from The Analysis Parameters Table. As you can see I have store # 31 which has 3 different Sku Numbers. I need to put yes/No into the ship column for all 3 of the sku numbers.


If pa!SKU = rl![SKU Number] Then

Any Suggestion would be very helpful. Inherited this and need to figure it out.

If you need more information please let me know.

Thanks,

Ken

[mrojas]

If the Sku number matches, which value from Analysis Parameter's table do you want to update the Ship field in the RL?

[EirikDaude]

Is it something like this you are looking for?


Sub test()
Dim c As Range, r As Range, foundInCell As Range

' First cell in column with sku numbers
Set r = Worksheets("Analysis parameters").Range("A1")
' Expand to last cell in column
Set r = Range(r, r.Offset.End(xlDown))

For Each c In r
' Find the matching sku number
foundInCell = Worksheets("RL").Range("Sku numbers").Find(c.Value, LookIn:=xlValues, MatchCase:=True)
' If a match is found, copy the yes/no values to the adjacent cells
If Not foundInCell Is Nothing Then
Range(c.Offset(0, 1), c.Offset(0, 4)).Copy (foundInCell.Offset(0, 1))
End If
Next
End Sub


- edit - I just noticed that this wasn't in the Excel-part of the forums, which probably makes the code I posted useless :doh: Very sorry about that, and please don't mind me >_>