Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple problem, bad code (en/Excel2000/9.0.?)

    Hello there! My first VB code is a failure. My problem is very simple, I have one sheet with data, and considering the date of these entries, write them in another sheet. I attached a small version of my file and my code is:

    Longish piece of code moved to attachment by HansV

    I would appreciate any help <img src=/S/smile.gif border=0 alt=smile width=15 height=15>!
    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Your post didn't have an attachment. Could you post a reply with the workbook attached? Thanks.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Thanks, I didn't notice it!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    In the first place, select Tools | Options... in the Visual Basic Editor and tick the check box 'Require variable declaration', then click OK.

    In existing codemodules, insert a line Option Explicit at the top (if it isn't there already).

    This will force you to declare all variables you use explicitly, and warn you if you make a typo. For example, you have a line
    Application.Goto reference:=.Find(Data2, LookIn:=xlValues)
    It should be Date2 instead of Data2. Without Option Explicit, you probably wouldn't notice this.

    In the second place, I don't understand why you keep on writing values in the Calculus sheet, then immediately deleting them without having done anything with them. Could you explain in more detail what you want to accomplish?

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Thanks Hans for your post,

    Sorry if I couldn't explain it well. Ok, for each row in the Data sheet would return one debt value. So, the entries for Bob should not account for John...then I wrote:

    Sheets("Data").Select
    Application.Goto reference:="X"
    Application.Goto reference:="RC[5]"
    ActiveCell.Value = debt

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    I still don't understand. What your code DOES, after much moving hither and thither, is look up the value in the Debt column in the Calculus sheet for the Valuation date, and copy that into the Debt column of the Data sheet for each of the entries. So all the values in column F will be the same. I suspect that is not what you want, but you haven't explained yet what you DO want.

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

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    How do you identify from the Calculus sheet which entries belong to Bob, John, or Kimberly?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Yes, I got this result at the first time, and then the code didn't work anymore. You are right, it is not what I want <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> . For Bob, the columm F would return 860, but not for John. To calculate the Debt for John, I would have to look up the values from the second column...
    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> Certainly, it is a bad code...
    Thanks!

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    My intention was to use the variables date1, date2, value1,value2. After I got the "debt" for Bob, for example, I would move to the second row and use the same variables to calculate the debt for John.

    Thanks for your reply

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    I'm sorry, but I still don't have the slightest idea how you would arrive at 860 for Bob, but another amount for John or Kimberly. Could you explain how the data can be used to obtain the debt for John or Kimberly?

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

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    HansV and I are in effect asking the same question. What links $860 to Bob, and how do you tell who the $1000 and the $850 belong to? We can't figure out the link to connect the people to the amounts, and that is what you need to explain.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Hi, hans! Thanks for your patience <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> . Let's say that John took a loan of 800, at 10/2/2003, and payed 160 at 10/3/2004. I wish to registry this movement in the Calculus sheet. the calculus sheet do its job, and gives me the Debt value .In this example, Debt would not be 640, due to taxes.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Why not simply put the formula =C4-E4 in F4, and fill down? No code needed at all, nor the Calculus sheet.

    If that is not what you want, I still don't understand what you're getting at.

  14. #14
    New Lounger
    Join Date
    Dec 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Hi,
    Sorry, but the file I attached is just a simplified version. Actually, the "calculus sheet" performs many operations.
    [img]/forums/images/smilies/smile.gif[/img]

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simple problem, bad code (en/Excel2000/9.0.?)

    Then you will have to attach a more complete version, and explain what you want to accomplish, if you would like to be helped.

Posting Permissions

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