MSexcel - Revision history

Hdridder at 09:56, 6 May 2020

2020-05-06T09:56:13Z

← Older revision		Revision as of 09:56, 6 May 2020
Line 12:		Line 12:
	The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:		The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:

	<syntaxhighlight lang=~~vbscript~~>		<syntaxhighlight lang=vbnet>
	Sub EmptyCells()		Sub EmptyCells()
	'		'
Line 18:		Line 18:
	'		'
	'		'
	For Each cell In Range("C2:D10)		For Each cell In Range("C2:D10")
	If (cell.Value = "") Then cell.ClearContents		If (cell.Value = "") Then cell.ClearContents
	Next		Next
	End Sub		End Sub
	</syntaxhighlight>		</syntaxhighlight>

Hdridder at 09:54, 6 May 2020

2020-05-06T09:54:13Z

← Older revision		Revision as of 09:54, 6 May 2020
Line 12:		Line 12:
	The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:		The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:

	<syntaxhighlight lang=~~'VBScript'~~>		<syntaxhighlight lang=vbscript>
	Sub EmptyCells()		Sub EmptyCells()
	'		'

Hdridder at 09:49, 6 May 2020

2020-05-06T09:49:35Z

← Older revision		Revision as of 09:49, 6 May 2020
Line 7:		Line 7:
	;<nowiki>=IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))</nowiki>		;<nowiki>=IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))</nowiki>
	:VLOOKUP returns 0 values for empty cells by default. This looks better.		:VLOOKUP returns 0 values for empty cells by default. This looks better.


			==Clear cells with empty string==
			The VLOOKUP formula above sets an empty string if there is no value in the lookup-range (A2:B10). To make these cells really empty create and execute a macro like:

			<syntaxhighlight lang='VBScript'>
			Sub EmptyCells()
			'
			' EmptyCells Macro
			'
			'
			For Each cell In Range("C2:D10)
			If (cell.Value = "") Then cell.ClearContents
			Next
			End Sub
			</syntaxhighlight>

Hdridder at 08:19, 6 May 2020

2020-05-06T08:19:39Z

← Older revision		Revision as of 08:19, 6 May 2020
Line 4:		Line 4:
	:Fill out the value of cell A1 with 0's on the left side.		:Fill out the value of cell A1 with 0's on the left side.
	:TEKST(2,"0000") => 0002		:TEKST(2,"0000") => 0002

			;<nowiki>=IF(LEN(VLOOKUP(D2,A2:B10,2,0))=0,"",VLOOKUP(D2,A2:B10,2,0))</nowiki>
			:VLOOKUP returns 0 values for empty cells by default. This looks better.

Hdridder: Created page with "Some formula's to remember if you have to use Excel ;TEKST(A1,"00000") :Fill out the value of cell A1 with 0's on the left side. :TEKST(2,"0000") => 0002"

2019-02-06T19:28:00Z

Created page with "Some formula's to remember if you have to use Excel ;TEKST(A1,"00000") :Fill out the value of cell A1 with 0's on the left side. :TEKST(2,"0000") => 0002"

New page

Some formula's to remember if you have to use Excel

;TEKST(A1,"00000")
:Fill out the value of cell A1 with 0's on the left side.
:TEKST(2,"0000") => 0002