Announcement

Collapse
No announcement yet.

$20 criteria based cell value mining

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Re: $20 criteria based cell value mining

    Hi Wigi, Thank you for your help.

    I am happy to add an additional $20 for the error handling description you mentioned earlier once we get this up and running.
    Here is a better description, I added the improved description in red to the existing one.

    Click image for larger version

Name:	Capture5.jpg
Views:	1
Size:	47.5 KB
ID:	1127926

    note the two tables are on separate tabs. So the missing part to the code is some type of Vlookup of the first table offsetting to the value defined in the second tables column reference.




    Originally posted by Wigi View Post
    Hello,

    Please provide correct and descriptive screenshots of your tables, and how the information can be located.

    Also, please use F8 to step through the code and use the debugging tools in VBA to understand what the code is doing (or not).

    Comment


    • #17
      Re: $20 criteria based cell value mining

      Here is the transaction id for the second $20 transaction (10% to ozgrid deposit) ID 7NT72648YG034101F
      Thanks!

      Comment


      • #18
        Re: $20 criteria based cell value mining

        Can anyone help with this?

        Comment


        • #19
          Re: $20 criteria based cell value mining

          Hello,

          Over the weekend I will do the code for the first part.
          If possible also the second part. I will keep you posted.
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #20
            Re: $20 criteria based cell value mining

            Hello there,

            Here's updated coding, also with extensive error handling. Can you test it please and provide feedback, as well as transfer the funds ? Thanks !

            Code:
            Sub FindCell()
            
                '============================================================
                'Adjust to match
                '
                Const sTabNameForSecondTable = "sheet2"      'the name of the sheet where table 2 is located
                Const sStartingCell_TableOnTheRight As String = "J3"    'the address of the cell in the upperleft corner for table 2
                Const tabNameGoesHere     As String = "tab name"    'the sheet name where the cursor will land
                '
                '============================================================
            
                Dim rCell_Selection       As Range
                Dim rCell_Table2          As Range
                Dim rCell_Target          As Range
                Dim sColumnName           As String
                Dim sRowName              As String
                Dim sFileName             As String
                Dim ws                    As Worksheet
                Dim lRow                  As Long
                Dim lColumn               As Long
            
                If SheetExists(sTabNameForSecondTable) = False Then
                    MsgBox "The sheet called '" & sTabNameForSecondTable & "' does not exist in the active workbook.", vbCritical
                    Exit Sub
                End If
            
                Set rCell_Selection = Selection.Cells(1)
            
                sColumnName = Trim(Application.Intersect(rCell_Selection.EntireColumn, rCell_Selection.CurrentRegion.Rows(1)))
                If sColumnName = "" Then
                    MsgBox "The column name for the selected cell (" & rCell_Selection.Address(0, 0) & ") is empty.", vbCritical
                    Exit Sub
                End If
            
                sRowName = Trim(Application.Intersect(rCell_Selection.EntireRow, rCell_Selection.CurrentRegion.Columns(1)))
                If sRowName = "" Then
                    MsgBox "The row name for the selected cell (" & rCell_Selection.Address(0, 0) & ") is empty.", vbCritical
                    Exit Sub
                End If
            
                On Error Resume Next
                Set rCell_Table2 = Worksheets(sTabNameForSecondTable).Range(sStartingCell_TableOnTheRight).CurrentRegion.Columns(1).Find(sColumnName, , xlValues, xlWhole)
                On Error GoTo 0
            
                If rCell_Table2 Is Nothing Then
                    MsgBox "In table 2, first column, we could not find '" & sColumnName & "'. That first column's address is " & _
                           Range(sStartingCell_TableOnTheRight).CurrentRegion.Columns(1).Address(0, 0), vbCritical
                    Exit Sub
                End If
                sFileName = rCell_Table2.Offset(, 1).Value
                If Len(Dir(sFileName)) = 0 Then
            
                    MsgBox "The target file '" & sFileName & "' could not be found. Please verify.", vbCritical
                    Exit Sub
            
                Else
            
                    On Error Resume Next
                    Set ws = Workbooks.Open(sFileName).Worksheets(tabNameGoesHere)
                    On Error GoTo 0
            
                    If ws Is Nothing Then
                        MsgBox "The sheet called '" & tabNameGoesHere & "' could not be accessed.", vbCritical
                        Exit Sub
                    End If
            
                    On Error Resume Next
                    lRow = ws.Columns(1).Find(sRowName).Row
                    On Error GoTo 0
            
                    If lRow = 0 Then
                        MsgBox "The desired row could not be found. In column A of the target worksheet we did a Find of '" & sRowName & "'.", vbCritical
                        Exit Sub
                    End If
            
                    On Error Resume Next
                    lColumn = rCell_Table2.Offset(, 2).Value
                    On Error GoTo 0
            
                    If lColumn = 0 Then
                        MsgBox "The desired column could not be found. To know the column offset (offset starting in column B of target worksheet) " & _
                            "we looked at the 3rd column of table 2.", vbCritical
                        Exit Sub
                    End If
                    
                    On Error Resume Next
                    Set rCell_Target = ws.Cells(lRow, 2).Offset(, lColumn)
                    On Error GoTo 0
            
                    If rCell_Target Is Nothing Then
                        MsgBox "The desired cell could not be accessed. We tried row = " & lRow & ", column = " & lColumn, vbCritical
                        Exit Sub
                    End If
            
                    On Error Resume Next
                    rCell_Target.Interior.ColorIndex = 19
                    Application.Goto rCell_Target, True
                    If Err.Number <> 0 Then
                        Err.Clear
                        On Error GoTo 0
                        MsgBox "The desired cell '" & rCell_Target.Address(0, 0) & "' could not be accessed or highlighted in pink.", vbCritical
                        Exit Sub
                    End If
            
                End If
            
            End Sub
            
            Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
            
                Dim sht                   As Worksheet
            
                If wb Is Nothing Then Set wb = ActiveWorkbook
                On Error Resume Next
                Set sht = wb.Sheets(shtName)
                On Error GoTo 0
                SheetExists = Not sht Is Nothing
            
            End Function
            Regards,

            Wigi

            Excel MVP 2011-2014

            For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

            -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

            Comment


            • #21
              Re: $20 criteria based cell value mining

              Hi Wigi, looks good but I keep getting an error message. after running the test, sColumnName =" ", i.e no value, and the error message shows: The column name for the selected cell (X31) is empty.
              it seems to to pick up the cell value well i.e the rCell_selection fine, but not the column name. Bearing in mind there are other tables all over the sheet both before and after.

              Comment


              • #22
                Re: $20 criteria based cell value mining

                Please add a small testfile such that I can reproduce it.
                Also, please provide the steps that you do when the error occurs.
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment


                • #23
                  Re: $20 criteria based cell value mining

                  Originally posted by Wigi View Post
                  Please add a small testfile such that I can reproduce it.
                  Also, please provide the steps that you do when the error occurs.
                  Hi Wigi,

                  This is the situation with the table 2, as mentioned there are several on one page but only interested in the one highlighted yellow.Click image for larger version

