Excel macro to look up data from a database and copy it to another Excel file or sheet

Question

I have an excel file which looks up and calculates data for a given date. It works as follows: You enter the date in a specific cell (input) and in the same sheet the required data appears (20 cells). (the calculations etc... are done in another sheet)

Now I want to create an excel file which summarizes all this data without having to input the date myself and copy the data manually.

What the macro should do is: for each date change the value of that specific input cell, copy the output data and paste it in a new sheet (next to the date).

Any idea how this could be done? I worked with macros a long time ago and forgot most of the syntax.

Answer 1

as i see it, you got an array of input data
dim my_array(10) this is your array, which stores 10 values
let's say, you have 3 worksheets, first is where you input data "Sheet1", second is where you want to save output "Sheet2", and third, where all calculations are done, we won't touch it.
let’s start with filling our array with values:
my_array = Array(1, 2, 3, ..., 9)
now we’ll make a loop to go through all input values and save output values:
for i = 1 to 10
Worksheets(1).Range("A1").Value = my_array(i)
where “A1” is the cell on the first sheet, where you paste your input. let’s assume, that you get output in cell “A2” on sheet1 and you want to store it in collumn “A” in the sheet2:
Worksheets(2).Cells(i, 1).Value = Worksheets(1).Range("A2").Value
don’t forget to close the loop:
Next
so we get the code, that takes 10 hardcoded values and saves the output to collumn “A” on sheet2, full text:
dim my_array(10)
Private Sub macro1()
my_array = Array(1, 2, 3, ..., 9)
for i = 1 to 10
Worksheets(1).Range("A1").Value = my_array(i)
Worksheets(2).Cells(i, 1).Value = Worksheets(1).Range("A2").Value
Next
End sub