Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Hi: I am trying to auto-filter data that will change. Below is what I have
that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Maybe a simple INDEX/MATCH would suffice ?
In Sheet1, Put in N21: =IF(ISNA(MATCH("Doe, Jane",Sheet2!A:A,0)),"",INDEX(Sheet2!C:C,MATCH("Do e, Jane",Sheet2!A:A,0))) Format N21 as time to taste -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote: Hi: I am trying to auto-filter data that will change. Below is what I have that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Max is correct. A worksheet formula solution is best. If, however you need
to do this in the context of a larger macro, then: Sub jeannie() Sheets("worksheet2").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Doe, Jane" Then v = Cells(i, "C").Value Exit For End If Next Sheets("worksheet1").Activate Range("N21").Value = v End Sub You can add a line set set O21 if needed. -- Gary''s Student - gsnu200730 "jeannie v" wrote: Hi: I am trying to auto-filter data that will change. Below is what I have that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Hi Max: You are wonderful! I have decided to go the Macro route, but I you
with great appreciation. -- jeannie v "jeannie v" wrote: Hi: I am trying to auto-filter data that will change. Below is what I have that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Hi GS:
I am indebted to you for your help....It worked perfectly! I have many projects that I want to try for my work and I look forward to your help in the future. You have turned hours of hard work into minutes. Thank you. -- jeannie v "jeannie v" wrote: Hi: I am trying to auto-filter data that will change. Below is what I have that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
Gary thank you for your help...It worked perfectly, now I need help with the
following....Sorry I didn't reply properly previously..... I apologize that I am a real novice in Macros. This is the macro that works great, but I want to tweak it just a little. Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Glover, Ebony" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Ebony G").Activate Range("N21").Value = v I want to create a macro to do almost the same thing, except I want it to retrieve more than 1 field of data for the person...Can I tweak this one to do what I want? I want it to say say: If "Glover, Ebony" is found by auto-filter in Column A from Cell A2:A160 in Worksheet "Agent Login-Logout", take the data in Column A through Column L and copy to Worksheet 1 "Ebony G" in Cells A4 to L14. Any help would be appreciated. -- jeannie v "Gary''s Student" wrote: Max is correct. A worksheet formula solution is best. If, however you need to do this in the context of a larger macro, then: Sub jeannie() Sheets("worksheet2").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Doe, Jane" Then v = Cells(i, "C").Value Exit For End If Next Sheets("worksheet1").Activate Range("N21").Value = v End Sub You can add a line set set O21 if needed. -- Gary''s Student - gsnu200730 "jeannie v" wrote: Hi: I am trying to auto-filter data that will change. Below is what I have that works for the current day. The problem is that Worksheet 2 Column A will not be in the same order each day. Therefore, the data in Column C for the name in Column A will change and will be incorrect on Worksheet 1 the next time I log the new data on Worksheet 2. It seems that it would be so simple, but I can't figure it out....I'm really a novice in Macros, and I would appreciate any help you can give me. I want it to say: If "Doe, Jane" is found by auto-filter in Column A from Cell A2:A60 in Worksheet 2 , Take her Time in Column C and copy to Worksheet 1 in Cells N21:O21. This is what I have so far that works for the current day's data...when I change the data on Worksheet 2, it doesn't pop the correct data on Worksheet 1 because the Rage changes in Column C on Worksheet 2. Selection.AutoFilter Field:=1, Criteria1:="Doe, Jane" Range("C27").Select Application.CutCopyMode = False Selection.Copy Sheets("Jane D").Select ActiveSheet.Paste -- jeannie v |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Data within a Macro
No problem. Do go as preferred.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jeannie v" wrote in message ... Hi Max: You are wonderful! I have decided to go the Macro route, but I you with great appreciation. -- jeannie v |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotaling variable data in a macro | New Users to Excel | |||
PAUSE EXCEL MACRO FOR INPUT OF DATA VARIABLE | Excel Discussion (Misc queries) | |||
Variable Macro | Excel Discussion (Misc queries) | |||
variable range in a macro | Excel Discussion (Misc queries) | |||
Macro - Data Sort -Variable Selection | Excel Discussion (Misc queries) |