FINDING INFO IN HIDDEN CELLS

[Fire_Chief]

I am trying to find info in a group of cells that are hidden.
It will give be an error message unless I unhide the cells.
THIS WORKS FIND:
Columns("LV:MK").Select
Selection.EntireColumn.Hidden = False

Range("LV399:MK452").Select
Selection.Find(What:=DIVISION, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

THIS WILL NOT WORK:

Range("LV399:MK452").Select
Selection.Find(What:=Division, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

I have done this in another park of the workbook (same page) without unhide it, and it works so I am confused.
I have also unlocked the entire workbook with no luck.
Tried forever to figure this out. Need help..


error message is:
Object variable or with block variable not set


Thank You

 

[DanteAmor]

Hi @Fire_Chief . Thanks for posting on the forum.

It works with hidden columns, if you use the xlFormulas parameter instead of xlValues.

For example, the following works with hidden or visible columns:

Sub FindingInfo()
  Dim f As Range
  Dim DIVISION As String
  
  DIVISION = "hellox"
  Set f = Range("LV399:MK452").Find(DIVISION, , xlFormulas, xlWhole, , , False)
  If Not f Is Nothing Then
    f.Select
  Else
    MsgBox "Not found"
  End If
End Sub

I don't understand why you need to select the cell if the columns are hidden, you won't be able to see which cell is selected. But in object 'f' you have the cell found and then you can use it for whatever you want.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Fire_Chief]

Hi @Fire_Chief . Thanks for posting on the forum.

It works with hidden columns, if you use the xlFormulas parameter instead of xlValues.

For example, the following works with hidden or visible columns:

Sub FindingInfo()
  Dim f As Range
  Dim DIVISION As String
 
  DIVISION = "hellox"
  Set f = Range("LV399:MK452").Find(DIVISION, , xlFormulas, xlWhole, , , False)
  If Not f Is Nothing Then
    f.Select
  Else
    MsgBox "Not found"
  End If
End Sub

I don't understand why you need to select the cell if the columns are hidden, you won't be able to see which cell is selected. But in object 'f' you have the cell found and then you can use it for whatever you want.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Dante
I think we are very close but not getting what I need. It does many more things after it finds the correct division.
I need to highlight the area I want it to look and then find the word "SOUTH" Then I can select the cell I want with
an offset when the active cell is SOUTH. Inside that range is the word SOUTH and that is where I want the activecell.
It creates brackets for the NCAA basketball Tournament.
Hope this isn't too confusing.... Let me know
My code is highlighted out. Statements 2 & 3 are needed for my code to work without and error.


Sub ENTER_TEAM_IN_BRACKET()

Dim f As Range
Dim DIVISION as string
DIVISION = "SOUTH"

' ActiveSheet.Unprotect PASSWORD:="MARTIN"
' Columns("LU:MJ").Select
' Selection.EntireColumn.Hidden = False

'Range("LU399:MJ452").Select

' Selection.Find(What:=DIVISION, After:=ActiveCell, LookIn:=xlValues, LookAt _
' :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
' False, SearchFormat:=False).Activate


Set f = Range("LV399:MK452").Find(DIVISION, , xlFormulas, xlWhole, , , False)

 

[DanteAmor]

I think we are very close but not getting what I need. It does many more things after it finds the correct division.
I need to highlight the area I want it to look and then find the word "SOUTH" Then I can select the cell I want with
an offset when the active cell is SOUTH. Inside that range is the word SOUTH and that is where I want the activecell.
It creates brackets for the NCAA basketball Tournament.
Hope this isn't too confusing.... Let me know
My code is highlighted out. Statements 2 & 3 are needed for my code to work without and error.

It's hard to understand if your code doesn't work, plus most of the lines of your code are commented out.
I recommend that you explain with examples what you have in the cells, what you need to do, what is the final objective of locating the data in the sheet.

As I told you, I don't understand why you need to activate a cell. If you locate the cell just do what you need to do with the cell. It's not good practice what you do in your code, select the cells, activate the cell.
A good practice is to locate the cell and use the object. You don't need to select it to do anything to it.

As I told you, explain in detail what you want to do, forget about the macro, I'll help you with the macro.

And you must also specify if the rows are hidden, if the columns are hidden, if the sheet is hidden, if the sheet is protected. Do not omit any information, that way the solution will be easier to deliver to you.

By the way.
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Fire_Chief]

