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
 

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,651
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
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,651
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
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,651
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
 

Forum statistics

Threads
1,081,841
Messages
5,361,638
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top