excel macros

[Marie]

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.

 

[Charles Chickering]

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")

 

[Tom Ogilvy]

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)),VLOOKUP(A1,Sheet1!A,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

 

[Marie]

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.

 

[Marie]

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?

 

[Tom Ogilvy]

Nor does the formula assume any correlation between rows between the sheets.
That is why the match and vlookup functions are in the formula.

 

[Marie]

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]

The formula was written for D as originally specified.

Look in the Excel help at Vlookup and Match.