Macro message box.

[puiuluipui]

Hi, i have this macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "SEARCH" And Sh.Name <> "PLANNING" Then
If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then
If Trim(Target.Text) <> "" Then
Application.EnableEvents = False
If WorksheetFunction.CountIf(Worksheets("PLANNING").Range("J4:J10"), _
Split(Target.Value)(0)) > 0 Then
MsgBox "NO COLABORATION", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
Application.EnableEvents = True
End If
End If
End If
End Sub

I need to extract "NO COLABORATION" from range K4:K10. I don't want to have
"NO COLABORATION" in macro. i need to transform this:
MsgBox "NO COLABORATION", vbExclamation, ""
into something like this:
MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _

Can this be done?
Thanks!

 

[Atishoo]

are you wanting to find the string "no collaboration in the range and then
return it? this would make no difference to wht you already have! Or are you
wanting to return the value in the first or last non blank cell in the range
(Worksheets("PLANNING").Range("K4:K10")),?

 

[JLatham]

It depends on some other cell's condition creates "NO COLABORATION" in column
K. You could do something like this - example assumes column D has some
value that causes "N C" to show up in column K:

Dim searchRange As Range
Dim anySearchCell As Range
....other If tests...
Set searchRange = ActiveSheet.Range("D410")
For Each anySearchCell In searchRange
If anySearchCell = condition to cause "NO COLABORATION" in K on the row
MsgBox anySearchCell.Offset(0,8), vbExclamation, ""
Range ("C" & anySearchCell.Row).Activate
End If
.... more code

 

[puiuluipui]

Hi, i have this range:
B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i
will write "John". after i click enter the macro is searching in J4:J10 for
"John" and the macro display the message.
I need the macro to display the message next to "John" in range K4:K10.
J4:J10 and K4:K10 are in "PLANNING" sheet.

Ex:
In B5 i write John
J7=john
K7= No colaboration.
I need the macro to display a msgbox with message from the cell next to
John. John in J7, message is in J7.

If the macro find John in another cell, let's say J9, then to display
message from K9.
In every cell range K4:K10, i will have another message
In range J4:J10, there will be no duplicates.
I need to extract message from range.

Can this be done?
Thanks!

"JLatham" a scris:

 

[puiuluipui]

Hi, i have this range:
B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i
will write "John". after i click enter the macro is searching in J4:J10 for
"John" and the macro display the message.
I need the macro to display the message next to "John" in range K4:K10.
J4:J10 and K4:K10 are in "PLANNING" sheet.

Ex:
In B5 i write John
J7=john
K7= No colaboration.
I need the macro to display a msgbox with message from the cell next to
John. John in J7, message is in J7.

If the macro find John in another cell, let's say J9, then to display
message from K9.
In every cell range K4:K10, i will have another message
In range J4:J10, there will be no duplicates.
I need to extract message from range.

Can this be done?
Thanks!

"puiuluipui" a scris:

 

[Don Guillett]

Modify to suit
Sub findtext()
For i = 1 To Sheets.Count
MsgBox Sheets(i).Name
Set myfind = Sheets(Sheets(i).Name).Columns("j") _
..Find(What:="john", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not myfind Is Nothing Then
myfind.Offset(, 1) = "hello"
'MsgBox myfind.Row
End If
Next i
End Sub

 

[puiuluipui]

Hi Don, i will have many names in J4J:10.
Ex
J4=ANNE K4=A MESSAGE 1
J5=JOHN K5=A MESSAGE 2
J6=JIM K6=A MESSAGE 3
J7=CRIS K7=A MESSAGE 4

When i write a name in range B5:B120, in any sheet except sheet "PLANNING"
and "SEARCH", then the macro to search for that name in sheet "PLANNING" in
range J4J:10 and to display the message next to that name. Message is located
in next colum, in K4:K10.
The macro i have, (in my first post) is very good, except that it display a
message for every name, and the message is inside macro. I have a list with
names and i need to display a message for every name. And i need to have
names and their message in a table so, i can modify them as needs.
Can this be done?
Thanks!

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

Will not be able to look until later today

 

[JLatham]

Don,
I'm assuming he probably did, or will, take you up on your offer. Take a
look at my earlier post and his reply to it. I think my code was almost
there, although you could use FIND vs the loop, it just needs to NOT exit the
loop when it finds the 1st match, and it needs to work through several
worksheets ... which I think his original code may already do.
Jerry

 

[JLatham]

If I understand what you've written correctly, then this should work for you.
These lines of code would replace your If WorksheetFunction.CountIF(... End
If block of code:

Dim searchRange As Range
Dim anySearchCell As Range
....other If tests...
Set searchRange = ActiveSheet.Range("J4:J10")
For Each anySearchCell In searchRange
If anySearchCell.Value = Target.value
MsgBox anySearchCell.Value & ": " & _
anySearchCell.Offset(0,1).Value, vbExclamation, ""
End If
Next
Range ("C" & anySearchCell.Row).Activate

 

[Don Guillett]

Actually it was much easier than that. Turns out this works. Of course, FIND
& offset could also be used.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Employee info" or Sh.Name = "DB" Then exit sub
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) = "" Then exit sub
MsgBox Application.VLookup(Target, Sheets("DB").Range("f1:g21"), 2, 0)
End If
End Sub
=====
Using FIND
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Employee info" Or Sh.Name = "DB" Then Exit Sub
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) = "" Then Exit Sub
Set mv = Sheets("DB").Columns("F").Find(Target, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not mv Is Nothing Then
MsgBox mv.Offset(, 1)
Else
MsgBox "No Match"
End If
End If
End Sub