Coding To Populate Cells Based On Date 1

[TheMus]

Hello,

I've got a spreadsheet with dates across the top (03/01...03/30) and each day I pull data to the hard drive that I want to populate in the right cells according to the date.

I recorded a macro that opens the data files, the master sheet and transfers the data from the data files to the master sheet, but the data always populates in the same column. So, regardless of the date each time the macro is run, the data populates in the same column. How can I code the macro to populate the data in the right cell based on the system date?

Thanks in advance,

Rus

 

[Chance1234]

try this , this will get the column reference for the date you want

Sub bob()

Dim Myrange As Range
Dim Mydate As Date
Dim TstVal As Date

Mydate = #1/1/1900# 'the date you are looking for could be =date() for todays date

Set Myrange = Worksheets(1).Range("C9:j9&quot ' the range of cells that contains your column headings

For Each Var In Myrange
TstVal = Range(Var.Address).Value
If TstVal = Mydate Then
MsgBox Var.Column & " This is the column you want to dump your data in"

End If




Next

End Sub

 

[SkipVought]

Hi, TheMus,

You have two problems, really. One is a database/storage problem and the other is a reporting problem. Quite often the CORRECT structure of a database does not resemble the structure of a report. In fact, a database may have data that can be reported in a variety of ways an in views consistant with different needs. If a database is constructed for a report or specific view perspective, it is difficult to maintain and even more difficult to accomodate other views. What you are describing is a non-normalized database.

Not knowing the nature of your data, I cannot make specific recommendations. But let me make some assumptions from the information at hand.

Assume that for each set of data you have a DATE, ITEM and VALUE. I am assuming a numeric value. WIth this information, your database/storage table would have 3 elements/columns: DATE, ITEM, VALUE.

Now your report, using the database as a source, will show rows by ITEM and columns, grouped by DATE (day as opposed to month, quarter, year), with summed VALUE at the intersection of ITEM and DATE. This can be accomplished by the Pivot Table Wizard and if you desire to pursue this avenue, I will provide some assistance.

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[TheMus]

Thanks Chance...I'm on my way. How would I paste the value from another spreadsheet (file "1gar.xls" Cell "B2" and "B9&quot to the Var.Column& and a specific Row in the master (file "Interim_Data&quot?

The column will change each day based on the date but the row I populate the data in will be constant. For example, date = 0318 the cell would be "T3", date = 0319 then the cell would be "U3", the date = 0320 the cell would be "V3".

 

[TheMus]

Chance you're great, it works! Skip thanks for your response.

 

[Chance1234]

plese feel free to give me a star

 

[TheMus]

Chance, I'll need some direction on how to give you a star.

 

[Chance1234]

on my replys in the bottom left hand corner it says

Click here to mark this post as a helpful or expert post!

just click that

 

[SkipVought]

TheMus,

When a contributor has made, what you believe to be a helpful or expert posting, you can recognize such a contribution by clicking on the hyperlink in the lower, left-hand corner of that contributor's posting, where it says...
Click here to mark this post as a helpful or expert post!

Skip,
metzgsk@voughtaircraft.com