• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Variable resetting to 0

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.

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:
Upload sample workbook. Without it, hard to tell exactly where it's gone wrong.

I suspect, you have Sheet Code Name mixed with Sheet Name. But then there could be other cause for your issue.
 
Attached is an example file. May not be a great example, but it is quite the struggle getting a file small enough to upload.
 

Attachments

  • ExampleReferencing.xlsm
    17.6 KB · Views: 3
It actually is not. The macro is simply returning the last value in the last sheet. If you move the 365 to the top of the sheet you can see the issue. Is there anyway to ensure the macro stops on the value, and secondly, not consider the original value. I had some issues with returning the spot of the criteria cell i am searching for.
 
Huh? I don't get you.

In your code you have...
Code:
datatoFind = Sheet1.Cells(5, 3).Value

So, whatever is the value held in C5 is returned as datatoFind. Of course, it will return blank if you have blank in C5.

Detail step by step, exactly what you want this code to do, in words.
 
Okay I will do my best to explain:
datatoFind is the cell value that I want to search for throughout the work (365).
counters to search all sheets in the workbook.
Set foundValue is the cell that I want the result to be delivered in.
Everything after the if notfound=false is a way to get the footer of the current sheet where the value is found.
And then cycle back through if it isnt found.
 
Help with the code would be appreciated, but what I really need for anything to work is to understand how to search a cell value in the workbook. I want a variable to store the value of the cell contents (365), and then search the workbook for that value.
 
The .Value is returning the correct number, but that number is not storing in the (datatoFind) variable. Can anyone provide guidance?
 

Attachments

  • datatoFind.PNG
    datatoFind.PNG
    9.6 KB · Views: 5
... it is storing that number, like I have stated previously.

Instead of using ?datatoFind, use Debug.Print and break line.

upload_2018-5-1_11-34-13.png

Edit: To clarify what "?" does. It returns "CURRENT" value of variable/object etc. So, when the code isn't running or before the value is set for the variable, datatoFind isn't defined yet and will always return null string. You need to run the code past variable = something line in order to read it's content wtihin context.

So using break line, you could do something like below as well.
upload_2018-5-1_11-40-38.png
 
Last edited:
I have bit of hard time following your logic in code.
What is the process to return FD2.2 when 365 is found in FD2.1 sheet C64?
 
I apologize for the confusion, I actually figured it out. But while I have you here Chihiro, do you know of any way to pull a footer that was created in another macro as a string into my current macro?? Once again thanks for all the help!

Code for creating footer:
Code:
Sub Ref()
    Dim currentName As String
    Dim shName As String
    Dim MySheet As String
    Dim PageNumber As String
    Dim WS_Count As Integer
    Dim I As Integer
    Dim RFooter As String
   
    WS_Count = ActiveWorkbook.Worksheets.Count
   
    For I = 1 To WS_Count
   
    Sheets(I).Select
   
        currentName = ActiveSheet.Name
 
        If InStr(currentName, ".") > 0 Then
        MySheet = (Split(currentName, ".")(0))
        Else: MySheet = (Split(currentName, " ")(0))
        End If
       
        shName = "&""Times New Roman,Bold""&10&KFF0000" & MySheet + "."
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
       
        Dim shNumber As String
        shNumber = "&""Times New Roman,Bold""&10&KFF0000&P"
       
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = ""
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
           
            .RightFooter = shName & shNumber
            ActiveSheet.PageSetup.RightFooter = RFooter
        End With
        Application.PrintCommunication = True
       
    Next I
    Exit Sub
End Sub
 
I made RFooter = ActiveSheet.PageSetup.RightFooter
And then tried:
Function RFooter(ByVal ActiveSheet.PageSetup.RightFooter As String) As String

But I cant have 'ActiveSheet.PageSetup.RightFooter' in the parentheses. Suggestions?
 
Nope that's now how you do it.

Looking at your sub, you have unnecessary "Select"which is undesirable in function. So just use another function to read footer from some parameter.

Ex:
Code:
Function readRFooter(shName As String) As String
Dim x As String
x = Worksheets(shName).PageSetup.RightFooter
If Len(x) > 0 Then
    readRFooter = x
Else
    readRFooter = "No Right Footer"
End If
End Function

Then you can access it as:
Code:
Sub Demo()
Dim y as String
y = readRFooter(Sheet1.Name)
Debug.Print y
End Sub
 
Still getting the same issue. It is pulling the formula to create the footer and not the final text.
 

Attachments

  • FooterIssue.PNG
    FooterIssue.PNG
    23.6 KB · Views: 4
