With these it helps You can find the cell containing the button from a handler as follows:
Dim nCount As Long
Dim i As Long
sControlName = oEvent.source.model.Name
oSheet = thiscomponent.currentcontroller.activesheet
nCount = oSheet.drawpage.count
oPage = oSheet.drawpage
For i = 0 To nCount - 1
oShape = oPage.getbyindex(i)
'oControlShape = oPage.getbyindex(i).control
If (oShape.supportsService("com.sun.star.drawing.ControlShape")) Then
If oShape.control.Name = sControlName Then
oAnchor = oShape.anchor
If (oAnchor.supportsService("com.sun.star.sheet.SheetCell")) Then
Print "Button is anchored in cell: " + oAnchor.AbsoluteName
Dim oOldSelection 'The original selection of cell ranges
Dim oRanges 'A blank range created by the document
Dim oActiveCell 'The current active cell
Dim oConv 'The cell address conversion service
oDoc = ThisComponent
REM store the current selection
oOldSelection = oDoc.CurrentSelection
REM Create an empty SheetCellRanges service and then select it.
REM This leaves ONLY the active cell selected.
oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
REM Get the active cell!
oActiveCell = oDoc.CurrentSelection
oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = oActiveCell.getCellAddress
REM Restore the old selection, but lose the previously active cell
Excel macro to find text, find a reference cell, copy data in a fixed position from the reference cell
By : cleiton cleiton
Date : March 29 2020, 07:55 AM
it helps some times I see that you are still over-using "Activate" and "Select". These are common mistakes when you are just starting out. As I mentioned in my answer to another StackOverflow question, you should try to avoid doing that. I went ahead and created a macro that I think will do what you've asked, and I included comments which should explain each line of code. This way, you can also see how the code works in case you want to recreate or modify it. Let me know if it gives you any trouble...
Dim Report As Worksheet, bReport As Workbook, Report2 As Worksheet 'Create your worksheet and workbook variables.
Dim i As Integer, k As Integer, j As Integer, m As Integer 'Create some variables for counting.
Dim iCount As Integer, c As Integer 'This variable will hold the index of the array of "Text1" instances.
Dim myDate As String, Text2 As String, Text1 As String, Data_Test As String, Data2 As String 'Create some string variables to hold your data.
Dim rText1() As Integer 'Create an array to store the row numbers we'll reference later.
Dim r As Range 'Create a range variable to hold the range we need.
' Below are three variables: Text1, Data_Test, and Data2.
' These represent variables in your specific scenario that I did not know what to put. Change them accordingly.
'Enter your "Text1" value below (e.g., "Housing Counseling Agencies")
Text1 = "Text1" 'Assign the text we want to search for to our Text1 variable.
'Enter the names of your two worksheets below
Data_Test = "Data_Test" 'Assign the name of our "Data_Test" worksheet.
Data2 = "Data2" 'Assign the name of our "Data2" worksheet.
' This assigns our worksheet and workbook variables.
On Error GoTo wksheetError 'Set an error-catcher in case the worksheets aren't found.
Set bReport = Excel.ActiveWorkbook 'Set your current workbook to our workbook variable.
Set Report = bReport.Worksheets(Data_Test) 'Set the Data_Test worksheet to our first worksheet variable.
Set Report2 = bReport.Worksheets(Data2) 'Set the Data2 worksheet to our second worksheet variable.
On Error GoTo 0 'Reset the error-catcher to default.
' This gets an array of row numbers for our text.
iCount = Application.WorksheetFunction.CountIf(Report.Columns("A"), Text1) 'Get the total number of instances of our text.
If iCount = 0 Then GoTo noText1 'If no instances were found.
ReDim rText1(1 To iCount) 'Redefine the boundaries of the array.
i = 1 'Assign a temp variable for this next snippet.
For c = 1 To iCount 'Loop through the items in the array.
Set r = Report.Range("A" & i & ":A" & Report.UsedRange.Rows.Count + 1) 'Get the range starting with the row after the last instance of Text1.
rText1(c) = r.Find(Text1).Row 'Find the specified text you want to search for and store its row number in our array.
i = rText1(c) + 1 'Re-assign the temp variable to equal the row after the last instance of Text1.
Next c 'Go to the next array item.
' This loops through the array and finds the date and Text2 values, then places them in your new sheet.
For c = 1 To iCount 'Loop through the array.
k = rText1(c) 'Assign the current array-item's row to k.
For i = k To 1 Step -1 'Loop upward through each row, checking if the value is a date.
If IsDate(Report.Cells(i, 1).Value) Then 'If the value is a date, then...
myDate = Report.Cells(i, 1).Value 'Assign the value to our myDate variable.
j = i 'Set the j variable equal to the current row (we want to use it later).
Exit For 'Leave the loop since we've found our date value. **Note: jumps to the line after "Next i".
Next i 'Go to the next row value.
Text2 = Report.Cells(j - 6, 1).Value 'Subtract the date row by six, and store the "Text2"/[city, state, zip] value in our Text2 variable.
m = Report2.Cells(Report2.UsedRange.Rows.Count + 1, 1).End(xlUp).Row + 1 'Get the row after the last cell in column "A" that contains a value.
Report2.Cells(m, 1).Value = Text2 'Paste the value of the city,state,zip into the first available cell in column "A"
Next c 'Go to the next array-item.
MsgBox ("The worksheet was not found.")
MsgBox ("""" & Text1 & """ was not found in the worksheet.") 'Display an error message. **NOTE: Double-quotations acts as a single quotation in strings.
Macro to find paragraphs in text selection in Open/Libre/Neo Office
To fix the issue you can do Select the cells of interest then use Data > Validity to open the validity dialog. On the Criteria tab, set the Allow drop down to "Text Length". Set Data to less than or equal
Excel VBA macro. How to write an absolute cell reference into a cell
By : D.Menzies
Date : March 29 2020, 07:55 AM
wish help you to fix your issue This approach avoids using hyperlinks (which I find hard to maintain) and instead uses the BeforeDoubleClick event of the index sheet to provide equivalent functionality. This code goes into the code module for the Dive Index worksheet, so that it picks up the double-click event on the index:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'// This function is called whenever the sheet is double-clicked
'// It checks the value of the target (selected) cell, to see if it is a reference
'// to a dive sheet. If so, it activates that sheet
'// Get hold of the contents of the target cell
Dim sTarget As String: sTarget = Target.Cells(1, 1).Value
'// Check if it refers to a dive sheet
If Left(sTarget, 5) = "Dive " Then
Dim wsTarget As Worksheet
On Error Resume Next
'// Try to find the worksheet referred to by the target cell
Set wsTarget = Worksheets(sTarget)
On Error GoTo 0
'// Check that a target sheet was found
If wsTarget Is Nothing Then
'// Activate the sheet
'// Cancel the default action for double-click
Cancel = True
' Add_links Macro
' Adds links to the index sheet so it 'fills itself in'...
' Each dive is on the row 9 more than the dive number (bear in mind each log is 50 higher than the previous)
Dim Divenumber As Double
Dim Rownumber As Double
Dim wsDive As Worksheet
Set wsDive = ActiveSheet
'// Dive number is in cell I7 of the sheet
Divenumber = wsDive.Range("I7").Value
'// Make sure the sheet name corresponds to the dive number
Dim sDiveName As String
sDiveName = "Dive " & Divenumber
wsDive.Name = sDiveName
'// Calculate the row number for the index entry
'-- -- Rownumber = Divenumber Mod 50 + 9
'// Use below if dive numbers in sheet run from 1 to 50, not 0 to 49
Rownumber = (Divenumber - 1) Mod 50 + 10
'// Get a reference to the index sheet
Dim wsIndex As Worksheet: Set wsIndex = Worksheets("Dive Index")
'// Get a reference to column A in the index entry row
Dim rLink As Range: Set rLink = wsIndex.Range("A" & Rownumber)
'// Put the Dive name into column A
rLink.Value = sDiveName
'// Reference data from the dive sheet into the index sheet ================
'// Project number (in cell F4)
rLink.Offset(0, 1).Formula = ReferenceToCell(wsDive.Range("F4")) '// Index Column B
'// Task(in cell C7)
rLink.Offset(0, 2).Formula = ReferenceToCell(wsDive.Range("C7")) '// Index Column C
'// Start date (in cell C21)
rLink.Offset(0, 7).Formula = ReferenceToCell(wsDive.Range("C21")) '// Index Column H
'// Start time (in cell E21)
rLink.Offset(0, 8).Formula = ReferenceToCell(wsDive.Range("E21")) '// Index Column I
'// End date (in cell F21)
rLink.Offset(0, 9).Formula = ReferenceToCell(wsDive.Range("F21")) '// Index Column J
'// End time (in cell G21)
rLink.Offset(0, 11).Formula = ReferenceToCell(wsDive.Range("G21")) '// Index Column L
Private Function ReferenceToCell(rCell As Range) As String
'// This function returns a formula that references the value of the given cell
ReferenceToCell = "='" & rCell.Parent.Name & "'!" & rCell.Cells(1, 1).Address
Change cell reference dynamically based on last filled cell using Office 2007
By : user1516746
Date : March 29 2020, 07:55 AM
I wish this helpful for you Excel 2007 (US English version) does not have the LINKS or ADRESS functions. But an English version of the formula which will return the last address in row 2 that contains a value greater than 0: