Find 1 to 15 Code from Excel

[bobsmith79]

Hi

I’m new to LibreOffice. I was using Excel but I’m trying to switch over to Calc. I was using the code below to find and write. I’m hoping someone could please look at the code and see what adjustments are needed to use it in Calc. Thank you.

• See attached spreadsheet for an example of the code as described below.

Code Notes:

1. The code searches the range B20:B50, E20:E150, J20:J150 and I need to able to add a lot more ranges as time goes on. The code is looking for sets of numbers (eg. 1-15 or 2-10 etc.) and when found uses the first number in the set of numbers. It then searches the range G2:G16 for the first number in the set of found numbers.

In my sample spreadsheet cell B25 contains 1-10 so the code would search range G2:G16 and find cell G2 which contains 1 and would write 1-10 in cell J2 and so on.

That’s it.

Sub LINE1TO15()

 Application.ScreenUpdating = False
Dim LastRow As Long, rng As Range, splitRng As Variant, fnd As Range
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In Range("B20:B50, E20:E150, J20:J150")
    If rng <> "" Then
        splitRng = Split(rng, "-")
        Set fnd = Range("G2:G16").Find(splitRng(0), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(0, 3) = rng.Address(0, 0)
        End If
    End If

count.ods (7.5 KB)

Next rng
Application.ScreenUpdating = True

End Sub

[Villeroy]

menu:Edit>Find&Replace…
Other options
[X] Regular Expressions
Search in: Values
Search: ^1*[0-5]$
[Find All]

[JohnSUN]

Sorry but I don’t understand.
On the one hand, you write

@bobsmith79

and would write 1-10 in cell J2

And on the other hand, this line in your code

@bobsmith79

fnd.Offset(0, 3) = rng.Address(0, 0)

will write something like B25 in J2

[JohnSUN]

For Calc, your code could be like this:

Sub LINE1TO15()
	Call updateFrom1Range("B20:B50", "G2:G16", "J2:J16")
	Call updateFrom1Range("E20:E150", "G2:G16", "J2:J16")
	Call updateFrom1Range("J20:J150", "G2:G16", "J2:J16")
End Sub

Sub updateFrom1Range(sSourceRange As String, sSearchRange As String, sTargetRange As String)
Dim oActiveSheet As Variant
Dim oCellRangeByName As Variant
Dim oData As Variant, oDictionary As Variant, oTarget As Variant
Dim i As Long, j As Integer, iSearchVal As Long 
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	oData = oActiveSheet.getCellRangeByName(sSourceRange).getDataArray()
	oDictionary = oActiveSheet.getCellRangeByName(sSearchRange).getDataArray()
	oCellRangeByName = oActiveSheet.getCellRangeByName(sTargetRange)
	oTarget = oCellRangeByName.getDataArray()
	For i = LBound(oData) To UBound(oData) 
		If Trim(oData(i)(0))<>"" Then
			iSearchVal = CLng(oData(i)(0))
			For j = LBound(oDictionary) To UBound(oDictionary)
				If CLng(oDictionary(j)(0)) = iSearchVal Then oTarget(j)(0) = oData(i)(0)
			Next j
		EndIf
	Next i
	oCellRangeByName.setDataArray(oTarget)
End Sub

[bobsmith79]

Good morning John. How are you? I know I have a long road ahead learning this code but I do study what you do. I will try your code and get back to you but I have a question on the structure (hope that’s the correct terminology). The 3 ranges B20:B50, E20:E150, J20:J150 you wrote Call updateFrom1Range and put each range. In the Excel they used Range but I don’t think that’s a available in Cal so is it always best to list the way you did it? Thank you so much.

[JohnSUN]

@bobsmith79

and I need to able to add a lot more ranges as time goes on

That’s why I wrote this: adding one helper procedure call with another range is easier than editing a long, very long string (it’s easier to get confused there).

[bobsmith79]

Thank you, your code always works. Have a great weekend.
Bob