Search ONLY through rows < 23 and column between A and Z

[jasondu]

Hi all,

I would like to search only between rows 1 and 23, and columns A and Z for a particular value .. "X". When the value .. "X" is found, find out which column that value is in, and store the column LETTER as a variable to be used later on.

Example:

"X" exists in Range("G6") .. macro would run and store G = column_where_x_exists

Thanks!
Jason

[StevenM]

Something like this?

Please Login or Register  to view this content.

[jasondu]

Hey StevenM,

Thanks for the reply!

This is good, but not exactly ... I need to store the location as a variable (the column letter only though) so that later on I can use it. I don't need it to display in a message box

So .. what I wanted to do is I have some headings and I need for the script to search for a particular heading between A1 and Z23. If it finds the value, lets say in C10, it will go down a couple of cells to sum all the numbers in C12:C15 .. that is why I need to store the column as a variable so later I can sum the range("x" & 12 & ":x" & 15)

essentially I need to locate a specific heading and only if that heading is an exact match will it take the values below it and sum it up

[StevenM]

Please Login or Register  to view this content.

If you want something more specific, could you upload a mockup of your workbook?

[jasondu]

Hi, thanks for the quick response,

I am having a little bit of trouble understanding some parts of your code especiallly this part .. I am still a beginner

Please Login or Register  to view this content.

ALSO*** I want to store the values of the sum as a variable so I can use it later on another worksheet


However I have attached an example of what I am looking for

If you have any questions please let me know, and thankyou once again!

Jason

[StevenM]

Please Login or Register  to view this content.

You can find the above code in: jasondu_search_example.xlsm

[jasondu]

Great! thank you so much .. but I have a problem - why does it say search not found if I change "Alien" to "Q1" on my sheet that has 75 rows and columns A - Z? I tried changing the parameters but search is not found?

Regards,
Jason

[StevenM]

(1) I assumed you changed sFind = "Alien" to sFind = "Q1", yes?

(2) Do you need to change the area to be searched: With Range("B3:H3")?

(3) "Q1" is not the same as "q1" (because of: MatchCase:=True), change to false if you want the search to search for both.

(4) There must not be anything else in the cell other than "Q1", including even a space character which cannot be seen.
If there is other text in that cell, then: Lookat:=xlWhole will need to be changed to: Lookat:=xlPart

With any luck, one of these four will be the cause of the problem.

[jasondu]

Hey Steven,

I have double checked and

1.) Yes I changed exactly to sFind = "Q1"
2.) Range - No change needed
3.) I replaced Alien with the exact case "Q1"
4.) In the cell there is only Q1

which is why I find it wierd for the macro to not be able to find...

Jason

[jasondu]

Okay, for some reason it won't find unless I limit the range down to 1 row, and columns A - W .. is it because it won't search for such a large range?

Jason

[StevenM]

Can you upload a workbook with the problem?

If you want, you can change the range to: With ActiveSheet.UsedRange

That should cover the whole worksheet.

[jasondu]

I'm sorry, but the workbook is related to work and I cannot upload it due to security reasons ..

Thank you so much for your help thus far - it seems like I have a crude workaround (by narrowing the search range) .. hopefully this will last for now

Again, thank you so much!

Regards,
Jason