MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel Macro

Posted by Arthur on March 20, 2001 6:21 AM

Would anyone know how, when a macro is run, it looks at contents of a specific cell, if there is nothing there then the current date is put in there, and under neath that cell there will be 3 numbers put from another 3 cells that are linked to an access database.

Here is what I curently have:

a1,a2,a3 are linked to access queries that generates specific numbers,

in excel, I link 3 tables to those queries.

Next what I want to do is:

b1 contains today's date

b1:03/20/01
b2: contents from a1
b3: contents from a2
b4: contents from a3

When I run the macro tomorrow I would like the date to be created in c1:
and the most latest #'s that are generated from the query to be put in c2,c3,c4.

Does anyone have any idea of how I could accomplish this?

Thanks

Posted by Celia on March 20, 2001 3:42 PM

Arthur
Try this :-
Sub Daily_Update()
Dim sr As Range, dte As Range, col%

Set sr = Range("A1:A3")
Set dte = Range("IV1").End(xlToLeft).Offset(0, 1)

col = dte.Column - sr.Column
dte.Value = Date
sr.Copy sr.Offset(1, col)
End Sub

Celia

Posted by Celia on March 20, 2001 3:52 PM

Correction


Sub Daily_Update()
Dim sr As Range, dte As Range, col%

Set sr = Range("A1:A3")
Set dte = Range("IV1").End(xlToLeft).Offset(0, 1)

col = dte.Column - sr.Column
dte.Value = Date
sr.Copy
sr.Offset(1, col).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub