VBA Loop If statement

[PVANS]

Good morning,

Please could someone help me with this. I currently have the following
piece of code in my workbook:
Sub ProdCheck()
Dim Answer As String
If Range("B2").Value = "ESX" Then
Range("AB2").Value = 10
'*****this is where the ELSEIF argument of the code should be*****
Else: Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Does this product
exist?")
If Answer = vbNo Then
MsgBox "You pressed NO!"
Range("AB2").Value = 0
Else
MsgBox "You pressed Yes!"
Range("AB2").Value = 1000
End If
End If
End Sub

Whilst the code works, it has two issues with regards to what I am trying to
achieve:
1.) I need it to loop through the whole worksheet performing the if
statement. ie: not just row 2, but row 2-last row on worksheet

2.) in the IF statement itself, I need to include an ELSE IF (see commented
secion in code), and for the code to check whether the value in the cell "B"
it is checking is in ColumnA of worksheet2, and if so for "AB2" = 100, if not
- then continue to the ELSE part of the current code

Please can someone help with this, I am completely stuck

Really appreciate it

Regards,

 

[PVANS]

Sorry, just saw an area of my request that may cause confusion:

2.) in the IF statement itself........... and if so for cell "AB" in the row
it is checking to = 100, if not.........

What I am looking for is for the Cell AB of the each line to either equal
10, 1000, 100 or 0 depending on the results of the code


Thanks again in advance for any help

 

[Mike H]

Hi,

I think i got it.

A couple of points.
1. I created the string MyNote so change to suit
2. This operates on Sheet1 so change that to suit
3. ChkRange is the range on worksheet 2 where we look up the value so change
that to the correct sheet and range


Sub ProdCheck()
Set Sht = Sheets("Sheet1") ' change to suit
Set ChkRange = Sheets("Sheet2").Range("A1:A10000") 'Chng to suit
MyNote = "Some text"
Dim Answer As String
Dim LastRow As Long
Dim C As Range
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B2:B" & LastRow)
For Each C In MyRange
If UCase(C.Value) = "ESX" Then
C.Offset(, 26).Value = 10
ElseIf WorksheetFunction.CountIf(ChkRange, C.Value) > 0 Then
C.Offset(, 26).Value = 100
Else
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, _
"Does this product exist?")
If Answer = vbNo Then
MsgBox "You pressed NO!" 'I think the user knows that
C.Offset(, 26).Value = 0
Else
MsgBox "You pressed Yes!" 'I think the user knows that
C.Offset(, 26).Value = 1000
End If
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[PVANS]

Mike, it works absolutely perfectly.

Thank you so much, that really is brilliant!!!!

Have a good day,

Regards

 

[Mike H]

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.