LibreOffice Calc Macro – Multiple values from vlookup result

This LibreOffice Macro Tutorial will search for data in cells like VLOOKUP function and the result will be stored in a different cells. This problem is picked up from here.

The Calc spreadsheet contains the data like this:

We will search each product_id from A2 to A4 in product_image. Each of the image contains first 3 bytes as the product id. All the matching product ID will be shown together as csv in another cells including product_id and product_name.

The Macro

'REM  *****  BASIC  *****
Sub multiple_vlookup()

' declare variables
	Dim row1, row2, row3, prod_img_str
	row3 = 6
	' loop through all the products
	for row1 = 1 to 3
		' hold the product id and name
		prod_id = ThisComponent.Sheets(0).getCellbyPosition(0,row1)
		prod_nm =  ThisComponent.Sheets(0).getCellbyPosition(1,row1)
		' loop through the image list
		for row2 = 7 to 15
			prod_img = ThisComponent.Sheets(0).getCellbyPosition(0,row2)
			' if matched product list found hold the value
			if prod_id.Value = Mid(prod_img.String,1,3) then
				prod_img_str = prod_img_str & prod_img.String & ","				
			end if
		next
		' put the result value
		result =  ThisComponent.Sheets(0).getCellbyPosition(2,row3)
		result.Value = prod_id.Value
			
		result = ThisComponent.Sheets(0).getCellbyPosition(3,row3)
		result.String = prod_nm.String
			
		result = ThisComponent.Sheets(0).getCellbyPosition(4,row3)
		result.String = prod_img_str
		prod_img_str = ""			
		row3 = row3 + 1

	Next

End Sub

After running this macro, the result will look like this.