Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

This VBA module finds the Value of H2 and displays all matches as per requirement into…

  1. User

    This VBA module finds the Value of H2 and displays all matches as per requirement into the range H3:H51 For each of the matches found, is it possible to include the address formula in column 'I' as text (for example Day!A3)

    Sub CheckAndCopyValues() Me.Unprotect Password:="edit" ' Unlock the sheet Application.EnableEvents = False Application.ScreenUpdating = False Dim ws As Worksheet Dim lastRow As Long Dim result As String Me.Range("H3:H51").ClearContents ' Get the value in cell H2 Dim searchValue As Variant searchValue = Me.Range("H2").Value ' Loop through each sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Forthnight" Or ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or ws.Name = "Year" Then ' Store the values of column F in an array Dim rngF As Range Dim arrF As Variant Set rngF = ws.Range("F:F") arrF = rngF.Value ' Find the value in the array Dim i As Long For i = LBound(arrF) To UBound(arrF) If arrF(i, 1) = searchValue Then ' Get the row number lastRow = rngF.Cells(i, 1).Row ' Build the result string with sheet name and value of column A result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Paste the result in sheet "Planner" column H With Sheets("Planner") .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = result End With End If Next i End If Next ws Application.ScreenUpdating = True Application.EnableEvents = True Me.Protect Password:="edit" ' Unlock the sheet End Sub

  2. Assistant

    To modify your VBA module to also include the address formula in column 'I' as text for each match found, you can simply extend your existing logic. Below, I've added a line to assign the address of the matched cell in column 'I' alongside the result in column 'H'. Here's the modified code:

    Sub CheckAndCopyValues()
        Me.Unprotect Password:="edit" ' Unlock the sheet
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim result As String
        
        ' Clear contents of H3:H51
        Me.Range("H3:H51").ClearContents
        Me.Range("I3:I51").ClearContents ' Clear column I as well
        
        ' Get the value in cell H2
        Dim searchValue As Variant
        searchValue = Me.Range("H2").Value
        
        ' Loop through each sheet
        For Each ws In ThisWorkbook.Worksheets
            ' Check if the sheet is one of the specified sheets
            If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Forthnight" Or ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or ws.Name = "Year" Then
                ' Store the values of column F in an array
                Dim rngF As Range
                Dim arrF As Variant
                Set rngF = ws.Range("F:F")
                arrF = rngF.Value
                
                ' Find the value in the array
                Dim i As Long
                For i = LBound(arrF) To UBound(arrF)
                    If arrF(i, 1) = searchValue Then
                        ' Get the row number
                        lastRow = rngF.Cells(i, 1).Row
                        
                        ' Build the result string with sheet name and value of column A
                        result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value
                        
                        ' Determine the next empty row in column H (and I)
                        With Sheets("Planner")
                            Dim nextRow As Long
                            nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
                            
                            ' Paste the result in sheet "Planner" column H
                            .Range("H" & nextRow).Value = result
                            
                            ' Paste the address formula in column I as text
                            .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text
                        End With
                    End If
                Next i
            End If
        Next ws
    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Me.Protect Password:="edit" ' Lock the sheet
    End Sub
    

    Modifications Explained:

    1. Clear Column I: Added .Range("I3:I51").ClearContents to ensure column I is clear of any previous data before the search begins.

    2. Next Row Calculation: The nextRow variable is calculated to determine the next empty row in the "Planner" sheet.

    3. Address Formula:

      • The address of the matched cell in column F is constructed with ws.Range("F" & lastRow).Address.
      • An apostrophe is added at the beginning of the address (with "'" & ...) to ensure it is treated as text.

    This will display the found matches in column H and their corresponding cell addresses in column I as requested.

  3. User

    In the VBA below, when I double click on a cell in the range A2:G52, it uses the address formula reference of the Target cell to activate a sheet and also select the specific cell. In column I3:I51 are text values similar to the address formula refernces without the = sign. Can you add the function to this module bellow so that when I double click on a cell in I3:I51 it will activate the sheet and also select the specific cell.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim ws As Worksheet Select Case Target.Value Case "Day": Set ws = ThisWorkbook.Sheets("Day") Case "Week": Set ws = ThisWorkbook.Sheets("Week") Case "Forthnight": Set ws = ThisWorkbook.Sheets("Forthnight") Case "Month": Set ws = ThisWorkbook.Sheets("Month") Case "QtrYear": Set ws = ThisWorkbook.Sheets("QtrYear") Case "HalfYear": Set ws = ThisWorkbook.Sheets("HalfYear") Case "Year": Set ws = ThisWorkbook.Sheets("Year") End Select If Not ws Is Nothing Then Cancel = True ws.Activate Exit Sub End If

    If Not Intersect(Target, Range("A2:G52")) Is Nothing Then
    

    Dim formulaReference As String formulaReference = Target.Formula If Left(formulaReference, 1) = "=" Then formulaReference = Mid(formulaReference, 2) formulaReference = Replace(formulaReference, "'", "") Dim formulaParts As Variant formulaParts = Split(formulaReference, "!") If UBound(formulaParts) >= 1 Then Dim worksheetName As String, cellAddress As String worksheetName = formulaParts(0) cellAddress = Right(formulaReference, Len(formulaReference) - Len(worksheetName) - 1) Dim destinationWorksheet As Worksheet Set destinationWorksheet = ThisWorkbook.Worksheets(worksheetName) If Not destinationWorksheet Is Nothing Then Cancel = True destinationWorksheet.Activate destinationWorksheet.Range(cellAddress).Select End If End If End If End If

    End Sub

  4. Assistant

    To enable double-click functionality in the specified range (I3:I51) that activates a target sheet and selects a specific cell based on text values similar to address formulas, you can modify your Worksheet_BeforeDoubleClick event handler.

    In this updated code, I've added a new condition that checks if the double-clicked cell is within the range I3:I51. If so, it will parse the text string in that cell to extract the sheet name and cell address, activate the corresponding sheet, and select that cell.

    Here's the updated code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws As Worksheet
        Dim formulaReference As String
        
        ' Check if the double-click target is I3:I51
        If Not Intersect(Target, Range("I3:I51")) Is Nothing Then
            ' Get the text value from the clicked cell
            formulaReference = Target.Value
            If Len(formulaReference) > 0 Then
                ' Remove any leading apostrophe
                If Left(formulaReference, 1) = "'" Then 
                    formulaReference = Mid(formulaReference, 2)
                End If
                
                ' Split at "!"
                Dim formulaParts As Variant
                formulaParts = Split(formulaReference, "!")
                If UBound(formulaParts) >= 1 Then
                    Dim worksheetName As String, cellAddress As String
                    worksheetName = formulaParts(0)
                    cellAddress = formulaParts(1)
                    
                    ' Get the target worksheet
                    On Error Resume Next
                    Set ws = ThisWorkbook.Worksheets(worksheetName)
                    On Error GoTo 0
                    
                    ' If the sheet exists, activate it and select the cell
                    If Not ws Is Nothing Then
                        Cancel = True
                        ws.Activate
                        ws.Range(cellAddress).Select
                    End If
                End If
            End If
            Exit Sub
        End If
    
        ' Existing code to handle double-click in range A2:G52
        Select Case Target.Value
            Case "Day": Set ws = ThisWorkbook.Sheets("Day")
            Case "Week": Set ws = ThisWorkbook.Sheets("Week")
            Case "Forthnight": Set ws = ThisWorkbook.Sheets("Forthnight")
            Case "Month": Set ws = ThisWorkbook.Sheets("Month")
            Case "QtrYear": Set ws = ThisWorkbook.Sheets("QtrYear")
            Case "HalfYear": Set ws = ThisWorkbook.Sheets("HalfYear")
            Case "Year": Set ws = ThisWorkbook.Sheets("Year")
        End Select
    
        If Not ws Is Nothing Then
            Cancel = True
            ws.Activate
            Exit Sub
        End If
    
        If Not Intersect(Target, Range("A2:G52")) Is Nothing Then
            formulaReference = Target.Formula
            If Left(formulaReference, 1) = "=" Then
                formulaReference = Mid(formulaReference, 2)
                formulaReference = Replace(formulaReference, "'", "")
                Dim formulaParts As Variant
                formulaParts = Split(formulaReference, "!")
                If UBound(formulaParts) >= 1 Then
                    Dim worksheetName As String, cellAddress As String
                    worksheetName = formulaParts(0)
                    cellAddress = Right(formulaReference, Len(formulaReference) - Len(worksheetName) - 1)
                    Dim destinationWorksheet As Worksheet
                    Set destinationWorksheet = ThisWorkbook.Worksheets(worksheetName)
                    If Not destinationWorksheet Is Nothing Then
                        Cancel = True
                        destinationWorksheet.Activate
                        destinationWorksheet.Range(cellAddress).Select
                    End If
                End If
            End If
        End If
    
    End Sub
    

    Key Changes and Features:

    1. New Condition for I3:I51: The code checks if the double-clicked cell is within the range I3:I51, and processes it accordingly to extract the worksheet name and cell address.

    2. Text Handling: The code correctly handles the content in column I by removing any leading apostrophe and processing the string for split operations.

    3. Error Handling: The use of On Error Resume Next prevents runtime errors when trying to set a worksheet object. If the worksheet doesn't exist, it gracefully moves on.

    4. Cell Selection: If the specified sheet exists, it activates the sheet and selects the referenced cell indicated in the clicked cell of column I.

    This should fulfill the requirement of activating a sheet and selecting a specific cell upon double-clicking cells in both A2:G52 and I3:I51.

  5. User

    Thanks. It worked brilliantly :)

  6. Assistant

    You're welcome! I'm glad to hear that it worked well for you. If you have any more questions or need further assistance with VBA or anything else, feel free to ask. Happy coding! 😊