Sponsor Message:
Non Aviation Forum
My Starred Topics | Profile | New Topic | Forum Index | Help | Search 
Excel VBA Question (yes HERE, Why Not...)  
User currently offlineAirstud From United States of America, joined Nov 2000, 2732 posts, RR: 3
Posted (2 years 6 months 1 week 3 days 3 hours ago) and read 1474 times:

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.
6 replies: All unread, jump to last
 
User currently offlinedfwrevolution From United States of America, joined Jan 2010, 997 posts, RR: 51
Reply 1, posted (2 years 6 months 1 week 2 days 21 hours ago) and read 1434 times:

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.

User currently onlineGoldenshield From United States of America, joined Jan 2001, 6101 posts, RR: 14
Reply 2, posted (2 years 6 months 1 week 2 days 18 hours ago) and read 1401 times:

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.
User currently offlineAirstud From United States of America, joined Nov 2000, 2732 posts, RR: 3
Reply 3, posted (2 years 6 months 5 days 3 hours ago) and read 1284 times:

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 currently offlineairmagnac From Germany, joined Apr 2012, 317 posts, RR: 52
Reply 4, posted (2 years 6 months 5 days 2 hours ago) and read 1280 times:

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

Set C = Worksheets("T_TUESDAY_FLAT").Range("X:X").Find("Henson")



One "oh shit" can erase a thousand "attaboys".
User currently onlineGoldenshield From United States of America, joined Jan 2001, 6101 posts, RR: 14
Reply 5, posted (2 years 6 months 5 days 2 hours ago) and read 1269 times:

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 currently offlineairmagnac From Germany, joined Apr 2012, 317 posts, RR: 52
Reply 6, posted (2 years 6 months 5 days 1 hour ago) and read 1256 times:

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.

     



One "oh shit" can erase a thousand "attaboys".
Top Of Page
Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

Similar topics:More similar topics...
Global Warming IT Is REAL--Here's Why. posted Sun Dec 6 2009 17:01:41 by JRDC930
Lose A House ... Why Not Shoot Your Wife posted Thu Dec 11 2008 04:16:00 by STLGph
Geek Question: Anyone Here Using Asterisk (PBX)? posted Sun Aug 12 2007 05:12:32 by Lincoln
If He "Misled" Congress, Why Not Impeachment? posted Tue Feb 13 2007 17:22:34 by AerospaceFan
Why Not Sell Nuclear Tech To Taiwan? posted Tue Jan 23 2007 23:53:09 by Sean1234
So Why Not? Another SoCal Commuter Train Video posted Wed Jan 3 2007 04:42:08 by Matt D
Another Signed By Thread, Why Not? posted Mon Dec 4 2006 07:02:27 by Cadet57
Do You Care About Darfur, And Why Not? posted Wed Oct 4 2006 12:15:29 by AerospaceFan
Canada Hosting A Soccer World Cup: Why Not? posted Wed Jul 12 2006 20:13:29 by YVRlonghauler
Playing Tennis In Bikinis: Why Not? posted Wed Jun 28 2006 03:24:49 by AerospaceFan