VBA Question
 

Hello everyone,
I have the following Code in Excel:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$G$4" Then Exit Sub
Application.Goto Cells.Find(Target, after:=Target)
End Sub

I'm sure you are familiar with this??? Someone gave it to me her
awhile ago. It just lets me type something in a cell and then it goe
and finds whatever I typed on the worksheet.

Okay, here is what I would like my code to do. Instead of going to th
cell that contains that data, I would like it to return the value o
the cell to the right of that cell in the cell underneath the one yo
enter stuff in. Got it?? I know. Also, there is going to be more tha
just one, so how can I get it to return all values to the right of al
values I entered? Whoa, here is an example:

I have a list that is sorted:

DDD John
DDD Mike

EEE AL
EEE Joe
EEE John

GGG Joe

HHH Joe

Okay, So I type into Cell G4 (going back to the code) "DDD". I wan
Cells G6 and G7 to display John and Mike, respectively. Thank you s
much

--
Message posted from http://www.ExcelForum.com

VBA Question
 

Scottmk

Try this code




Private Sub Worksheet_Change(ByVal Target As Range)
Dim v(1) As Variant
Dim iCol As Integer

If Target.Address = "$G$4" Then
Application.EnableEvents = False
v(1) = Target.Address
'get next free column in row 5
iCol = Cells(5, Columns.Count).End(xlToLeft).Column
' clear used cell row 5 column H to ???
If iCol 8 Then
Cells(5, "h").Resize(, iCol - 7).ClearContents
Else
Cells(5, "h").ClearContents
End If
' find & list all matches
Do
v(0) = Cells.Find(Target, after:=Range(v(1))).Address
If v(0) = Target.Address Or v(0) = v(1) Then
Exit Do
End If
iCol = Cells(5, Columns.Count).End(xlToLeft).Column + 1
If iCol < 8 Then
iCol = 8
End If
Cells(5, iCol).Value = Range(v(0)).Offset(, 1).Value
v(1) = v(0)
Loop
End If
Application.EnableEvents = True
End Sub



If code crashes you may need to run this code to enable change event.


Sub ddd()
Application.EnableEvents = True
End Su

--
Message posted from http://www.ExcelForum.com

VBA Question
 

Thanks...that did it. Would it be possible to change it for me so tha
it would list the results vertically instead of across in the row?
Thanks, my VBA book should be here tomorrow...I can't wait to figur
out what all this stuff means

--
Message posted from http://www.ExcelForum.com

VBA Question
 

Also, I don't know it the "code crashed" or what, but I typed in a
invalid entry (something I knew that wasn't on the sheet) to see wha
would happen and now it no longer works. I tried entering the othe
code you gave me to fix it but it didn't help. Thank

--
Message posted from http://www.ExcelForum.com

VBA Question
 

Scottmk

try



Private Sub Worksheet_Change(ByVal Target As Range)
Dim v(1) As Variant
Dim lRow As Long

If Target.Address = "$G$4" Then
Application.EnableEvents = False
v(1) = Target.Address
'get next free column in row 5
lRow = Cells(Rows.Count, "h").End(xlUp).Row
' clear used cell row 5 to ??? column H
If lRow 5 Then
Cells(5, "h").Resize(lRow - 4).ClearContents
Else
Cells(5, "h").ClearContents
End If
' find & list all matches
Do
v(0) = Cells.Find(Target, after:=Range(v(1))).Address
If v(0) = Target.Address Or v(0) = v(1) Then
Exit Do
End If
lRow = Cells(Rows.Count, "h").End(xlUp).Row + 1
If lRow < 5 Then
lRow = 5
End If
Cells(lRow, "h").Value = Range(v(0)).Offset(, 1).Value
v(1) = v(0)
Loop
End If
Application.EnableEvents = True
End Su

--
Message posted from http://www.ExcelForum.com

VBA Question
 

Thanks alot Mudraker....looks like that Fixed everything!

--
Message posted from http://www.ExcelForum.com