Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Code (97)

    Worksheet -"Stock" - stores a list of cd's and their prices.
    Worksheet -"Receipt"- stores a list of cd's and prices selected from ListBox1.
    Each new selection in any one order is added to the bottom of ranges named Invoice and Invoice2.
    I have written some simple code to achieve this. The problem is that it worked once then subsequently throws up an error on the marked line. The message claims that the object does not support this method or property.

    Private Sub Receipt_Click()
    Dim Invoice, Invoice2, MyCdList As Range
    Dim MyCd As String
    Dim ListBox1 As ListBox
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Worksheets("Receipt").Range("D9").Select</span hi>
    x = ActiveCell.CurrentRegion.Rows.Count
    Set Invoice = Worksheets("Receipt").Range("D" & x + 10).Select
    Set Invoice2 = Worksheets("Receipt").Range("E" & x + 10).Select
    Set MyCdList = Worksheets("Stock").Range("A2:A17")
    MyCd = SelectCd.ListBox1
    MyCdList.Find(MyCd).Select
    Invoice.Value = ActiveCell.Value
    Invoice2.Value = ActiveCell.Offset(0, 3).Value

    Can anyone suggest what the problem is and is there a better way of coding the solution?

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Code (97)

    I suspect you can do it like this:

    Private Sub Receipt_Click()
    ' In your code Invoice and Invoice2 were dimmed as Variant!
    Dim Invoice As Range, Invoice2 As Range, MyCdList As Range
    Dim MyCd As String
    Dim ListBox1 As ListBox
    x = Worksheets("Receipt").Range("D9").CurrentRegion.Ro ws.Count
    Set Invoice = Worksheets("Receipt").Range("D" & x + 10)
    Set Invoice2 = Worksheets("Receipt").Range("E" & x + 10)
    Set MyCdList = Worksheets("Stock").Range("A2:A17")
    MyCd = SelectCd.ListBox1
    MyCdList.Find(MyCd).Select
    Invoice.Value = ActiveCell.Value 'Not sure if you need to change this because the activecell might have changed!
    Invoice2.Value = ActiveCell.Offset(0, 3).Value

    Note I removed all selecting, since that is not necesary.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Code (97)

    Many thanks, Jan Karel.
    Would you explain the cause of the error message?

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Code (97)

    Because the sheet you are trying to select a cell on is not the active sheet. To avoid that problem, first select the sheet:

    Worksheets("WhateverSheet").select
    Activesheet.Range("C3").Select

    Or use:

    Application.goto Worksheets("WhatEverSheet").Range("C3")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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