Ok, I reviewed your code in bit more detail. There are multiple issues.
Major one being... use of Select, and Activate in your code.

This is causing ambiguity and mixup in code logic when proper sheet/cell isn't activated.

As well, your iPage logic is off. It will evaluate to 4 instead of 2 with current logic for C64.

FYI - Footer with special formatting can't be read using VBA (only special formatting string can be read). So you'll need to construct proper string using logic outside of PageSetup.

I don't have time today to fix the code. But I'd recommend doing away with all unnecessary Select(s) and Activate(s) in the code to start.

Have a read of below on how to construct .Find() loop logic.
https://excelmacromastery.com/excel-vba-find/

I'll see if I have time tomorrow.
 
I assume you always have the sheet where search is performed active when the code runs, and not the sheet where you have datatoFind value stored. Correct?
 
Provided above assumption is correct...
Code:
Option Explicit
Sub Find_Data()
    Dim datatoFind As String, MySheet As String, FV As String
    Dim aSh As Worksheet, fSh As Worksheet
    Dim c As Range
 
    Set aSh = Sheet1
    datatoFind = CStr(aSh.Cells(5, 3).Value)
 
    If Len(datatoFind) = 0 Or Not IsNumeric(datatoFind) Then Exit Sub
 
    Set c = ActiveSheet.Cells.Find(What:=datatoFind, LookIn:=xlValues, LookAt:=xlPart)
 
    If Not c Is Nothing Then
        MySheet = IIf(InStr(c.Parent.Name, "."), Split(c.Parent.Name, ".")(0), Split(c.Parent.Name)(0))
        FV = MySheet & "." & pageNum(c)
    Else
        FV = "VALUE NOT FOUND"
    End If
 
    With aSh.Cells(5, 2)
        .Value = FV
        .Font.Name = "Times New Roman"
        .Font.Bold = True
        .Font.Size = "10"
        .Font.Color = vbRed
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With

End Sub

Function pageNum(rng As Range)
    Dim vA, hA
    Dim iRow As Long, iCol As Long, i As Long
    Dim pg As String
 
    With rng.Parent
        If .HPageBreaks.Count > 0 Then
            ReDim hA(0 To .HPageBreaks.Count)
            hA(0) = 1
            For i = 1 To UBound(hA)
                hA(i) = .HPageBreaks(i).Location.Row
            Next
        Else
            ReDim hA(0 To 0)
            hA(0) = 0
        End If
        If .VPageBreaks.Count > 0 Then
            ReDim vA(0 To .VPageBreaks.Count)
            vA(0) = 1
            For i = 1 To UBound(vA)
                vA(i) = .VPageBreaks(i).Location.Column
            Next
        Else
            ReDim vA(0 To 0)
            vA(0) = 0
        End If
        iRow = Application.Match(rng.Row, hA, 1)
        iCol = Application.Match(rng.Column, vA, 1)
        If .PageSetup.Order = xlDownThenOver Then
            pg = (iCol - 1) * (.HPageBreaks.Count + 1) + iRow
        Else
            pg = (iRow - 1) * (.VPageBreaks.Count + 1) + iCol
        End If
    End With
 
    pageNum = pg

End Function
 

Attachments

  • ExampleReferencing (1).xlsm
    26.7 KB · Views: 0
Hi Chihiro, I have come back to this code because I am trying to add another loop. I want the code to run for each line underneath the "Wage QRE Exp" until it reaches the gray box. Do you have any guidance for this task? Much appreciated!

Provided above assumption is correct...
Code:
Option Explicit
Sub Find_Data()
    Dim datatoFind As String, MySheet As String, FV As String
    Dim aSh As Worksheet, fSh As Worksheet
    Dim c As Range

    Set aSh = Sheet1
    datatoFind = CStr(aSh.Cells(5, 3).Value)

    If Len(datatoFind) = 0 Or Not IsNumeric(datatoFind) Then Exit Sub

    Set c = ActiveSheet.Cells.Find(What:=datatoFind, LookIn:=xlValues, LookAt:=xlPart)

    If Not c Is Nothing Then
        MySheet = IIf(InStr(c.Parent.Name, "."), Split(c.Parent.Name, ".")(0), Split(c.Parent.Name)(0))
        FV = MySheet & "." & pageNum(c)
    Else
        FV = "VALUE NOT FOUND"
    End If

    With aSh.Cells(5, 2)
        .Value = FV
        .Font.Name = "Times New Roman"
        .Font.Bold = True
        .Font.Size = "10"
        .Font.Color = vbRed
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With

