PDA

View Full Version : Sleeper: Search sheet



gibbo1715
08-17-2005, 11:17 AM
can anyone tell me why the below is not working please


Private Sub Worksheet_Change(ByVal Target As Range)
Dim value As String
value = Range("B11").value
If ActiveCell = Range("B11") Then
Application.ScreenUpdating = False
Sheets("Customers").Select
Range("A1").Select
Cells.Find(What:=value, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Sheets("Invoice").Range("C22").value = ActiveCell.Offset(0, 1).value
Sheets("Invoice").Range("C23").value = ActiveCell.Offset(0, 2).value
Sheets("Invoice").Range("C24").value = ActiveCell.Offset(0, 3).value
Sheets("Invoice").Range("C25").value = ActiveCell.Offset(0, 4).value
Sheets("Invoice").Range("C26").value = ActiveCell.Offset(0, 5).value
Sheets("Invoice").Range("H21").value = ActiveCell.Offset(0, 9).value
Application.ScreenUpdating = True
Sheets("Invoice").Select
End If
End Sub
cheers

gibbo

Bob Phillips
08-17-2005, 11:25 AM
Need a bit more info than that. What should it do, and what happens?

Generally speaking, in a change event you shouldn't be testing ACtivecell, Target is passed as an argument, which is the cell being changed. Which sheet is this event associated with, and did put the code in the sheet code module.

gibbo1715
08-17-2005, 11:32 AM
what im trying to do is use a validation list to change the contents of a cell (A customers name), the list is a list from a customers sheet. What i then want it to do is search the customers sheet for that name and when found fill various cells on my invoice sheet with data from my customer sheet automatically (i.e address cust no etc)

The error i get is at the line Range("A1").Select and the error is a select method of range class failed

Thanks for taking the time to look

gibbo

Bob Phillips
08-17-2005, 12:03 PM
Not tested, knocked up in a bit of a rush, but try this.

BTW. NEVER name a variable value"



Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "B11" Then
Application.ScreenUpdating = False
Set cell = Worksheets("Customers").Find(What:=.value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not cell Is Nothing Then
Me.Range("C22").value = cell.Offset(0, 1).value
Me.Range("C23").value = cell.Offset(0, 2).value
Me.Range("C24").value = cell.Offset(0, 3).value
Me.Range("C25").value = cell.Offset(0, 4).value
Me.Range("C26").value = cell.Offset(0, 5).value
Me.Range("H21").value = cell.Offset(0, 9).value
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub

gibbo1715
08-17-2005, 12:09 PM
thanks again but that didnt work, if i remove the error handling it doesnt give me an error just doesnt move any data across

Bob Phillips
08-17-2005, 01:01 PM
Couple of silly errors. Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$B$11" Then
Set cell = Worksheets("Customers").Cells.Find(What:=.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not cell Is Nothing Then
Me.Range("C22").Value = cell.Offset(0, 1).Value
Me.Range("C23").Value = cell.Offset(0, 2).Value
Me.Range("C24").Value = cell.Offset(0, 3).Value
Me.Range("C25").Value = cell.Offset(0, 4).Value
Me.Range("C26").Value = cell.Offset(0, 5).Value
Me.Range("H21").Value = cell.Offset(0, 9).Value
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub

gibbo1715
08-17-2005, 01:23 PM
sorry still not doing anything when i change value in b11

Bob Phillips
08-17-2005, 02:09 PM
sorry still not doing anything when i change value in b11

Does for me. Have you put it in the right place?