Irmann,

This is how you would loop. I changed the item being found from Client to Product - otherwise, you
would simply find the same thing each time. I'm not sure if that is what you want, but you should
be able to get the idea....

Dim MyProduct As Range
For i = 8 To 100
Product = Cells(i, 6).Value 'Range("F8") = Cells(8, 6)
Set MyProduct = ThisWorkbook.Sheets("Sheet2").Columns("E").Find _
(what:=Product, LookAt:=xlWhole)
Cells(i, 8).Value = MyProduct.Offset(0, 2).Value
Next i


Without looping:

With Range("H8:H100")
.Formula = "=INDEX(Sheet2!G:G,MATCH(F8,Sheet2!E:E,False)) "
.Value = .Value
End With

HTH,
Bernie
MS Excel MVP


"Irmann" wrote in message ...

Thank You Bernie Deitrick for the reply.

That helping me a lot. :-)

Bernie, can you show me how to program my previous program by using
'For....Next' method.

because i don't want just find Range("F8") only. I want the program can
looping from Range("F8") to Range("F100") and the same time can pull
difference data.

Below is the new program that i use 'For....next' method(don't know its
right or not) :-

Private Sub CommandButton2_Click()

Dim MyProduct As Range
For i = 7 To 100
Product = Cells(i + 1, 6).Value 'Range("F8") = Cells(8, 6)
Set MyProduct = ThisWorkbook.Sheets("Sheet2").Columns("E").Find
_(what:=Client, LookAt:=xlWhole)
Next

ActiveSheet.Cells(i + 1, 8).Value = MyProduct.Offset(0, 2).Value

End Sub







Bernie Deitrick;631843 Wrote:
Irmann,

Your line

ActiveSheet.Cells(8, 8).Value = MyProduct.Columns("G").Value

uses relative addresssing (this part, psecifically:
MyProduct.Columns("G").Value) , which means
that the code will pull the value from 7 columns to the right of
MyProduct (a cell in column E) - so
the value is from column K. If you really want the value from column
G, then use

ActiveSheet.Cells(8, 8).Value = MyProduct.Offset(0,2).Value

HTH,
Bernie
MS Excel MVP


"Irmann" wrote in message
...-

I have a problem about using find method in Excel VBA.
Here is my program:-
__________________________________________________ _______________

Private Sub CommandButton2_Click()

Dim MyProduct As Range
Product = Range("F8").Value
Set MyProduct =
ThisWorkbook.Sheets("Sheet2").Columns("E").Find(wh at:=Client,
LookAt:=xlWhole)

ActiveSheet.Cells(8, 8).Value = MyProduct.Columns("G").Value

End Sub

__________________________________________________ ___________________
-
how can I pull data at Columns(G) to Cells(8,8)
the program I wrote ' ActiveSheet.Cells(8, 8).Value =-
MyClient.Columns("G").Value ' , give me error... Run-time error '91'
Object variable or With block variable not set-
What does it mean?
Please someone help me to solve this problem....-




--
Irmann -




--
Irmann