About Us

Steved

Hello Tom From Steved
Thankyou. it is excellent.

"Tom Ogilvy" wrote:

If you are going to have it in a change event - it needs to only process the
changed cell. See revised:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim rng As Range
Dim cell As Range
On Error GoTo ErrHandler
Set rng = Range("S2:S2000")
If Target.Count 1 Then Exit Sub
'for each cell in Range("E6:E255")
If Not Intersect(Target, Range("D6:D255")) Is Nothing Then
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = False
Cells(Target.Row, 5).Value = rng(res).Offset(0, 1) & Chr(10) & _
rng(res).Offset(0, 2)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
'Next
End Sub

Worked for me.

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
Hello Tom From Steved

Tom I put the below in and works but if I type in the second value in Col
D
it freezes the spreadsheet any idea's please

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res as Variant
Dim rng as Range
Dim cell as Range
set rng = Range("S2:S2000")
for each cell in Range("E6:E255")
res = Application.Match(Cell.offset(0,-1),rng,0)
if not iserror(res) then
cell.Value = rng(res).offset(0,1) & chr(10) & _
rng(res).offset(0,2)
end if
Next
End Sub

"Tom Ogilvy" wrote:

Dim res as Variant
Dim rng as Range
Dim cell as Range
set rng = Range("S2:S2000")
for each cell in Range("E6:E255")
res = Application.Match(Cell.offset(0,-1),rng,0)
if not iserror(res) then
cell.Value = rng(res).offset(0,1) & chr(10) & _
rng(res).offset(0,2)
end if
Next

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
Hello from Steved

Is it possible please to run the below in VBA

The formula is in E6 to E255

=IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10)
&VLOOKUP(D6,$S$2:$W$2000,3,0),"")

Thankyou.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Commenting custom formula fields/formula on formula editor	Muxer	Excel Programming	2	July 24th 03 01:02 AM

Menu

About Us