Airstud
Topic Author
Posts: 3071
Joined: Wed Nov 29, 2000 11:57 am

Excel VBA Question (yes HERE, Why Not...)

Sun May 20, 2012 9:48 am

I have posted about this in a VBA forum but have not gotten a response and I am an impatient dude so why not go ahead and post it ici:

See, I do not have much experience with the VBA, but I want to write a program part of which will locate a certain row or rows in a spreadsheet that contain certain data in Column X (like "Henson" or "Nguyen" or "Hutchens"); then extract other data from a couple other columns in that row and spit it into a different spreadsheet (and then do the same thing until it's hit all the criterion-satisfying rows).

It seems that the Range.Find method is the way to do this. Am I wrong? VBA's documamentation says that the return value for this method is "A Range object that represents the first cell where that information is found."

When I run this code:

Resulticus = Range("X:X").Find("Henson")
MsgBox (Resulticus)

The MsgBox says "Henson Stewart". Which is indeed the content of the cell I expected to be found, but "Henson Stewart" is a string o' text. I want a Range object a)because consarn it, that's what the documamentation said I'd get; and 2)because I need to get the row number of this cell so I can copy the other four cells' worth of data out of that row, stick it into my other spreadsheet, and allow my for-next loop to go merrily along.

Does Range.Find actually return a object? When I commented out the MsgBox and used either "Resulticus.Select" or "Resulticus.Activate" I got a runtime error 424 "Object required." So I went into the General/Declarations section of the code editor and did a "Dim Resulticus as Object", and then I got runtime error 91 "Object variable or With block variable not set"

Wha goin' on is...how do I get this Range.Find method to work?
Pancakes are delicious.
 
DfwRevolution
Posts: 8538
Joined: Sat Jan 09, 2010 7:31 pm

RE: Excel VBA Question (yes HERE, Why Not...)

Sun May 20, 2012 3:15 pm

If there is nothing else you are trying to automate with the VBA code, would a pivot table get the job done? I try to avoid VBA like the plague.
 
Goldenshield
Posts: 5005
Joined: Sun Jan 14, 2001 3:45 pm

RE: Excel VBA Question (yes HERE, Why Not...)

Sun May 20, 2012 6:44 pm

VBA is based on VB6, which is NOT object oriented in how we know it via .NET, although it does contain objects.

Anyhow, what you are doing is using the default action of the Range type, which is .text, and that's not what you want. You need to get its address.

This is what you need to do:

Quote:


'Define the range object
Dim c As Range

'get the first instance of the what we are searching for
c = Sheet1.Range("a1:a500").Find("test")

'test if anything was returned
If Not c Is Nothing Then
'Get the row and column where the search passed
Dim Row As Integer
Dim Col As Integer

Row = c.Range.Row
Col = c.Range.Column
MsgBox ("The row you want is: " & Row)
End If
Two all beef patties, special sauce, lettuce, cheese, pickles, onions on a sesame seed bun.
 
Airstud
Topic Author
Posts: 3071
Joined: Wed Nov 29, 2000 11:57 am

RE: Excel VBA Question (yes HERE, Why Not...)

Fri May 25, 2012 9:29 am

Thank you, Goldenshield.

I tried this code, and at first I got a compile-time error highlighting the word "Range" in
Row = c.Range.Row


The code that I'm running now goes:

Private Sub CommandButton1_Click()
Application.Workbooks.Open ("H:BASICTUESDAY_FLAT010312.xls")
Worksheets("T_TUESDAY_FLAT").Activate
C = Worksheets("T_TUESDAY_FLAT").Range("X:X").Find("Henson")

If Not C Is Nothing Then
'Get the row and column where the search passed
Dim cRow As Integer

cRow = C.Row
MsgBox ("The row you want is: " & cRow)
End If

End Sub

And I am again getting that 'runtime error 91 "Object variable or With block variable not set"' error, with the
C = Worksheets("T_TUESDAY_FLAT").Range("X:X").Find("Henson")

line highlighted when I click "Debug"


hooboy...
Pancakes are delicious.
 
User avatar
airmagnac
Posts: 355
Joined: Wed Apr 18, 2012 10:24 pm

RE: Excel VBA Question (yes HERE, Why Not...)

Fri May 25, 2012 10:14 am

Try adding a "Set" in front of the range object declaration :

Set C = Worksheets("T_TUESDAY_FLAT").Range("X:X").Find("Henson")
My goal as an engineer is to fill my soul with coffee and become immortal
 
Goldenshield
Posts: 5005
Joined: Sun Jan 14, 2001 3:45 pm

RE: Excel VBA Question (yes HERE, Why Not...)

Fri May 25, 2012 10:28 am

Quoting airmagnac (Reply 4):
Try adding a "Set" in front of the range object declaration

That's my bad. I don't use VB6/A as often as I used to.

--------------

Another thing in your code is that you failed to declare C as a Range object (as I did in the top of my code,) and instead are just naming a variable without telling it what it is.

Turning on OPTION STRICT and OPTION EXPLICIT may cause you a lot of headache when you turn them on, but will beat a lot of bad coding habits out of you.

[Edited 2012-05-25 03:41:07]
Two all beef patties, special sauce, lettuce, cheese, pickles, onions on a sesame seed bun.
 
User avatar
airmagnac
Posts: 355
Joined: Wed Apr 18, 2012 10:24 pm

RE: Excel VBA Question (yes HERE, Why Not...)

Fri May 25, 2012 11:40 am

Quoting Goldenshield (Reply 5):
That's my bad

And even when you use it fairly frequently, it's still sooooo easy to forget. I wasted 2 full days a couple of months ago, trying to solve this very same problem...Gotta love VBA !  



Quoting Goldenshield (Reply 5):
Turning on OPTION STRICT and OPTION EXPLICIT may cause you a lot of headache when you turn them on, but will beat a lot of bad coding habits out of you.

     
My goal as an engineer is to fill my soul with coffee and become immortal

Who is online

Users browsing this forum: Baidu [Spider], DocLightning, greenjetav, Yahoo [Bot] and 13 guests