1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Davis Henderson, Apr 30, 2018.

  1. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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 (vb):
    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: Apr 30, 2018
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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.
  3. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    Attached is an example file. May not be a great example, but it is quite the struggle getting a file small enough to upload.

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    Code runs fine. There must be something wrong with your original file.
  5. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    Huh? I don't get you.

    In your code you have...
    Code (vb):
    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.
  7. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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.
  8. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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.
  9. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    The .Value is returning the correct number, but that number is not storing in the (datatoFind) variable. Can anyone provide guidance?

    Attached Files:

  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    ... 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: May 1, 2018
  11. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    Great thanks so much for the help! Do you have any idea why the code isnt stopping when the values are found?
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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?
  13. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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 (vb):
    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
     
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
  15. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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?
  16. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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 (vb):
    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 (vb):
    Sub Demo()
    Dim y as String
    y = readRFooter(Sheet1.Name)
    Debug.Print y
    End Sub
  17. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    Still getting the same issue. It is pulling the formula to create the footer and not the final text.

    Attached Files:

  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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.
  19. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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?
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    Provided above assumption is correct...
    Code (vb):
    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

    Attached Files:

  21. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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!

    Last edited: May 7, 2018
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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.
  23. Davis Henderson

    Davis Henderson New Member

    Messages:
    27
    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.

    Attached Files:

  24. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    If you run the code when Sheet "FD2.1" is active.
    Code (vb):
    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.
  25. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    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.

Share This Page