Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Entering Values in Cells (VBA/Excel/97)

    How can I enter additional information into a cell without overwriting the existing data contained in the cell?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    You can't have two separate pieces of numeric data in one cell. You can change the value using the existing value in VBA, or you can add text to text or turn the data into text and append text, or you can expand an existing formula. Can you explain what you want to do?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    I have bonds and cds in a portfolio. I've created a "maturity" calendar so that I can see, forward, what securities will mature. The problem is when I have two securities that mature on the same day, my "loop" overwrites the cell with the last maturity. Any way out of this?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    Without knowing your data layout or seeing your code, there are a number of ways this overwrite could happen. (I can guess that you are extracting future maturities, and if there are more than one maturing on the same date they overwrite, but I could be wrong, and I'd need to see the code to fix it.) It would make most sense if you posted a censored example (change the names and amounts, limit the data to no more than 30 items, modify any other confidential data such as listing symbols) of your speadsheet including the code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    Yea, it's a bit of code and data. But, generally speaking, I have a "for" statement that loops through the named range that contains the maturity dates of all the holdings. If the ith security matures within the calendar date, then another "for" statement matches the name and amount of that maturity to that specific day of the month. I believe that you are correct: if there are two securities that mature on the same day, the loop posts the first maturity and then overwrites the first post with the second maturity. Before I post the code and data, do you know of a way that would simply allow the second security to information to be added (in the second loop) to the cell without clearing anything out?

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    In doing a loop like this, I'd probably be using a Counter typed as Long to define which Row the data gets copied into. So I'd just write each date that is within range to the Excel row matching the counter, and it wouldn't matter how many are the same maturity date.

    You don't want to try and add two security item listings to each other, you should be placing them in separate rows.

    That your code overwrites makes me think that you are using the date somehow to drive the loop, so you need to test the current item maturity date against the last one copied, and if they are the same, advance the row where the data will be placed. Testing the dates should be as simple as getting the current item date and the last item date and seeing if theye are equal.

    If it will take a while to censor the data, you could just post the code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    <P ID="edit" class=small>(Edited by JohnBF on 21-Sep-04 15:21. )</P>I just had a belated epiphany. Are you trying to stick each maturity record in a single cell in a Calendar layout? If so, go back to my post about adding text to text. You can concatenate cell contents using VBA if you treat all the parts as text and preset the cell NumberFormat as Text. To remind myself how to do this, I just recorded and edited this snippet of code to concatenate mutiple values into a singel Excel cell formatted as text:

    Dim date1 As Date, date2 As Date
    date1 = Date ' set date1 to system date for test
    date2 = Date - 2 ' set date2 two days prior to system date for test
    With ActiveCell
    .NumberFormat = "@"
    .FormulaR1C1 = .FormulaR1C1 & Chr(10) & date1 & Chr(10) & date2
    End With

    The code can be repeatedly run on the same cell and will just add more and more date values.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Values in Cells (VBA/Excel/97)

    A bit of modification and it works great. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •