How to automate a Spread Sheet?

davey11372

Board Regular
Joined
Dec 19, 2009
Messages
56
<!-- / 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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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
 

davey11372

Board Regular
Joined
Dec 19, 2009
Messages
56

ADVERTISEMENT

Thank you.

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Dec 19, 2009
Messages
56

ADVERTISEMENT

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

Board Regular
Joined
Dec 19, 2009
Messages
56
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You should just need to make the change in red

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,762
Messages
5,597,980
Members
414,199
Latest member
AMI_MINT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top