Range.Find not finding existing value

Question

I am trying to build a macro that loops through every worksheet in a workbook and records the "bill area" each worksheet is about and some other information in each workbook. My program works for the first worksheet but doesn't work for the second worksheet. The same standardized text that I am searching for is there, yet Range.Find is not working. Here is the code:

Sub DenialsReport() Dim heatWB, denialWB As Workbook Dim heatWS, denialWS As Worksheet Dim folderName, fileName, extName, wbName, billArea, postPeriod As String Dim a, b, lastRow, countCol, volumeCol, startRow, billAreaRow, billAreaColumn, grandRow As Long Dim postPeriodRange, billAreaRange, grandRange As Range Set heatWB = ActiveWorkbook Set heatWS = heatWB.Sheets("Denials") postPeriod = InputBox("Please enter the current post period in this form: yyyymm") Application.ScreenUpdating = False folderName = heatWB.Sheets("Support->").Cells(19, 3).Value fileName = heatWB.Sheets("Support->").Cells(20, 3).Value extName = heatWB.Sheets("Support->").Cells(21, 3).Value wbName = folderName + "\" + fileName + "." + extName Set denialWB = Workbooks.Open(wbName) b = 0 For Each denialWS In Worksheets denialWS.Cells.UnMerge Next denialWS For Each denialWS In Worksheets b = b + 1 denialWB.Sheets(b).Cells.UnMerge lastRow = heatWS.Cells(Rows.Count, 1).End(xlUp).Row + 1 ' find the bill area Set billAreaRange = denialWB.Sheets(b).Range("A1:G10").Find(What:="For Bill Area: ", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) billAreaRow = billAreaRange.Row billAreaColumn = billAreaRange.Column ' get the bill area billArea = denialWB.Sheets(b).Cells(billAreaRow, billAreaColumn).Value billArea = Replace(billArea, "For Bill Area: ", "") billArea = Left(billArea, InStr(billArea, "(") - 1) billArea = Trim(billArea) ' locate the count and amount columns along with the start and end of the rejection categories Set postPeriodRange = d enialWB.Sheets(denialWS.Index).Range("A1:AZ15").Find(What:=postPeriod, LookIn:=xlValues, LookAt:=xlWhole) countCol = postPeriodRange.Column amountCol = countCol + 1 startRow = postPeriodRange.Row + 2 Set grandRange = denialWB.Sheets(denialWS.Index).Range("A:A").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole) grandRow = grandRange.Row ' copy the information over to heatWS For a = startRow To grandRow - 1 heatWS.Cells(lastRow, 1).Value = billArea heatWS.Cells(lastRow, 2).Value = postPeriod heatWS.Cells(lastRow, 3).Value = denialWS.Cells(a, 1).Value heatWS.Cells(lastRow, 4).Value = denialWS.Cells(a, countCol).Value heatWS.Cells(lastRow, 5).Value = denialWS.Cells(a, amountCol).Value heatWS.Cells(lastRow, 6).Value = denialWS.Cells(a, amountCol).Value / denialWS.Cells(grandRow, amountCol).Value lastRow = heatWS.Cells(Rows.Count, 1).End(xlUp).Row + 1 Next a Next denialWS denialWB.Close SaveChanges:=False heatWS.Range("A:F").AutoFit Application.ScreenUpdating = True End Sub

The problem occurs on this line:

Set billAreaRange = denialWB.Sheets(b).Range("A1:G10").Find(What:="For Bill Area: ", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

I have also tried to hardcode the program like this (value lies in cell C7):

billArea = denialWB.Sheets(b).Cells(7,3).Value

I believe this means that VBA isn't recognizing that there is a value in the cell. However, I am able to physically enter the cell and make edits. Any help is appreciated. Thank you.

Likely not your issue, but use the denialWS reference. denialWS.Range("A1:G10").Find. For Each looping over the worksheets and then manually trying to keep the index in sync is silly. It's also error prone, because an enumerated collection isn't required to return them in index order. — Comintern
– Comintern, Commented Nov 29, 2018 at 17:22
I had previously used denialWS.Range("A1:G10").Find but when that didn't work I manually kept track of the index. It was my way of diagnosing where the error occurred. Neither works. — Austin
– Austin, Commented Nov 29, 2018 at 18:47
Please, remove all the excessive code which doesn't have any meaning to question... — JohnyL
– JohnyL, Commented Nov 29, 2018 at 19:16

PeterT · Accepted Answer · 2018-11-29 19:33:35Z

You've got more than a few things in your code that can easily be working against you. So in addition to a suggestion for answering your question, here are some common issues that I see that can cause problems.

A single, common declaration line such as

Dim folderName, fileName, extName, wbName, billArea, postPeriod As String

does NOT assign the type String to all of those variables. In this case, only the last declaration postPeriod As String declares that variable of type String. All of the other variables default to the type Variant. The standard practice is to use a single line declaration for each variable, each with a specific type. Additionally, your code can appear much cleaner to declare the variable/type immediately before using it for the first time. In this way, you don't have to scroll way back up to the top to figure out what the type is. So:

Dim folderName As String Dim fileName As String Dim extName As String ...

Next, read this explanation of the difference between ActiveWorkbook and ThisWorkbook. Almost always, it's the latter form you want. So you should

Dim heatWB As Workbook Set heatWB = ThisWorkbook

You have two loops which use

For Each denialWS In Worksheets ... Next denialWS

My assumption in reading this line is that you are looping through worksheets in the ActiveWorkbook. But the variable denialWS implies that you really want to be looping through the worksheets of the denialWB. If that's the case, then use

For Each denialWS In denialWB.Worksheets ... Next denialWS

Further, in the next loop, you're getting to each worksheet in the workbook, BUT you're confusing things by setting the b variable (why?) and using that to index through the worksheets as well. So it's very unclear which sheet you're operating on. (Why not use denialWS instead?)

At last to get to your question involving Find. It can easily be the case if you clean up the problems mentioned here, your Find issue will be solved. But you should also account for the case where it fails. So use something like

Set billAreaRange = denialWS.Range("A1:G10").Find(What:="For Bill Area: ", _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If not billAreaRange Is Nothing Then billAreaRow = billAreaRange.Row billAreaColumn = billAreaRange.Column ... <the rest of your logic here> Else <raise an error or display a MsgBox here> Exit For End If

Fixing all these things should also give you a fix for the hard-coded test you tried as well.

Collectives™ on Stack Overflow

Range.Find not finding existing value

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related