logo
down
shadow

link Open/Libre Office button to cell and reference cell in macro


link Open/Libre Office button to cell and reference cell in macro

By : Simon Norris
Date : November 19 2020, 12:41 AM


Share : facebook icon twitter icon
Excel macro to find text, find a reference cell, copy data in a fixed position from the reference 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...
code :
Sub GetZip()

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".
        End If
    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.





Exit Sub
wksheetError:
    MsgBox ("The worksheet was not found.")
    Exit Sub

noText1:
    MsgBox ("""" & Text1 & """ was not found in the worksheet.") 'Display an error message. **NOTE: Double-quotations acts as a single quotation in strings.
    Exit Sub

End Sub
Macro to find paragraphs in text selection in Open/Libre/Neo Office

Macro to find paragraphs in text selection in Open/Libre/Neo Office


By : CodeWizards
Date : March 29 2020, 07:55 AM
should help you out The oSel.Text is a shortcut for oSel.getText() which "Returns the text interface in which the text position is contained." https://www.openoffice.org/api/docs/common/ref/com/sun/star/text/XTextRange.html#getText
So to get a ParagraphEnumeration only from the Selection, you should use oPE = oSel.createEnumeration() instead of oPE = oSel.Text.createEnumeration().
Libre Office Maximum numbers of cell

Libre Office Maximum numbers of cell


By : user3640568
Date : March 29 2020, 07:55 AM
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

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:
code :
Option Explicit

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
            Exit Sub
        End If

        '// Activate the sheet
        wsTarget.Activate

        '// Cancel the default action for double-click
        Cancel = True

    End If


End Sub
Option Explicit

Sub Add_links()

        '
        ' 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


End Sub

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

End Function
Change cell reference dynamically based on last filled cell using Office 2007

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:
code :
= ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))
= "$C$2:" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))
= ADDRESS(2,3) & ":" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))
Related Posts Related Posts :
  • How to show downshift + popper on top of material-ui dialog?
  • jQuery file upload and RequireJS configuration
  • How to send the result of a select query to a message body of a mail in oracle 10G
  • Worklight common build failing with "Failed to update main HTML file"
  • pg_listening_channels() is not returning the channels name
  • Asset management in ZF2
  • Does the Firefox add-on sdk allow direct modification of the http response byte stream?
  • How to remove menu hardware key from your android app
  • Identifying programming language
  • Use shell commands to find Makefile.am in configure.ac
  • Mono Compiler as Service or Microsoft Roselyn for a vb parser
  • How to add extra root nodes for not well formed XML structure?
  • which Uncrustify setting replaces blank lines with indenting spaces?
  • mac OSX Lion Homebrew install curl (77)
  • In Project Euler 47, why is 2^2 considered a prime number distinct from 2?
  • browserstack requesting localhost:45691
  • What was the real reason why Google is chosing RenderScript instead of OpenCL?
  • Mandrill Inbound Email routing
  • Prevent checkElementIndex() Guava function from concatenating additional response to existing error message
  • Arduino and Raspberry Pi Serial communication + multiple variables
  • convert a 960 grid based site to responsive
  • Should it be possible to have more than one DocuSign account (DEMO) with the same email address?
  • Is it possible to limit ammount of concurrent builds in Travis-CI
  • Selecting languages with specific ISO code
  • Deprecated vs Unsupported SDK
  • Verifying ClearCase files have been labeled properly
  • What's the difference between "Bag of Words" and "Bag of features" in computer vision?
  • Is there a way to tell Serde to use a struct field as a map's key?
  • ld:framework not found sfml
  • nice, go-idiomatic way of using a shared map
  • IzPack ChmodInstallerListener.jar
  • Breaking down tasks of user stories between developer and QA
  • Dropwizard service not starting properly
  • How to override devise invitable actions
  • Coded UI. How can I change TimeOut in Find() method
  • Why when I click on the update button error TypeError: r is undefined happen?
  • Visio Component Diagram - Required Interface
  • Lucene: fast(er) to get docs in bulk?
  • can I use windows 8 font (Segoe UI)for my web app?
  • Using Flask Session in Gevent Socket-IO
  • Difference between recommended and suggested cookbooks
  • Dynamic Forms (Formsets) in Flask / WTForms?
  • Image Servlet doesn't want to show image in browser (FireFox, IE..) but in Eclipse browser works?
  • Logback - how to get each logger logging to a separate log file?
  • In Crystal Reports, how do I keep a row from printing if the value is null?
  • iOS 6 Audio multi-route - use external microphone AND internal speaker simultaneously
  • Adding Comments in JasperReports template (jrxml)
  • Servicestack mini profiler
  • Logging with Castle Windsor, the Logging Facility and log4net
  • Subscribe for instances list update in GCE autoscaled group
  • Generating a unique QR code for each Order in Woocommerce
  • libvirtError: internal error Cannot find suitable CPU model for given data
  • CKEditor changing content automatically
  • Pass double pointer in a struct to CUDA
  • Apache Camel for TCP based streamer
  • How to convert old CDC mainframe PRUs to bytes?
  • compilation on Blue Gene Q - ELF header
  • Set border of a jasper subreport having a title and a detail band
  • How to highlight tpl file?
  • cygwin: Starting cron as a service (access denied)
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org