finding works with column range but not row range

Good Afternoon Folks,

I am trying to play with "find" and "mergearea" in order to better understand vba.

The code i wrote worked for finding a string in a range of merged columns.....but the code is failing in a range of merged rows.
if you see the problem, please hint, but do not give me the answer. I am more likely to remember the solution if i struggle. At
the moment, I have no clue where the struggle should be.

Not quite. I did see that Rng was not created when i looked at it in the "locals" window. I checked the spelling of the term in the string.
Also the Range appears logical to me. i am defining the range as column "C" and including rows 5 to 100.

THIS CODE WORKS

Please Login or Register  to view this content.

THIS CODE FAILS

Please Login or Register  to view this content.

Thank you,
bil

Sintek,

I looked up Find. i changed your suggestion to the following:

Please Login or Register  to view this content.

I used "xlvalues" as i thought it would give me a more accurate response than xlformula
i am mising some of the options....there are nine in all.

i checked that the range was working....(it is) but the code returns an error, "Runtime error '448' Named argument not found"
I am assuming the error means it could not find the string?

Hello Marc !
Thank you for responding.

I am a novice. so please be patient

I read your response. lets discuss your first two lines

the code needs to define a range in which the find will operate. the code defines the range as C3:C100. By looking at the worksheet on can see i have selected the correct column and a range of rows that encapsulates the rows of interest.

The string is "WallBoundary" and it is spelled the same way both in the code and in the worksheet. The only difference is that the worksheet string has been rotated ccw 90 degrees. I assumed that
was not relevant as the string is probably just a variable as is the rotation.

There is only one worksheet.

i will write a code check (although i am not sure i know what that means) after i get this crucial find step working.

There are 9 parameters for Find. I rewrote the code to make clear what parameters I am using.

Please Login or Register  to view this content.

The above code differs from Sintek's suggestion as when i read the explanation for xlValues and xlFormula it seemed a string search would succeed more accurately with xlValues
I was not sure how to account for the 5 parameters I did not use. i tried a series of commas but i got compile errors.

