Solved: convert macro

[Raf]

Who can help me converting my marco so that it will activate on "change"? (after entering a value in a cell).
I've tried it but I keep getting errors...


Sub complete()
Dim searchvalue As Integer
searchvalue = ActiveCell.Value

With Worksheets(1).Range("a1:a10")
If Intersect(ActiveCell, Range("a1:a10")) Is Nothing Then Exit Sub Else
If ActiveCell.Value = 0 Then Exit Sub Else
Set c = ActiveCell
sheets(2).Select
Range("A2").Select
cells.find(What:="" & searchvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.Range("b1:f1").Copy
sheets(1).Activate
c.Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
c.Activate
End With
End Sub

[mikerickson]

would putting
=VLOOKUP(A1,Sheet2!$A$1:$F$1000,COLUMN(),FALSE)
in sheet1 B1 and dragging down and right do what you want?

[Paul_Hossler]

A regular ol' sub won't run on a worksheet change, you need to use a worksheet (or workbook) level event handler like


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


OR use a WS formula like Mike said with Calculation On

Paul

[Raf]

A regular ol' sub won't run on a worksheet change, you need to use a worksheet (or workbook) level event handler like


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


OR use a WS formula like Mike said with Calculation On

Paul

Hi Paul,

This still doesn't work. It results in an error and highlights:
" Range("A2").Select " ???

Mike's solution isn't what I need, I really need a vba code!

[Raf]

Thanks for the reply, but it's not what I'm looking for. It's a vba code I need.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Else

If Target.Value = 0 Then Exit Sub

With Sheets(2)
Set cell = .Cells.Find(What:="" & Target.Value, _
After:=.Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not cell Is Nothing Then

cell.Offset(0, 1).Resize(1, 5).Copy Target.Offset(0, 1)
End If
End With
End Sub

[Paul_Hossler]

Yea, I gave you the wrong one -- use XLD's Worksheet_Change code




Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Sorry about that

Paull

[Raf]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Else

If Target.Value = 0 Then Exit Sub

With Sheets(2)
Set cell = .Cells.Find(What:="" & Target.Value, _
After:=.Range("a1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not cell Is Nothing Then

cell.Offset(0, 1).Resize(1, 5).Copy Target.Offset(0, 1)
End If
End With
End Sub

This one works, many many thanks!!!!