Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find Method problem in Excel VBA....
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.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Method problem in Excel VBA....
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 |
#3
|
|||
|
|||
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:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Method problem in Excel VBA....
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 |
#5
|
|||
|
|||
Thanks Bernie,
The program you give it to me run smoothly. The program run(looping) i = 8 To 100. It find the same name(product) in sheet1 at sheet2. When it find the same product name at sheet2, it pull data at column 'E' to Cells(i, 8) in sheet1. The problem is, when it can't find the same product name, the program give me error like it can't jump to the Next i. Bernie, what is the program that can jump to the Next i after it can't find the same product name. ### Bernie can you give me your email address. I want give you my excel file. Thanks, IRmann Quote:
Last edited by Irmann : March 3rd 08 at 06:45 AM |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Method problem in Excel VBA....
Irmann,
To check that the value is actually found before processing: 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) If Not MyProduct Is Nothing Then Cells(i, 8).Value = MyProduct.Offset(0, 2).Value Next i HTH, Bernie MS Excel MVP "Irmann" wrote in message ... Thanks Bernie, The program you give it to me run smoothly. The program run(looping) i = 8 To 100. It find the same name(product) in sheet1 at sheet2. When it find the same product name at sheet2, it pull data at column 'E' to Cells(i, 8) in sheet1. The problem is, when it can't find the same product name, the program give me error like it can't jump to the Next i. Bernie, what is the program that can jump to the Next i after it can't find the same product name. ### Bernie can you give me your email address. I want give you my excel file. Thanks, IRmann Bernie Deitrick;632349 Wrote: 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 - -- Irmann |
#7
|
|||
|
|||
Thank You so much Bernie for the code you gave it to me..... Its really helps. ;-) . Thanks again so so much.
I got one more question to ask you. Before this, we discuss the coding that can pull data between two difference sheet in the same workbook using Find method. Can we do it with difference workbook? can you give me the code........... i'm try to do it by myself but it give me an error. thanks, IRmann Quote:
|
#8
|
|||
|
|||
Thank you Bernie Deitrick for helping me solve my excel VBA question and give me some ideal. Thanks again.. God bless you
I’m try to automated 3 excel file that can pull some data by using Find method. The coding succeeds. Below is the coding:- Private Sub CommandButton2_Click() Dim MyProduct As Range For i = 5 To 100 Product = Cells(i, 6).Value 'Range("F8") = Cells(8, 6) Set MyProduct = Workbooks("January KLM NPI ReviewIR.xls").Worksheets("Sheet1").Columns("E").F ind(what:=Product, LookAt:=xlWhole) If Not MyProduct Is Nothing Then Cells(i, 8).Value = MyProduct.Offset(0, 4).Value If Not MyProduct Is Nothing Then Cells(i, 17).Value = MyProduct.Offset(0, 6).Value Next i End Sub This thread SOLVE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Object property/method problem | Excel Discussion (Misc queries) | |||
How do you find the method of selected points? | Setting up and Configuration of Excel | |||
How to find method to cut steel coil by using excel. | Excel Worksheet Functions | |||
problem with the command - method "UpdateFromFile" | Excel Worksheet Functions |