jezra85
03-01-2011, 10:30 PM
Hi,
I have 2 spreadsheets. 1 contains data that is updated every quarter. the other is a spreadsheet that I update every quarter with the data from the first spreadsheet. What is the best way to match the heading of the column and the date of the row to insert the new data every quarter?
I have attached a sample, but have combined the 2 spreadsheets into 1. The data in column I is what would be in my destination spreadsheet and the data on the left is the live data i update every quarter.
I started working on it and thought of using an array to store the entire table and then compare the titles and dates and locate that value in the array, however this seems too complicated and im not sure how to make it flexible for multiple columns. Any help would be greatly appreciated. thanks
Sub test_array()
Dim myarray As Variant
Dim i, j As Integer
myarray = Range("a1:e6").Value
For j = 1 To UBound(myarray)
If (ActiveSheet.Range("i" & j).Value = myarray(j, 1)) And (ActiveSheet.Range("j1").Value = myarray(1, 2)) Then
ActiveSheet.Range("i" & j).Offset(0, 1).Value = myarray(j, 2)
End If
Next
End Sub
I have 2 spreadsheets. 1 contains data that is updated every quarter. the other is a spreadsheet that I update every quarter with the data from the first spreadsheet. What is the best way to match the heading of the column and the date of the row to insert the new data every quarter?
I have attached a sample, but have combined the 2 spreadsheets into 1. The data in column I is what would be in my destination spreadsheet and the data on the left is the live data i update every quarter.
I started working on it and thought of using an array to store the entire table and then compare the titles and dates and locate that value in the array, however this seems too complicated and im not sure how to make it flexible for multiple columns. Any help would be greatly appreciated. thanks
Sub test_array()
Dim myarray As Variant
Dim i, j As Integer
myarray = Range("a1:e6").Value
For j = 1 To UBound(myarray)
If (ActiveSheet.Range("i" & j).Value = myarray(j, 1)) And (ActiveSheet.Range("j1").Value = myarray(1, 2)) Then
ActiveSheet.Range("i" & j).Offset(0, 1).Value = myarray(j, 2)
End If
Next
End Sub