Problem Code (97)

Topic

New Reply

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
Worksheets(“Receipt”).Range(“D9”).Select
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

Reply | Quote

Replies

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.Rows.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.

Reply | Quote

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

Cheers

Rob

Reply | Quote

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”)

Reply | Quote