End Sub

Function pageNum(rng As Range)
    Dim vA, hA
    Dim iRow As Long, iCol As Long, i As Long
    Dim pg As String

    With rng.Parent
        If .HPageBreaks.Count > 0 Then
            ReDim hA(0 To .HPageBreaks.Count)
            hA(0) = 1
            For i = 1 To UBound(hA)
                hA(i) = .HPageBreaks(i).Location.Row
            Next
        Else
            ReDim hA(0 To 0)
            hA(0) = 0
        End If
        If .VPageBreaks.Count > 0 Then
            ReDim vA(0 To .VPageBreaks.Count)
            vA(0) = 1
            For i = 1 To UBound(vA)
                vA(i) = .VPageBreaks(i).Location.Column
            Next
        Else
            ReDim vA(0 To 0)
            vA(0) = 0
        End If
        iRow = Application.Match(rng.Row, hA, 1)
        iCol = Application.Match(rng.Column, vA, 1)
        If .PageSetup.Order = xlDownThenOver Then
            pg = (iCol - 1) * (.HPageBreaks.Count + 1) + iRow
        Else
            pg = (iRow - 1) * (.VPageBreaks.Count + 1) + iCol
        End If
    End With

    pageNum = pg

End Function
 
Last edited:
Upload sample that's able to reproduce your issue. And have expected result manually filled out (with clear label). Without it, bit difficult to see exactly what you are trying to do.
 
The macro is working for the first row "5" but I now want it to move to do the same thing in row 6 and go until it reaches the gray cells. I am trying to get the offset value in the macro to increase by 1, but havent had any luck.
 

Attachments

  • ExampleReferencing.xlsm
    28.3 KB · Views: 1
If you run the code when Sheet "FD2.1" is active.
Code:
Option Explicit
Sub Find_Data()
    Dim datatoFind As String, MySheet As String, FV As String
    Dim aSh As Worksheet, fSh As Worksheet
    Dim c As Range, cel As Range
    Set aSh = Sheet1
    For Each cel In aSh.Range("C5:C" & aSh.Cells(Rows.Count, "C").End(xlUp).Row)
        datatoFind = CStr(cel.Value)
        If Len(datatoFind) = 0 Or Not IsNumeric(datatoFind) Then Exit Sub
      Set c = ActiveSheet.Cells.Find(What:=datatoFind, LookIn:=xlValues, LookAt:=xlPart)
 
      If Not c Is Nothing Then
          MySheet = IIf(InStr(c.Parent.Name, "."), Split(c.Parent.Name, ".")(0), Split(c.Parent.Name)(0))
          FV = MySheet & "." & pageNum(c)
      Else
          FV = "VALUE NOT FOUND"
      End If
        With cel.Offset(, -1)
            .Value = FV
            .Font.Name = "Times New Roman"
            .Font.Bold = True
            .Font.Size = "10"
            .Font.Color = vbRed
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlCenter
        End With
    Next
End Sub

Function pageNum(rng As Range)
    Dim vA, hA
    Dim iRow As Long, iCol As Long, i As Long
    Dim pg As String
    With rng.Parent
        If .HPageBreaks.Count > 0 Then
            ReDim hA(0 To .HPageBreaks.Count)
            hA(0) = 1
            For i = 1 To UBound(hA)
                hA(i) = .HPageBreaks(i).Location.Row
            Next
        Else
            ReDim hA(0 To 0)
            hA(0) = 0
        End If
        If .VPageBreaks.Count > 0 Then
            ReDim vA(0 To .VPageBreaks.Count)
            vA(0) = 1
            For i = 1 To UBound(vA)
                vA(i) = .VPageBreaks(i).Location.Column
            Next
        Else
            ReDim vA(0 To 0)
            vA(0) = 0
        End If
        iRow = Application.Match(rng.Row, hA, 1)
        iCol = Application.Match(rng.Column, vA, 1)
        If .PageSetup.Order = xlDownThenOver Then
            pg = (iCol - 1) * (.HPageBreaks.Count + 1) + iRow
        Else
            pg = (iRow - 1) * (.VPageBreaks.Count + 1) + iCol
        End If
    End With
    pageNum = pg

End Function

EDIT: However, I see that you have some numbers that would appear more than once in your FD2.1 sheet. How are those supposed to be treated? There's no indication of treatment in your sample.
 
I can't reproduce your issue with your sample file.
Restart your computer and test.
If issue persist, upload sample file where you can reproduce the error.
 
Back
Top