• 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

Chihiro said:
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.
Click to expand...
Code: 
Sub Find_Dat()
    Dim datatoFind As String, MySheet As String, FV As String
    Dim aSh As Worksheet, fSh As Worksheet
    Dim firstResult As Range
    Dim secondResult As Range
    Dim rng As Range
    Dim LeftCell As Range
    Dim leftValue As String
    Dim RowCount As Integer
    Dim rw As Long
 
    sheetNumber = ActiveWorkbook.Sheets.Count
    For counter = 1 To sheetNumber
        Sheets(counter).Activate
    Set rng = Cells.Find(What:="First Prior Year QRE", after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
    If Not rng Is Nothing Then
    Exit For
    Else
    End If
    Next counter
    Set LeftCell = rng.Offset(0, -1)
    leftValue = LeftCell.Value
    If leftValue = "Ref." Then
    For rw = 1 To 3
    Set findValue = rng.Offset(rw, 0)
 
    datatoFind = findValue
    sheetCount = ActiveWorkbook.Sheets.Count
    If Len(datatoFind) = 0 Or Not IsNumeric(datatoFind) Then Exit Sub
    For counter = 1 To sheetCount
        Sheets(counter).Activate
    Set firstResult = Cells.Find(What:=datatoFind, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    Set secondResult = Cells.FindNext(firstResult)
    If Not firstResult Is Nothing Then
        MySheet = IIf(InStr(secondResult.Parent.Name, "."), Split(secondResult.Parent.Name, ".")(0), Split(secondResult.Parent.Name)(0))
        FV = MySheet & "." & pageNum(secondResult)
    Else
    End If
    Next counter
    With rng.Offset(rw, -1)
        .Value = FV
        .Font.Name = "Times New Roman"
        .Font.Bold = True
        .Font.Size = "10"
        .Font.Color = vbRed
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
    Next rw
    End If
 
End Sub


I am working to transition the code to another file, but I cannot send over the file. I am getting a Invalid procedure call or argument on the line for 'set second result'. I assume this has something to do with it not finding the first result, but dont know why that would be. Could it be because I have two "counter" statements? any suggestions?
 
... So what sheet do you have active when you start your code?
And why do you insist on using Activate?

If your actual need to search in multiple sheets, then please, upload sample file to reflect that. It's so much easier for me to debug something when I have sample that's representative of your actual workbook.
 
I am sorry for jumping around so much, but I figured it out have it working now. Curious though, say I had another column like "Beer Exp" like in the picture. Why cant I just change the name of first "Wage QRE Exp" search to "Beer Exp" and have it work? It wont find "Beer Exp" for some reason.
 

Attachments

  • Beer exp.PNG
    Beer exp.PNG
    13.5 KB · Views: 3
Davis Henderson said:
I am sorry for jumping around so much, but I figured it out have it working now. Curious though, say I had another column like "Beer Exp" like in the picture. Why cant I just change the name of first "Wage QRE Exp" search to "Beer Exp" and have it work? It wont find "Beer Exp" for some reason.
Click to expand...
nevermind all good.
 
Hi Chihiro, probably thought you were finished dealing with me, but I'm back :). The macro is working great, but the sheetnumber function works properly about half the time. Often it will just say for example, a value on the 3rd page, FD1.1 instead of FD1.3. Then I can rerun it, and it will work. Any thoughts as to why this is happening?


Chihiro said:
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
Click to expand...
 
If you are using code that I gave you. I can't think of reason why.

If you added some other stuff to the code (like you did in post #26). Then likely Activate etc is the culprit.

However, as I have stated before... without sample workbook that accurately represent your workbook structure (including multiple page) along with your "current" code. Can't really help you.
 
Chihiro said:
Parent is always the worksheet that's active at runtime
Click to expand...
Code: 
    Set secondResult = Cells.FindNext(firstResult)
    Debug.Print secondResult.Address
        MySheet = IIf(InStr(secondResult.Parent.Name, "."), Split(secondResult.Parent.Name, ".")(0), Split(secondResult.Parent.Name)(0))
        FV = MySheet & "." & pageNum51(secondResult)
    Else
    End If
    Next counter
    With rng3.Offset(rw, -1)
        .Value = FV
        .Font.Name = "Times New Roman"
        .Font.Bold = True
        .Font.Size = "10"
        .Font.Color = vbRed
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
lastLine:
    Next rw
    End If
   
End Sub

Function pageNum51(rng3 As Range)
    Dim vA, hA
    Dim iRow As Long, iCol As Long, i As Long
    Dim pg51 As String
    With rng3.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(rng3.Row, hA, 1)
        iCol = Application.Match(rng3.Column, vA, 1)
        If .PageSetup.Order = xlDownThenOver Then
            pg51 = (iCol - 1) * (.HPageBreaks.Count + 1) + iRow
        Else
            pg51 = (iRow - 1) * (.VPageBreaks.Count + 1) + iCol
        End If
    End With
    pageNum51 = pg51

End Function

Okay so this much of it I can show. When I print the address of 'secondResult' it is exactly right, but the pageNum function is working about half the time. Can you see anything that could be causing this?
 
Instead of just printing .Address. Use following to check the parent of secondResult.

Code: 
Debug.Print secondResult.Parent.Name & "," & secondResult.Address

But then again, without seeing full context can't really help you. Or at least upload sample workbook, which is representative of your actual (desensitized values/info). I'll not be responding further without sample workbook. I've not been able to reproduce your issue, using your previous sample.
 
You must log in or register to reply here.
Back
Top