Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question
Thanks alot Mudraker....looks like that Fixed everything!
-- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Sum question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |