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

 

[Fire_Chief]

Thank You I do have the snipping tool.
My trouble is I self taught myself VBA and I do some things an odd way.
I am leaving now but I may try to send you a part of the workbook that shows what I am doing.
The bracket is kinna big but I will paly with it.
Haver a great rest of your day

 

[Fire_Chief]

I hope you can see this
This what I made with code.

 

[Joe4]

I hope you can see this
This what I made with code.

Yes, we can see the image. However, we cannot see your row/column headers, so we cannot tell where exactly any of these cells reside on your sheet.
It is important to know exactly how your data aligns to your VBA code, so those details are critical.

Also, what part of this sheet is being hidden?

And please answer Dante Amor's question, of what exactly you are trying to do once you have found what you are looking for? What exactly is the next step?
And how does it know who the winner is?

I think this is going to be very complex, if you expect it to fill out the next line with the winner, because there is really no set rule to determine which cell to move to. The VBA code cannot really follow the lines of your document automatically, you need to tell it how to move, and that seems to change depending on which side of the bracket you are on (move left or move right), and how many rows to move up or down.

 

[Fire_Chief]

My code does all of what you are asking. The VBA I have written knows how to find the winning team and where to put that team. It even puts in the name of the person that has that team. What you see is a very small amount of what
this program does. The first thing I do is find the Division the team is in. I highlight the entire bracket and find the DIVISION. After that I have a variable that holds the name and though math I can figure where the winning team goes. My original question was can I find the corect DIVISION without unhiding the cells. If I don't unhide the coulmns first I get an error message when I try to find the division. If I unhide the columns it works perfectly.

 

[DanteAmor]

This is your original post:

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 put it from the beginning, you just have to change the xlFormulas parameter:

The following change works with hidden columns:

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

If you say your macro and it does everything else, then this simple change should work.

 

[Joe4]

My code does all of what you are asking. The VBA I have written knows how to find the winning team and where to put that team. It even puts in the name of the person that has that team. What you see is a very small amount of what
this program does. The first thing I do is find the Division the team is in. I highlight the entire bracket and find the DIVISION. After that I have a variable that holds the name and though math I can figure where the winning team goes. My original question was can I find the corect DIVISION without unhiding the cells. If I don't unhide the coulmns first I get an error message when I try to find the division. If I unhide the columns it works perfectly.

OK, it sounds like your code does a lot already, and you haven't shown it all to us, so we are just going to have to trust you on that.

The issue is you cannot "SELECT" cells that are hidden. But in VBA code, while the Macro Recorder has lots of "Select/Selection" statements in it because it is very literal in recording, most of the time you do NOT need to actually select the cells to work with them (and doing so actually slows your code down!). So many times, "Select/Selection" statements can be eliminated from your code. And you actually need to do that here, if you want to find hidden cells.

The method that Dante showed you in his first reply should give you most of what you need, maybe with a few small edits.
Here is a variation of his code that tells you the address of the cell it finds the Division in. So from there, I think you should have what you need to identify where you are on the sheet, and continue on with the rest of your code.

Sub FindingInfo()
  Dim f As Range
  Dim DIVISION As String
  
  DIVISION = "EAST"
  Set f = Range("LV399:MK452").Find(DIVISION, , xlFormulas, xlWhole, , , False)
  If Not f Is Nothing Then
    MsgBox "DIVISION found in cell " & f.Address(0, 0)
  Else
    MsgBox "Not found"
  End If
  
End Sub

Note that "f" is a range variable. So if you know what ranges each of the four quadrants is in, you can check to see if "f" is found in that range, like this:

Sub FindingInfo()
  
  Dim f As Range
  Dim DIVISION As String
  Dim QUAD1 As Range
  Dim QUAD2 As Range
  Dim QUAD3 As Range
  Dim QUAD4 As Range
  
  DIVISION = "EAST"
  
  Set f = Range("LV399:MK452").Find(DIVISION, , xlFormulas, xlWhole, , , False)
  If Not f Is Nothing Then
    MsgBox "DIVISION found in cell " & f.Address(0, 0)
  Else
    MsgBox "Not found"
    Exit Sub
  End If
  
  Set QUAD1 = Range("LV399:MC425")
  Set QUAD2 = Range("LV426:MC452")
  Set QUAD3 = Range("MD399:MK425")
  Set QUAD4 = Range("MD426:MK452")
  
  If Not Intersect(QUAD1, f) Is Nothing Then
    MsgBox DIVISION & " found in QUAD1"
  Else
    If Not Intersect(QUAD2, f) Is Nothing Then
      MsgBox DIVISION & " found in QUAD2"
    Else
      If Not Intersect(QUAD3, f) Is Nothing Then
        MsgBox DIVISION & " found in QUAD3"
      Else
        If Not Intersect(QUAD4, f) Is Nothing Then
          MsgBox DIVISION & " found in QUAD4"
        End If
      End If
    End If
  End If
  
End Sub

 

[Fire_Chief]

It does not work.
How do I send you my code?

 

[Joe4]

It does not work.
How do I send you my code?

You can post your code, as directed here: How to Post Your VBA Code
However, if you have a lot of code, that might not be the best way to share it. And I don't know how helpful that would be anyway without having access to your data file, where we can see where everything is laid out and what your data looks like (and see what is hard-coded and what is a formula).

The best way to share all of that is to upload your file to a file sharing site, and then provide a link to it here in this thread, so helpers can download it.
Note that I have limited access to download file myself (I can from home computer but cannot from work computer), so I may not be able to respond right away, as it may be a few hours before I have the chance to download the file (though perhaps Dante might have a chance to look at it sooner than me).

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
With your book we can test your code and see what the problem is.


In my tests my code works with the hidden columns, maybe you have something else in your book that you are not telling us and we can't test in our book.

 

[Fire_Chief]

I think I found my problem. Your code works but there is something different that I hope you can help me with.

When I write the word SOUTH in the bracket the code works.
However, that is not whats in the bracket. What is in the bracket is a reference from another cell. What is in the cell
that says SOUTH is =FP9 so it doesn't find SOUTH unless I unhide it. (not sure why)
I have tried this and it works with your code fine but I have to find a way to find SOUTH or I will have to unhide every time.

I hope I have explained good enough.

Marty