LibreOffice Calc Macro – Multiple values from vlookup result

1 min


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:

Multiple Vlookup
Multiple Vlookup

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.

Multiple Vlookup-Result
Multiple Vlookup-Result

Next Step:

More Macro Tutorials


Arindam

Creator and author of debugpoint.com. Connect with me via Telegram, Twitter, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments