Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

A sample call:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

Edit:

A sample call:call for specific cells:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

A sample call for entire column:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A:A", "Good", "Good-Bye")
End Sub

To search entire sheet, change last line of macro to oSheet.replaceAll(xSearchDesc)

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

Edit:

A sample call for specific cells:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

A sample call for entire column:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A:A", "Good", "Good-Bye")
End Sub

To search entire sheet, change last line of macro to oSheet.replaceAll(xSearchDesc)

Edit 11/13/2107:

In light of the comments below, here is a second version with added capabilities:

Sub MyFindReplace3(strSheetName, Optional strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByIndex(0)
  xSearchDesc = oSheet.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  If IsMissing(strCellRange) Then
    strCellRange = "*"
  Else
    iResult = InStr(strCellRange, ",")
    iRangeCount = 0
    while iResult > 0
        iRangeCount = iRangeCount + 1
        iResult = InStr(iResult + 1,strCellRange, ",")
    wend
  EndIf
  If strSheetName = "*" Then
    iSheetCount = ThisComponent.Sheets.getCount()
    For x = 0 to iSheetCount -1
        oSheet1 = ThisComponent.Sheets.getByIndex(x)
        oSheet1.replaceAll(xSearchDesc)
    Next
  Else
    If strCellRange = "*" Then
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        oSheet1.replaceAll(xSearchDesc)
    Else
        iResult = InStr(strCellRange, ",")
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        If  iResult = 0 Then
            oData = oSheet1.getCellRangeByName( strCellRange )
            oCellFormatRanges = oData.getCellFormatRanges()
            oRangeSelected = oCellFormatRanges.getByIndex(0)
            oRangeSelected.replaceAll(xSearchDesc)
        Else
            Dim sCellSplit(iRangeCount) As String
            sCellSplit = Split(strCellRange,",")
            For x = 0 to iRangeCount
                oData = oSheet1.getCellRangeByName( sCellSplit(x) )
                oCellFormatRanges = oData.getCellFormatRanges()
                oRangeSelected = oCellFormatRanges.getByIndex(0)
                oRangeSelected.replaceAll(xSearchDesc)
            Next
        EndIf
      EndIf
  EndIf
End Sub

Parameter usage:

  • strSheetName - "SPECIFIC_SHEET" will only look at named sheet; "*" will replace in ALL sheets dis-regarding strCellRange.

  • strCellRange - Now Optional. If missing, will replace all as specified in strSheetName; If "*" will replace all as specified in strSheetName; will accept "A:A" for entire column, "A1:A15" for specifics in column, "A:B" for contiguous columns, or "A:A,C:C" for non-contiguous columns. Note this is sample & other combinations apply.

  • strSearch is the string to search for

  • strReplace is the replacement string

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

Edit:

A sample call for specific cells:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

A sample call for entire column:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A:A", "Good", "Good-Bye")
End Sub

To search entire sheet, change last line of macro to oSheet.replaceAll(xSearchDesc)

Edit 11/13/2107:11/13/2017:

In light of the comments below, here is a second version with added capabilities:

Sub MyFindReplace3(strSheetName, Optional strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByIndex(0)
  xSearchDesc = oSheet.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  If IsMissing(strCellRange) Then
    strCellRange = "*"
  Else
    iResult = InStr(strCellRange, ",")
    iRangeCount = 0
    while iResult > 0
        iRangeCount = iRangeCount + 1
        iResult = InStr(iResult + 1,strCellRange, ",")
    wend
  EndIf
  If strSheetName = "*" Then
    iSheetCount = ThisComponent.Sheets.getCount()
    For x = 0 to iSheetCount -1
        oSheet1 = ThisComponent.Sheets.getByIndex(x)
        oSheet1.replaceAll(xSearchDesc)
    Next
  Else
    If strCellRange = "*" Then
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        oSheet1.replaceAll(xSearchDesc)
    Else
        iResult = InStr(strCellRange, ",")
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        If  iResult = 0 Then
            oData = oSheet1.getCellRangeByName( strCellRange )
            oCellFormatRanges = oData.getCellFormatRanges()
            oRangeSelected = oCellFormatRanges.getByIndex(0)
            oRangeSelected.replaceAll(xSearchDesc)
        Else
            Dim sCellSplit(iRangeCount) As String
            sCellSplit = Split(strCellRange,",")
            For x = 0 to iRangeCount
                oData = oSheet1.getCellRangeByName( sCellSplit(x) )
                oCellFormatRanges = oData.getCellFormatRanges()
                oRangeSelected = oCellFormatRanges.getByIndex(0)
                oRangeSelected.replaceAll(xSearchDesc)
            Next
        EndIf
      EndIf
  EndIf
End Sub

Parameter usage:

  • strSheetName - "SPECIFIC_SHEET" will only look at named sheet; "*" will replace in ALL sheets dis-regarding strCellRange.

  • strCellRange - Now Optional. If missing, will replace all as specified in strSheetName; If "*" will replace all as specified in strSheetName; will accept "A:A" for entire column, "A1:A15" for specifics in column, "A:B" for contiguous columns, or "A:A,C:C" for non-contiguous columns. Note this is sample & other combinations apply.

  • strSearch is the string to search for

  • strReplace is the replacement string

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

Edit:

A sample call for specific cells:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

A sample call for entire column:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A:A", "Good", "Good-Bye")
End Sub

To search entire sheet, change last line of macro to oSheet.replaceAll(xSearchDesc)

Edit 11/13/2017:11/13/2017 (Recent change adds error checking for Sheet name & Cell range):

In light of the comments below, here is a second version with added capabilities:

Sub MyFindReplace3(strSheetName, Optional strCellRange, strSearch, strReplace)
  on error goto SheetError
  oSheet=ThisComponent.Sheets.getByIndex(0)
  xSearchDesc = oSheet.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  If IsMissing(strCellRange) Then
    strCellRange = "*"
  Else
    iResult = InStr(strCellRange, ",")
    iRangeCount = 0
    while iResult > 0
        iRangeCount = iRangeCount + 1
        iResult = InStr(iResult + 1,strCellRange, ",")
    wend
  EndIf
  If strSheetName = "*" Then
    iSheetCount = ThisComponent.Sheets.getCount()
    For x = 0 to iSheetCount -1
        oSheet1 = ThisComponent.Sheets.getByIndex(x)
        oSheet1.replaceAll(xSearchDesc)
    Next
  Else
    If strCellRange = "*" Then
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        oSheet1.replaceAll(xSearchDesc)
    Else
        iResult = InStr(strCellRange, ",")
        oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        on error goto RangeError
        If  iResult = 0 Then
            oData = oSheet1.getCellRangeByName( strCellRange )
            oCellFormatRanges = oData.getCellFormatRanges()
            oRangeSelected = oCellFormatRanges.getByIndex(0)
            oRangeSelected.replaceAll(xSearchDesc)
        Else
            Dim sCellSplit(iRangeCount) As String
            sCellSplit = Split(strCellRange,",")
            For x = 0 to iRangeCount
                oData = oSheet1.getCellRangeByName( sCellSplit(x) )
                oCellFormatRanges = oData.getCellFormatRanges()
                oRangeSelected = oCellFormatRanges.getByIndex(0)
                oRangeSelected.replaceAll(xSearchDesc)
            Next
        EndIf
      EndIf
  EndIf
  Exit sub
  SheetError:
    MsgBox "Specified Sheet name not found."
    Exit sub
  RangeError:
    MsgBox "Error in Cell Range"
End Sub

Parameter usage:

  • strSheetName - "SPECIFIC_SHEET" will only look at named sheet; "*" will replace in ALL sheets dis-regarding strCellRange.

  • strCellRange - Now Optional. If missing, will replace all as specified in strSheetName; If "*" will replace all as specified in strSheetName; will accept "A:A" for entire column, "A1:A15" for specifics in column, "A:B" for contiguous columns, or "A:A,C:C" for non-contiguous columns. Note this is sample & other combinations apply.

  • strSearch is the string to search for

  • strReplace is the replacement string

If this answers your question please click on the ✔ (upper left area of answer).