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
Quote:
Originally Posted by Bernie Deitrick
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
|