How to automate a Spread Sheet?

[davey11372]

<!-- / icon and title --> <!-- message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --> <!-- END TEMPLATE: ad_showthread_firstpost_start --> I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.

Sheet 1, has a list of stocks, indexed in Column A.

Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.

I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size.

Any help would be appreciated

 

[wolverineb]

You should post the VBA code. Just copy and paste it in here with the word code in square brackets [] before and after it (but with a slash / before the second word "code").

Most likely you will need to loop the same code it's running now through all the stock values and record them in column B as it goes.

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Sheets("Sheet2").Range("A1").Value = .Range("A" & i).Value
        .Range("B" & i).Value = Sheets("Sheet2").Range("P6").Value
    Next i
End With
End Sub

 

[wolverineb]

Like that

 

[davey11372]

Thank you.

The workbook has 2 sheets. Where do I copy the code to. And, how do I run it.

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste the code into the white space on the right.

Press ALT + Q to close the VBE, Tools > Macro > Macros, click on test then click the Run button.

 

[davey11372]

VoG, I think I got it to work. Thanks much. I tried it for a few stocks, now I will try it for the full list of hundreds of stocks. Thanks again.

 

[davey11372]

Can I ask you for one quick modification.

Your script works great. Just as I wanted to tabulate the results from P6 on sheet 2, for each stock, to Sheet 1 column B ... can you please add another line that allows me to tabulate the results from Q6 in sheet 2 to colmn C in sheet 1 (exactly the way you did it for P6 to column B). Thanks a ton. I am trying to do it on my own too. Just in case I mess it up.

 

[VoG]

You should just need to make the change in red

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Sheets("Sheet2").Range("A1").Value = .Range("A" & i).Value
        .Range("C" & i).Value = Sheets("Sheet2").Range("P6").Value
    Next i
End With
End Sub

 

[davey11372]

Never mind, I did modify your code, and it works with the addition. Thank you.