Name:	Capture6.jpg
Views:	1
Size:	24.9 KB
ID:	1127947
                  Attached Files

                  Comment


                  • #24
                    Re: $20 criteria based cell value mining

                    Here is an example workbook the macro would open and highlight cells. file name is same as the link in the example file as an example.
                    Attached Files

                    Comment


                    • #25
                      Re: $20 criteria based cell value mining

                      Hello,

                      I solved it. Your screenshot in post #16 was misleading/incorrect.
                      We have to do lookup the name in the row heading in the table on the other sheet, not the name in the column heading.
                      Anyway, here is the updated coding in attached file.

                      Also, I had to make changes to your file names (\ and also xlsx instead of xls).

                      After these changes it works flawlessly.
                      Attached Files
                      Regards,

                      Wigi

                      Excel MVP 2011-2014

                      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                      Comment


                      • #26
                        Re: $20 criteria based cell value mining

                        Hey Wigi, Sorry actually it was correct. Just the test file I just sent you was incorrect. it is actually looking up the column names. but still unsure how to do this.

                        Comment


                        • #27
                          Re: $20 criteria based cell value mining

                          See correct column naming convention.
                          So on selection the tables column name and left row reference is used as reference to select the correct link in the other sheet to open, an the vlookup is performed on the row reference.

                          hope this helps!
                          Attached Files

                          Comment


                          • #28
                            Re: $20 criteria based cell value mining

                            Newest version of the file attached
                            Attached Files
                            Regards,

                            Wigi

                            Excel MVP 2011-2014

                            For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                            -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                            Comment


                            • #29
                              Re: $20 criteria based cell value mining

                              Click image for larger version

Name:	Captureerror1.JPG
Views:	1
Size:	21.3 KB
ID:	1127959
                              Ran the code but somethings stopping it from working at all

                              Comment


                              • #30


                                Re: $20 criteria based cell value mining

                                This means that in the first column of the table (with address C2:C8) we cannot find 'A'.
                                This A is the column heading of the cell that you select when running the small macro.
                                Regards,

                                Wigi

                                Excel MVP 2011-2014

                                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                                Comment

                                Working...
                                X