Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

automate row insert to archive

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
tfrentz - 28 Nov 2006 00:25 GMT
I need some help automating a row insert from one sheet to a second sheet.  I
think a macro is needed, but I'm not sure how to set one up as I've never
worked with them, nor do I know how to program it.  I'd appreciate a little
help setting up the macro.

Basically, I retrieve my stock quotes into the Quote sheet, and it updates a
total amount.  I want to archive the current date and amount to a Database
sheet.  But there is an important twist.  If there is already a row inserted
in the Database sheet with the same date, then I only want to update the
amount, not insert another row.  Once the current date changes, then it is
okay to insert another row.

This is my data.  Sheet Quote has the total amount.  The amount is dynamic
and will change whenever my stock quotes update the prices of the securities.
Stock    Price       Amount    
IBM       $91.45    15000
MSFT    $29.48    20000
------    --------    -------
Total                   35000

Sheet Database stores the current date and current balance on the last row,
as well as archived dates and balances going back in time.  Only the last row
with the current date is to be updated.  The data is sorted in ascending date
as you can see.
Date           Amount
11/24/06    30000
11/25/06    33000
11/26/06    34000
11/27/06    35000   ** This row can be updated multiple times
                                 or a new row inserted below if date changes
Martin Fishlock - 28 Nov 2006 01:13 GMT
How do you update your stock quotes sheet?

What happens if you miss a day? Ie you run it on a monday and then on a
wednesday?

There are couple of ways to do it but I will give my comments after comment
on the above.
Signature

Hope this helps
Martin Fishlock

> I need some help automating a row insert from one sheet to a second sheet.  I
> think a macro is needed, but I'm not sure how to set one up as I've never
[quoted text clipped - 26 lines]
> 11/27/06    35000   ** This row can be updated multiple times
>                                   or a new row inserted below if date changes
tfrentz - 28 Nov 2006 01:26 GMT
The stock prices are updated via the MSNStockQuote function add-in.  One
click of the 'Update Quotes' on the toolbar will update all the stock prices,
and this will update the total.

I haven't ran into missed days, but I guess the answer is I don't care if I
happen to miss a day.  Obviously, Saturday's and Sunday's the markets are
closed, so no updates take place.  So on Monday's, there would be two missing
dates for the weekend.  I guess, I could manually calculate the stock prices
using historical data, and then add them up and insert a manual entry to the
Database, but this isn't of prime importance.  The only important thing to me
is that the balance is current on the Database as I want to use these numbers
to later track my portfolio performance over different time periods.

Hope that makes it clear, and thanks for your help.

> How do you update your stock quotes sheet?
>
[quoted text clipped - 34 lines]
> > 11/27/06    35000   ** This row can be updated multiple times
> >                                   or a new row inserted below if date changes
tfrentz - 29 Nov 2006 00:29 GMT
Can someone please give me a hand.  It looks like Martin got busy on
something else.  Thanks.

> The stock prices are updated via the MSNStockQuote function add-in.  One
> click of the 'Update Quotes' on the toolbar will update all the stock prices,
[quoted text clipped - 49 lines]
> > > 11/27/06    35000   ** This row can be updated multiple times
> > >                                   or a new row inserted below if date changes
Martin Fishlock - 29 Nov 2006 04:32 GMT
I think the best way to try and do it is as follows

write a marco as follows:

'----------------------------
Sub updatestockvalues()

   Const sheetNameQ As String = "Quotes"
   Const sheetNameDB As String = "Database"
   
   Const QcolumnName As String = "A"
   Const QcolumnValue As String = "C"
   Const QrowStart As Long = 2
   Const QtextTotal As String = "Total"
   
   Const DBcolumnDate As String = "A"
   Const DBcolumnValue As String = "B"
   Const DBrowStart As Long = 2 ' not the titles
   
   Dim Amt As Currency
   
   Dim r As Long ' row pointer
   Dim c As Long ' column number
   
   Dim wsQ As Worksheet, wsDB As Worksheet
   Set wsQ = ThisWorkbook.Sheets(sheetNameQ)
   
  ' insert the macro to run the quotes here.

   ' find total row in Quotes
   r = QrowStart
   Do While wsQ.Range(QcolumnName & r) <> QtextTotal
       r = r + 1
       If r = 65537 Then
           MsgBox "Error finding total, halting.", vbOKCancel, "Error"
           Set wsQ = Nothing
           Exit Sub
       End If
   Loop
       
   Amt = wsQ.Range(QcolumnValue & r).Value
   Set wsQ = Nothing
       
   Set wsDB = ThisWorkbook.Sheets(sheetNameDB)
   
   ' find last day in Database assume that there is at least one date
   r = DBrowStart
   Do While wsDB.Range(DBcolumnDate & r) <> ""
       r = r + 1
       If r = 65537 Then
           MsgBox "Error in database sheet, halting.", vbOKCancel, "Error"
           Set wsDB = Nothing
           Exit Sub
       End If
   Loop
   If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then
       r = r - 1 ' date already there
   Else
       wsDB.Range(DBcolumnDate & r) = Date
   End If
   With wsDB.Range(DBcolumnValue & r)
       .Value = Amt
       .NumberFormat = "#,##0.00"
   End With
   Set wsDB = Nothing
   
End Sub
'----------------------------

This will update the sheet with the new day or current value.
You may have to play around with the dates if they are not dates in the
database but text.

You now need to get the name of the macro that is called when you click the
update quotes button and insert it into the macro above at the place where
indicated to insert the macro and then instead of clicking the current button
you click a new button that you need to asign to the above code.

I don't have the msquotes program so I can't advise.

I may be able to look at it if you give me a link to it.

This should set you on your way.
Signature

Hope this helps
Martin Fishlock

> Can someone please give me a hand.  It looks like Martin got busy on
> something else.  Thanks.
[quoted text clipped - 52 lines]
> > > > 11/27/06    35000   ** This row can be updated multiple times
> > > >                                   or a new row inserted below if date changes
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.