Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
I need a macro for two different problems. I need one that will copy data
from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
Sub CopyIf()
Dim ws1 as Worksheet Dim ws2 as Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") If ws1.Range("A1") = ws2.Range("A1") Then ws1.Range("D1").Copy ws2.Range("D1") End If End Sub Sub CalcAge() Dim r as Range On error resume next Set r = Application.InputBox("Please Select Cell",Type:=8) if r is nothing then exit sub r.Offset(,1) = Format(Now() - r,"yy") -- Charles Chickering "A good example is twice the value of good advice." "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
If you not familiar with macros, then perhaps a formula would be better.
IN D1 of Sheet2 =IF(A1<"",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLO OKUP(A1,Sheet1!A:D,4,FALSE),""),"") then drag fill down the column. To calculate an Age, use the DateDif worksheet function. It is only documented in the xl2000 help, but has been around for all versions and still is. See Chip Pearson's site for documentation and how to use it. http://www.cpearson.com/excel/datedif.htm -- Regards, Tom Ogilvy "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
the datedif function worked, but for the first function, what do I do if the
data in column a that i'm matching is on different rows, ie: data in a2.sht1 could be in a5.sht2, and data in a3.sht1 could be in column a: any row.sht2 do you understand what I'm trying to say? no correlation in how far/close the data in sht2 is. "Tom Ogilvy" wrote: If you not familiar with macros, then perhaps a formula would be better. IN D1 of Sheet2 =IF(A1<"",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLO OKUP(A1,Sheet1!A:D,4,FALSE),""),"") then drag fill down the column. To calculate an Age, use the DateDif worksheet function. It is only documented in the xl2000 help, but has been around for all versions and still is. See Chip Pearson's site for documentation and how to use it. http://www.cpearson.com/excel/datedif.htm -- Regards, Tom Ogilvy "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
the calcage i couldn't get to work, and the copyif would only work if the
information were on the same row in both worksheets. is there a way to do it if the info is on different rows? "Charles Chickering" wrote: Sub CopyIf() Dim ws1 as Worksheet Dim ws2 as Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") If ws1.Range("A1") = ws2.Range("A1") Then ws1.Range("D1").Copy ws2.Range("D1") End If End Sub Sub CalcAge() Dim r as Range On error resume next Set r = Application.InputBox("Please Select Cell",Type:=8) if r is nothing then exit sub r.Offset(,1) = Format(Now() - r,"yy") -- Charles Chickering "A good example is twice the value of good advice." "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
Nor does the formula assume any correlation between rows between the sheets.
That is why the match and vlookup functions are in the formula. -- Regards, Tom Ogilvy "Marie" wrote: the datedif function worked, but for the first function, what do I do if the data in column a that i'm matching is on different rows, ie: data in a2.sht1 could be in a5.sht2, and data in a3.sht1 could be in column a: any row.sht2 do you understand what I'm trying to say? no correlation in how far/close the data in sht2 is. "Tom Ogilvy" wrote: If you not familiar with macros, then perhaps a formula would be better. IN D1 of Sheet2 =IF(A1<"",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLO OKUP(A1,Sheet1!A:D,4,FALSE),""),"") then drag fill down the column. To calculate an Age, use the DateDif worksheet function. It is only documented in the xl2000 help, but has been around for all versions and still is. See Chip Pearson's site for documentation and how to use it. http://www.cpearson.com/excel/datedif.htm -- Regards, Tom Ogilvy "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
i still can't get it to work. can you explain each piece to me to help me
understand what it is saying? my data that I want to copy and paste is in the same column (E) in both worksheets. Row A is a header row in both. but the rest of the rows don't match and I need the function to go through the whole worksheet. "Tom Ogilvy" wrote: Nor does the formula assume any correlation between rows between the sheets. That is why the match and vlookup functions are in the formula. -- Regards, Tom Ogilvy "Marie" wrote: the datedif function worked, but for the first function, what do I do if the data in column a that i'm matching is on different rows, ie: data in a2.sht1 could be in a5.sht2, and data in a3.sht1 could be in column a: any row.sht2 do you understand what I'm trying to say? no correlation in how far/close the data in sht2 is. "Tom Ogilvy" wrote: If you not familiar with macros, then perhaps a formula would be better. IN D1 of Sheet2 =IF(A1<"",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLO OKUP(A1,Sheet1!A:D,4,FALSE),""),"") then drag fill down the column. To calculate an Age, use the DateDif worksheet function. It is only documented in the xl2000 help, but has been around for all versions and still is. See Chip Pearson's site for documentation and how to use it. http://www.cpearson.com/excel/datedif.htm -- Regards, Tom Ogilvy "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macros
The formula was written for D as originally specified.
Look in the Excel help at Vlookup and Match. -- Regards, Tom Ogilvy "Marie" wrote: i still can't get it to work. can you explain each piece to me to help me understand what it is saying? my data that I want to copy and paste is in the same column (E) in both worksheets. Row A is a header row in both. but the rest of the rows don't match and I need the function to go through the whole worksheet. "Tom Ogilvy" wrote: Nor does the formula assume any correlation between rows between the sheets. That is why the match and vlookup functions are in the formula. -- Regards, Tom Ogilvy "Marie" wrote: the datedif function worked, but for the first function, what do I do if the data in column a that i'm matching is on different rows, ie: data in a2.sht1 could be in a5.sht2, and data in a3.sht1 could be in column a: any row.sht2 do you understand what I'm trying to say? no correlation in how far/close the data in sht2 is. "Tom Ogilvy" wrote: If you not familiar with macros, then perhaps a formula would be better. IN D1 of Sheet2 =IF(A1<"",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLO OKUP(A1,Sheet1!A:D,4,FALSE),""),"") then drag fill down the column. To calculate an Age, use the DateDif worksheet function. It is only documented in the xl2000 help, but has been around for all versions and still is. See Chip Pearson's site for documentation and how to use it. http://www.cpearson.com/excel/datedif.htm -- Regards, Tom Ogilvy "Marie" wrote: I need a macro for two different problems. I need one that will copy data from one spreadsheet to another in such that if a1.sht1 = a1.sht2 then copy data from d1.sht1 to d1.sht2. I also need one that can calculate an age from a date so that if c5= 7-Mar-80 then d5= 26. I'm new to macros and am not sure on how to program this. I'm using Excel 2003. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Making excel macros run Word macros | Excel Programming |