can som1 correct this code please (pretty simple i think)

[Guest_imported]

hello
when this wrx, excel should copy a name and paste it into th first empty cell on a dffrnt sheet, however it just pastes the word 'TRUE':

here's the code:

Sub cmdProcess_click()
Application.ScreenUpdating = False

Worksheets("Invoice&quot.Select
Range("B3&quot.Select

With Worksheets("Previous Purchases&quot.Range("C65535&quot.End(xlUp)
If .Address = "$C$3" And IsEmpty(.Value) Then
.Value = Worksheets("invoice&quot.Select
Else: .Offset(1, 0).Value = Worksheets("invoice&quot.Select
End If
End With

End Sub


...i think i need to change what .value equals, but what to?
hlp me pls
cheers

 

[rmikesmith]

The problem is with this statement:

Else: .Offset(1, 0).Value = Worksheets("invoice").Select

Should be:

    Else: .Offset(1, 0).Value = Worksheets("invoice").Selection

or

    Else: .Offset(1, 0).Value = Worksheets("invoice").ActiveCell

Your code was setting the value of a cell to the condition of whether the worksheet was selected, which is true because that's what WorkSheets("SheetName&quot.Select does!

HTH
M. Smith

 

[Loomah]

hello again killer
Have you given up on the 5th column of a listbox thing? If not, this guy, Mike, might be able to help - tee hee.

happy friday
roll on tomorrow
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Guest_imported]

i decided to change it a little bit, so now i have a text box and a button to add items, so its mostly working, but my lookup table has stopped working.

=IF(ISERROR(VLOOKUP(D3,Prices,3)),"NOTHING SELECTED",(VLOOKUP(D3,Prices,3)))

thats wot a cell is
but if i type a correct code in D3, it gives me the wrong value. not only this but, as long as i type a correct code, it will give me the saem value..help?

 

[Guest_imported]

mike, .activecell or .selection still doesn't work "object doesn't support this property or method"

 

[rmikesmith]

My error. It always seems to me that the Selection and ActiveCell properties should belong to the Worksheet object, but they don't; it's the Window object. Here is your modified procedure:

Sub cmdProcess_click()
    
Application.ScreenUpdating = False
Worksheets("Invoice").Select
Range("B3").Select
With Worksheets("Previous Purchases").Range("C65535").End(xlUp)
    If .Address = "$C$3" And IsEmpty(.Value) Then
      .Value = ActiveWindow.ActiveCell
    Else
      .Offset(1, 0).Value = ActiveWindow.ActiveCell
    End If
End With

End Sub

BTW, I don't see how the line

.Value = ActiveWindow.ActiveCell

will ever run since if the construct

.Range("C65535").End(xlUp)

returns address $C$3, that cell by definition will contain a value. Therefore the condition

If .Address = "$C$3" And IsEmpty(.Value) Then

will never be satisfied.

Regards,
Mike

 

[Guest_imported]

worked like a charm, cheers matey

 

[Loomah]

mike
i actually did the code for the xlup bit
it was originally A1 and was to allow for the first time the code was run on an empty sheet to prevent overwriting the same cell

it would need to be modified slightly for a header row etc...

Loom
nearly there
happy friday
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Guest_imported]

can ne1 explain y the lookup isnt workin?

 

[rmikesmith]

Loomah,

Thanks for the explanation. The code makes more sense now. Not to rub it in, but Thursday was my "Happy Friday"; I took the day off!


killer,

Change your VLookup formula to look like the following (add the FALSE parameter -- This tells VLookup to find an exact match and the list does not need to be in ascending order):

=IF(ISERROR(VLOOKUP(D3,Prices,3,FALSE)),"NOTHING SELECTED",(VLOOKUP(D3,Prices,3,FALSE)))

 

[Guest_imported]

i really didnt know that parameter existed. so thanks, now everything ive done so far works

now i can rest