i am going to take your and other people's suggestions and not merge. originally i was trying to avoid hard coding the range as i have 30+ worksheets to read and the data shifts.
Hence i chose to use an implicit action on the part of the data input person (his or her using merged cells. but i realized that i was creating an opportunity for error by asking the
inputter to correlate merged cells with data cells. So now i will look for bounding data regions within the worksheet by actually looking at data and not using merged cells and avoiding
putting added responsibility on the person inputting the data.

Thank you for responding....
bil

You wrote :
« the code needs to define a range in which the find will operate. the code defines the range as C3:C100. By looking at the worksheet […] »
No, to test if Range.Find is not coded by foot that is superfluous to 'define' a range.
You stated for C3:C100 but your code used another range !
So what is the exact range where to search and in which worksheet ? As coding can't be guessing so that needs to be accurate at least !
By the way an alternative to Find method is the Excel basics worksheet function MATCH …

According to your last attachment and VBA help an Excel basics VBA demonstration as a beginner starter :

PHP Code: 

Sub Demo4Noob1()
       Dim Rf As Range
       Set Rf = Sheet1.UsedRange.Columns(3).Find("WallBoundary", , xlValues, 1)
    If Not Rf Is Nothing Then MsgBox "Found in cell " & Rf.Address: Set Rf = Nothing
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

According to your last attachment an alternative as a very beginner starter :

PHP Code: 

Sub Demo4Noob2()
    Dim V
        V = Application.Match("WallBoundary", Sheet1.UsedRange.Columns(3), 0)
        If IsNumeric(V) Then MsgBox "Found in cell C" & V
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

By the way according to your last attachment post #2 code works too on my side so what is the bad on yours ?!

TMS
O apologize for not thanking you earlier. Your acute observation really made a difference in successful compilation. I apologize for not
being more attentive.

Good Morning Marc!!!

For the second time, you have brought to my attention that I have not clearly defined the range. I apologize for presenting a code snippet that did not contain all the data needed to help me. The code i uploaded in support of my original question should reveal that I thought i did define exactly where the search would take place. I copied and pasted the code and placed it below so you can see I
am defining where the search is to take place.

How did you know that the range i specified C5:C100 was not the range being used by the code !!!! I looked for a way to conclude that result with data. The best i could do was to look at the "locals" window and see that Rng was "nothing". That answer could mean that the range was not appropriate (C5:C100) or the string was not appropriate. but i could not find a definitive way to isolate what the problem is. Oh. (thinking). maybe break up the code line leaving off the .find. let me try that. Test revealed that the column was 3 and the row was 5 which is exactly the cell (top left) that represents the merged region. So.....the range is correct. now i will put back the .find and this time use "ZZ" without any text rotation....to see if my original string is somehow causing a fault. Test with "ZZ" also failed. so the string is not at fault. Maybe i should spell out all the parameters of find. will do that in a few minutes.

Please Login or Register  to view this content.

SinTek!!!

Yes !!! yes !!! i saw how text IS considered a formula AND a value when i looked up "find". But when i looked for the option
(in writing the code), the only option i had was EITHER formula OR value. i could not find an option of both !!!! Soooo when i
looked for examples, the formula applied to "Apple" created "App" and the value apple to "Apple" created "Apple". Hence I chose
value instead of formula. Perhaps i misunderstood the example ! now... let me go explore your "here" to understand why i don't need all the parameters. In looking at the find explanation, all but "what" are optional. but the definition of find does not seem to
convey what one does if one does not include an option. I interpreted your line of code with ", ," to mean a placeholder for an option.
But there are NINE options....so i expected more ",," placeholders in your line of code. very confusing (not your code and help but the
microsoft support of the definition, Find.

Hi Sintek

I have uploaded the sample file I have been using. Many times. At least 5 times on this one question.
my focus, here, is to understand a failure. You and the other helpers have already convinced me to
look at methods of identifying a region without using merged cells. So when i write my code for that
task, i will chose another approach.

BUT merged cells is not the issue here. I am not using Find successfully. It is not apparent to me why
i am failing...and it is apparent that i am not using appropriate troubleshooting tools that i suspect
exist in VBA but am ignorant about.

The sample file is simple, on purpose. it keeps the conversation on only a few lines of code and prevents the shift of focus
to alternate ways to achieve a goal.

The focus remains on why does The code work on a horizontal search and fail on a vertical search.

why is the question. That is the name of my post , in fact.

I apologize if i sound snippy. I am actually very grateful that you guys are taking valuable time to
help me. All of you have helped me considerably. You all made me think.

But none of us have resolved the question i posted. Despite code corrections, the code continually fails to work.

I think i will post another worksheet and its code to even more dramatize the Find failure. the code will have nothing
but two lines of code and a few declarations and creations.

It is clear that I do not know how to troubleshoot in a way that offers data to support one way of thinking or another.
Once i get past compile errors, i should be able to use locals and immediate windows to observe what is happening
within one line of code. But other than deconstructing (breaking up a line of ocde into simpler pieces), i am not very
effective.

Marc,
I do like your suggestion. I will try it and see what happens. in the meantime what is wrong with my code? why does it fail in the vertical find and yet succeed on the horizontal.
what trouble shooting techniques can i use that will give me data that will help me isolate where the problem lies?

BY THE WAY....in your code just above (Sub Demo4Noob1()) you use a place holder, ", ," I am very confused. there are NINE possible parameters for Find. does ", ," suggest a placeholder
for more than one parameter? also how does "find" know "xlvalues" is for the parameter, "Lookat:=" I see....mmmm it is unique only for "LookAt". ok

thank you, Sintek!
What I am surmising is that the comma separated by a space and then another comma is a placeholder for the default settings of the other parameters. Only one set of ", ," are needed regardless of the number of
default parameters one is electing to use.

ok...Sintek,

I FOUND THE PROBLEM !!!!!!

Find demanded that I put in the SearchOrder parameter for the column search. The default must be xlRows and that is why the "Find" worked for the find in rows code line
But when applied to the column the default SearchOrder parameter was row and so it could find NOTHING given the range i had specified !!!!!

Thank you for hanging in there with me.

Now...to write code that will do what i want without the merged areas. will post in a new topic when i get it written.
Thank you again.

bil

Marc,
Your code with .Columns works !!!

in fact, your suggestion got me to consider the reason why my "find" failed in column mode.

find uses ALL its parameters. If a coder does not use any of the parameters, find uses its default parameters.
it turns out that find, by default, searches in a row mode despite the range declared within Find.
to fix my code, i had to use the SearchOrder:=xlColumns parameter for the search for a cell in a column.