It's hard to understand if your code doesn't work, plus most of the lines of your code are commented out.
I recommend that you explain with examples what you have in the cells, what you need to do, what is the final objective of locating the data in the sheet.

As I told you, I don't understand why you need to activate a cell. If you locate the cell just do what you need to do with the cell. It's not good practice what you do in your code, select the cells, activate the cell.
A good practice is to locate the cell and use the object. You don't need to select it to do anything to it.

As I told you, explain in detail what you want to do, forget about the macro, I'll help you with the macro.

And you must also specify if the rows are hidden, if the columns are hidden, if the sheet is hidden, if the sheet is protected. Do not omit any information, that way the solution will be easier to deliver to you.

By the way.
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

It is selected out because I wanted to show you the code that worked if I unhide it.

I hope you understand what a basketball bracket is.
If you don't I will try to send you a bracket or you can go online and lookup 2023 basketball brackets.

There are four DIVISIONS in the brackets......SOUTH, EAST. MIDWEST and WEST.
The first thing I have to do is find the correct DIVISION the team is in.
The teams are listed under the DIVISION they are in.
I then find the winning team, offset them to the winning cell.
The losing team stays in the first cell and does not move on.
There are six rounds until the winning team is in the very center cell.
My code works great but when I try to find the DIVISION without unhiding the cells I get an error message.
When I unhide the range the screen flickers.
I have tried application.screenupdating = false but that doesn't seem to work.
I think if I make the DIVISION something other than a string it may work.

BTY Thanks for the help

 

[DanteAmor]

You are not explaining what you need to do.
You want to look for a DIVISION and then what.
You should make your explanations along with an example.
You must put here a sample of your data or images of what you have and what you need to do.
Without examples it is not possible to understand what you want to do.
I'm sorry, but while I'd like to help you further, it's not possible since you didn't provide an example with data and explanations related to that example.

 

[Fire_Chief]

I take it you do not know what a bracket is and you did not look on line for it.
That would have explained it

Thanks You anyway for what you did do.

 

[Joe4]

I take it you do not know what a bracket is and did not on line for it.
That would have explained it

You shouldn't expect people to do a whole bunch of research in order to help you. You will increase your chances of getting help if you make it easy for people to help you (remember, everyone here is a volunteer, no one is being paid to do this). Also, bear in mind that people on this forum are from all over the world. So while many people in America are familair with March Madness Basketball Brackets, those in our countries may not be familiar with it.

If there is something online that explains, then why not just find one and post a link to it so people can read it and see what you are talking about?

And even for those of us who are familiar with it, without seeing the structure of your sheet and data, it can be difficult to assist.
As they say, "a picture often says 1000 words!".

 

[Fire_Chief]

I do not know how to post pictures on here.
I have always posted a question and someone will answer.
This one was a little confusing and I see why you would want more however I
will use what I have.
I am not looking to make any enemies on here. The more friends the more people I can ask.
I do appreaciate what you have done.

Thank you again

 

[Joe4]

I do not know how to post pictures on here.
I have always posted a question and someone will answer.
This one was a little confusing and I see why you would want more however I
will use what I have.
I am not looking to make any enemies on here. The more friends the more people I can ask.
I do appreaciate what you have done.

Thank you again

Fair enough.

For future reference, do you know how to post links to web pages?
Find the webpage that contains an explanation of the brackets and then just copy & paste the URL here, and it will automatically make it a hyperlink for you.

In regards to posting pictures, you can actually post sections of your workbook using the tool mentioned in section B here (XL2BB tool) : Guidelines
If you are unable to use that, you can just take a snapshot of your screen, and then paste the image to your post. I often use the "Snipping Tool" that comes with Microsoft Office in order to do this.

Just some hints to help maximize the help you get...