Davis Henderson
New Member
I am struggling to pull the value from a cell, and holding that value in the variable. Below is my code. Sheet5.Cells(20,15) is = 512 but the variable (datatoFind) is returning 0/empty. I have tried saving it as every type of variable (long, variant, string, integer, range) and nothing seems to pull the correct value. Also, is there a point in my code that is resetting the variable. Thanks for the help.
EDIT: it keeps returning the last sheet, perhaps the issue is that it the loop is not stopping when value is found.
Code:
Dim sheetCount As Integer
Dim datatoFind
Sub Find_Data()
Dim counter As Integer
Dim currentSheet As Integer
Dim notFound As Boolean
Dim FoundValue As Range
Dim FV As String
Dim yesNo As String
Dim sheetCount As Integer
Dim datatoFind As Variant
Dim RFooter As String
Dim shNumber As String
Dim shName As String
Dim MySheet As String
Dim currentName As String
Dim strFootNum() As Integer
Dim mystring As String
Dim iPages As Integer
Dim iCol As Integer
Dim iCols As Integer
Dim lRows As Long
Dim lRow As Long
Dim x As Long
Dim y As Long
Dim iPage As Integer
notFound = True
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = Sheet5.Cells(20, 15).Value
If IsError(datatoFind) Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Set FoundValue = Sheet5.Cells(20, 14)
If InStr(1, ActiveWorksheet.Cell, datatoFind, vbBinaryCompare) Then
notFound = False
If notFound = False Then
currentName = ActiveSheet.Name
If InStr(currentName, ".") > 0 Then
MySheet = (Split(currentName, ".")(0))
Else: MySheet = (Split(currentName, " ")(0))
End If
With ActiveSheet
y = ActiveCell.Column
iCols = .VPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = iCols _
Or y < .VPageBreaks(x).Location.Column
iCol = x
If y >= .VPageBreaks(x).Location.Column Then
iCol = iCol + 1
End If
y = ActiveCell.Row
lRows = .HPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = lRows _
Or y < .HPageBreaks(x).Location.Row
lRow = x
If y >= .HPageBreaks(x).Location.Row Then
lRow = lRow + 1
End If
If .PageSetup.Order = xlDownThenOver Then
iPage = (iCol - 1) * (lRows + 1) + lRow
Else
iPage = (lRow - 1) * (iCols + 1) + iCol
End If
End With
FV = MySheet & "." & iPage
With FoundValue
.Font.Name = "Times New Roman"
.Font.Bold = True
.Font.Size = "10"
.Font.Color = "Red"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
End With
FoundValue = FV
Else
Sheets(counter).Activate
Exit For
End If
Sheets(counter).Activate
End If
Next counter
If notFound Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub
EDIT: it keeps returning the last sheet, perhaps the issue is that it the loop is not stopping when value is found.
Last edited: