Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code assistance
I am tring to populate a column of cells when they have focus and all I can
seem to do is populate just one and the kicker is that it only will populate to the second cell in the reference range I have a validation list on sheet1, e2 validated with data from sheet2,a1-a50 and using the code for an advanced filter search I found online, I can get it to work but only on sheet2,e3 and no others in the column. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 5 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Lists").Range("F2").Calculate Worksheets("Lists").Range("AbrevList").AdvancedFil ter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Lists").Range("F1:F2"), _ CopyToRange:=Range("E3"), Unique:=False End If End Sub HELP PLEASE... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code assistance
Your code appears to be OK, so based on the tests that I ran, the
problem might be either how you set up your criteria, or the column that you designate to be filtered, or both. The criteria should consist of the column header on one row of the criteria range and a data element that is or will be contained in that column at the time the macro fires. The filter range is the range that you want data returned from. Since I do not know what you have in F1 and F2, I can not tell which column you are setting the filter criteria for, Nor do I know if Range("AbrevList") is the range you want the filtered data returned from, although I assume it is. If the AbrevList is also your criteria column, it won't work. This probably don't help much but, without seeing your sheet, it is the best I can do. "Mekinnik" wrote: I am tring to populate a column of cells when they have focus and all I can seem to do is populate just one and the kicker is that it only will populate to the second cell in the reference range I have a validation list on sheet1, e2 validated with data from sheet2,a1-a50 and using the code for an advanced filter search I found online, I can get it to work but only on sheet2,e3 and no others in the column. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 5 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Lists").Range("F2").Calculate Worksheets("Lists").Range("AbrevList").AdvancedFil ter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Lists").Range("F1:F2"), _ CopyToRange:=Range("E3"), Unique:=False End If End Sub HELP PLEASE... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code assistance
Correction to my post:
I can only get it to work on sheet1,e3 I think what I need to do is change the following line to include a column range not a cell, but I'm not sure how to do it? CopyToRange:=Range("E3"), Unique:=False I thinking something to the effect of: CopyToRange:=Range.(THE CELL WITH FOCUS), Unique:=False "Mekinnik" wrote: I am tring to populate a column of cells when they have focus and all I can seem to do is populate just one and the kicker is that it only will populate to the second cell in the reference range I have a validation list on sheet1, e2 validated with data from sheet2,a1-a50 and using the code for an advanced filter search I found online, I can get it to work but only on sheet2,e3 and no others in the column. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 5 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Lists").Range("F2").Calculate Worksheets("Lists").Range("AbrevList").AdvancedFil ter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Lists").Range("F1:F2"), _ CopyToRange:=Range("E3"), Unique:=False End If End Sub HELP PLEASE... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code assistance
No, that is not the problem. I got it to return several values down the
column using your original statement. The problem is either in your criteria set up, or your range that your are specifying to return the data from. Again, if you are using the same range for your criteria that you are trying to return data from, it won't work. Your criteria has to be in one column and the data you want to return needs to be in an adjacent column. Then it will return every cell value in the adjacent column that corresponds to a criteria cell in the other column. Unfortunately, the VBA help does not provide a lot of explanation on this. "Mekinnik" wrote: Correction to my post: I can only get it to work on sheet1,e3 I think what I need to do is change the following line to include a column range not a cell, but I'm not sure how to do it? CopyToRange:=Range("E3"), Unique:=False I thinking something to the effect of: CopyToRange:=Range.(THE CELL WITH FOCUS), Unique:=False "Mekinnik" wrote: I am tring to populate a column of cells when they have focus and all I can seem to do is populate just one and the kicker is that it only will populate to the second cell in the reference range I have a validation list on sheet1, e2 validated with data from sheet2,a1-a50 and using the code for an advanced filter search I found online, I can get it to work but only on sheet2,e3 and no others in the column. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 5 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Lists").Range("F2").Calculate Worksheets("Lists").Range("AbrevList").AdvancedFil ter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Lists").Range("F1:F2"), _ CopyToRange:=Range("E3"), Unique:=False End If End Sub HELP PLEASE... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code assistance | Excel Programming | |||
Code assistance | Excel Programming | |||
Assistance with code Please | Excel Discussion (Misc queries) | |||
Code assistance please | Excel Programming | |||
VBA Code Assistance | Excel Programming |