This VBA module finds the Value of H2 and displays all matches as per requirement into…
- 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
- 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 SubModifications Explained:
-
Clear Column I: Added
.Range("I3:I51").ClearContentsto ensure column I is clear of any previous data before the search begins. -
Next Row Calculation: The
nextRowvariable is calculated to determine the next empty row in the "Planner" sheet. -
Address Formula:
- The address of the matched cell in column
Fis constructed withws.Range("F" & lastRow).Address. - An apostrophe is added at the beginning of the address (with
"'" & ...) to ensure it is treated as text.
- The address of the matched cell in column
This will display the found matches in column
Hand their corresponding cell addresses in columnIas requested. -
- 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 ThenDim 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
- 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_BeforeDoubleClickevent 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 SubKey Changes and Features:
-
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.
-
Text Handling: The code correctly handles the content in column I by removing any leading apostrophe and processing the string for
splitoperations. -
Error Handling: The use of
On Error Resume Nextprevents runtime errors when trying to set a worksheet object. If the worksheet doesn't exist, it gracefully moves on. -
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.
-
- User
Thanks. It worked brilliantly :)
- 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! 😊