find and return fixed value
 

Hello all,

I have a question about the find option.

I have a sheet wich has some value in column B. I want to find some
different value's in there and give a fixed respons to the next cell
(in column c). First i have a problem that i don't now how to select
one cell to the right to give the output there.And second, sometimes
there is allready a value in column C which can not be overwrite but
the new value have to place right after it. Here an example


B C
Piet Henk;Klaas;Peter;
Dirk Rene;Joke;Vera;

so find piet and when found piet always give as response
Henk;Klaas;Peter.

But sometimes behind B there is allready an value in column C. If that
is true this wil be the result output.


B C
Piet Rene;Henk;Klaas;Peter;

The allready fillt in values in column C are variable, so there also
could have bin a Vera instead of Rene in Column C.

I hope somone can point me in the right direction.

Thanks allready

find and return fixed value
 

I don't know how you know what to put in that adjacent cell.

But maybe something like this will give you a start...

Option Explicit
Sub Testme01()

Dim FoundCell As Range
Dim WhatToFind As String
Dim WhatToAdd As String

WhatToFind = "Piet"
WhatToAdd = "Peter"

With ActiveSheet.Columns(1)

Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "not found"
Else
With FoundCell.Offset(0, 1)
.Value = .Value & WhatToAdd & ";"
End With
End If

End Sub

(If there's nothing in that adjacent cell, then the .value is "", so appending
would work fine.)


Chris wrote:

Hello all,

I have a question about the find option.

I have a sheet wich has some value in column B. I want to find some
different value's in there and give a fixed respons to the next cell
(in column c). First i have a problem that i don't now how to select
one cell to the right to give the output there.And second, sometimes
there is allready a value in column C which can not be overwrite but
the new value have to place right after it. Here an example

B C
Piet Henk;Klaas;Peter;
Dirk Rene;Joke;Vera;

so find piet and when found piet always give as response
Henk;Klaas;Peter.

But sometimes behind B there is allready an value in column C. If that
is true this wil be the result output.

B C
Piet Rene;Henk;Klaas;Peter;

The allready fillt in values in column C are variable, so there also
could have bin a Vera instead of Rene in Column C.

I hope somone can point me in the right direction.

Thanks allready

--

Dave Peterson