Solved: Look for Values in Range

[mykal66]

I need VBA to look at a range in a workbook A7:A20 for several values that should not be there e.g. xxxx or xxxx or xxxxxxxxx before the sheet can be printed

If any of the values are found within the range then i need the cell to be highlighted and a message-box advising the user to correct the entry before they are allowed to print. I need it to keep checking until they clear all instances on the list

I've tried a few pieces of code from the Internet but cant adapt them to work properly so tried macro recorder and did find...

The macro did work for one value only, highlighted the relevant cell and brought up a message box however, if the value was not found i get an error and it will only look for one value. This is what i have tried last

Cells.Find(What:="xxxx", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
MsgBox ("You cannot print your letter until you amend this phrase")

Can anyone help please

[Bob Phillips]

Sub CheckInput()
Dim vecCheck(1 To 3) As String '<<<<< resize array
Dim cell As Range
Dim firstaddress As String
Dim msg As String
Dim i As Long

vecCheck(1) = "xxxx"
vecCheck(2) = "yyyy"
vecCheck(3) = "zzzz"

For i = LBound(vecCheck) To UBound(vecCheck)

Set cell = Nothing
Set cell = Cells.Find(What:=vecCheck(i), _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then

firstaddress = cell.Address
msg = msg & cell.Value
Do

msg = msg & vbTab & cell.Address & vbNewLine
Set cell = Cells.FindNext(cell)
Loop Until cell.Address = firstaddress

msg = msg & vbNewLine
End If
Next i

If Not msg = "" Then

MsgBox "Correct the following input errors:" & vbNewLine & vbNewLine & msg, vbOKOnly, "Input errors"
End If
End Sub

[mykal66]

Hi

Thanks so much for your help, this worked perfectly and what you provided was better much better than i was originally planning in that it relayed all errors in one message.

Really appreciated, thanks again. Mykal

[snb]

or a oneliner approach:


Sub tst()
MsgBox "Restore " & vblf & Join(Filter([transpose(if(A7:A20="xx",address(row(A7:A20),column(A7:A20)),""))], "A"), vbLf)
End Sub

[mykal66]

Hi XLD.

Sorry to ask for more but I've tried to amend the code you gave me and can't figure out how to change it to look for the same strings within a cell.

I tested the code you gave me yesterday and it worked brilliantly because i only tested the exact strings in a cell on there own.

The spreadsheet allows users to use preselected phrases to populate a letter and the random text (xxxx, yyyy, zzzz) is used in the middle of a phrase where additional information needs added manually after the letter is created. The issue has been is that some people have not been amending the letter and sending it with the random text still showing.

I really like your code from yesterday where any of the dodge strings are highlighted in the msgbox (and didn't know this was possible) and didn't even think about it not working when the text was in the middle of other text.

Basically i want to use your code to prevent them printing th letter until they amend the random text

Thank you. Mykal

[Teeroy]

Hi mykal66,

Try changing LookAt:=xlWhole to LookAt:=xlPart.
This will make the .Find look inside the string in the cell rather than just the whole string.

[mykal66]

Hey Teeroy.

Thank you, simple when you know how. This will force people at work to do it properly now

Thanks to everyone for your help. Mykal