Printable View
The following code is skipping over merged cells.
Set RngNex = RngTar.Find("*", After:=RngCur, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
I want to find non-blank cells in both merged and non-merged cells.
Why is this not working?
Is it a bug in Excel?
How do I fix it?
I cannot reproduce the problem as I understand it. Could you supply details of what is in the cells, what those range variables point at, and what you get?
Every variable starting with the name Rng is a declared range variable.
RngTar was initialized to the UsedRange on a sheet.
There is a looping construct in the program. Within the loop, RngCur is the last cell successfully found and RngNex is the next cell found.
My objective is to find non-blank cells (subject to other qualifications also). I am able to find non-blank non-merged cells, but it is skipping over non-blank merged cells.
Here is the relevant loop. You need not concern yourself about the IsEnc function. Suffice it to say that the loop stops when I find a cell of interest, or I run out of cells. You should be able to replace Not IsEnc() with True and observe what I'm observing.
100 If Not IsEnc(I, J) Then
Cells(I, J).Select
Call BeepSub("END")
Exit Sub
End If
Set RngNex = RngTar.Find("*", After:=RngCur, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
Set RngCur = RngNex
I = RngCur.Row
J = RngCur.Column
If I = RowLas And J = ColLas Then
Call BeepSub("END")
Exit Sub
Else
GoTo 100
End If
Thank you for your reply. I hope this helps. If you can't reproduce it from this, I suppose all I can do is send my workbook. I don't know if you're offering that or if that is even supported on this forum.
- Dave
And by the way, my code works with xlNext, but not with xlPrevious
Seems to work with some error handling for the merged cells.(Adjust as needed)
(attached sample file)
[VBA]Option Explicit
Sub Find_NONBlank_Cells()
Dim RngTar As Range
Dim RngCur As Range
Dim RngNex As Range
Set RngTar = Range("A2:L20")
Set RngCur = ActiveCell
On Error GoTo MergedCellHandler
Set RngNex = RngTar.Find("*", After:=RngCur, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
On Error GoTo 0
If RngNex Is Nothing Then
MsgBox "No Match found"
Else
RngNex.Activate
End If
Exit Sub
MergedCellHandler:
Set RngNex = RngTar.Find("*", , LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
RngNex.MergeArea.Activate
End Sub[/VBA]
I haven't tried the code directly above in this thread, but I don't see how it could possibly work. My code is not throwing an error, it is simply not finding the merged cells.
You can upload a workbook, hit the 'Go Advanced' button, and there is a 'Manage Attachments' button ther.
Workbook illustrating the problem attached
I downloaded and tested your file. Your code is finding merged cells ok as far as I noticed. Looks a little over complicated to me though.
What are the address's of the merged cells is it missing?
(My code also looks to me to be working ok in your file, after after I broadened the range to ActiveSheet.UsedRange.) - The reason I could take advantage of error handling is because my code is arranged differently than yours.
While from what I observed your code is working, it is a best practice in most cases to add Option Explicit at the top of the module, so that you will be alerted to undefined variables and similar problems.
All of the following variables in your code are undefined and should be. - Not defining the variables can make the code run inconsistently or differently on differently pc's, which might be what is happening to you.
[vba]Dim RngTar As Range
Dim RngCur As Range
Dim RngNex As Range
Dim I As Long
Dim J As Integer
Dim RowFr As Long
Dim RowTo As Long
Dim ColFr As Integer
Dim ColTo As Integer
Dim RowLas As Long
Dim ColLas As Integer[/vba]
Thank you for your reply.
Actually, in my production code, I do use Option Explicit, but I stripped down the code for this forum. I do appreciate the advice though.
When I select cell H17 and invoke macro CellPrev, H16 is then selected, whereas E17 (the upper left cell of a merged range) should be selected.
- Dave
After seeing your additional description of the issue, I'm experiencing the same problem.. The multi-column merged cells seem to be handled fine, but the multi-row merged cells get skipped.
If the users never change the sheet layout, or which cells have data, you could use select case for every cell address.
I put together the sample code below to demonstrate. (admittedly long winded and a lot of effort needed to code every address)
(sampleworkbook attached) -- coded only for cells H18 to A13
[vba]Sub CellPrev()
Option Explicit
' this covers only Range("A13:H18")
If ActiveCell.Column = 1 Then
ActiveCell.Offset(0, 1).Select
End If
If ActiveCell.Column >= 9 Then
ActiveCell.Offset(0, 9 - ActiveCell.Column).Select
End If
Select Case ActiveCell.Address
Case ("$A$16"), ("$G$19")
ActiveCell.Offset(-1, -2).Select
Case ("$A$17")
Range("H17").Select
Case ("$B$13")
Range("H13").Select
Case ("$B$17")
Range("H17").Select
Case ("$H$17")
Range("E15").Select
Case ("$A$15")
Range("H16").Select
Case ("$B$15")
Range("H16").Select
Case ("$F$18"), ("$G$18"), ("$H$18")
Range("E17").Select
Case ("$H$15"), ("$H$16")
ActiveCell.Offset(-1, 0).Select
Case ("$F$13"), ("$G$13"), ("$F$14"), ("$G$14")
Range("E13").Select
Case ("$F$15"), ("$G$15"), ("$F$16"), ("$G$16")
Range("E15").Select
Case ("$F$17"), ("$G$17"), ("$F$18"), ("$G$18")
Range("E17").Select
Case ("$H$14")
Range("E13").Select
Case ("$H$13")
Range("H11").Select
'... and so on
Case Else
ActiveCell.Offset(0, -1).Select
End Select
End Sub[/vba]
I'm sorry I didn't state this explicitly, but the sheet I supplied is just an example. In general, the incidence and shape of merged cells could be just about anything.
I guess I'm going to assume that FindPrevious is not working. I suppose I should see if I can get MicroSoft to comment.
Thanks for everybodys help.
- Dave