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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,118,662
Messages
5,573,485
Members
412,533
Latest member
Pejter
Top