zero appears in the Msg box

[KlausW]

Hi everyone In an Msg box, some information from some cells has, see the VBA code.
This works well, but when the cells are empty a zero appears in the Msg box see image, can I hide them?

Any help will be appreciated

Best regards Klaus W

Sub Afrundetrektangel1_Klik()

If Range("ai52") = 0 Then Range("ai75") = ""

MsgBox Range("Ai52") & vbNewLine & Range("Ai53") & vbNewLine & Range("Ai54") & vbNewLine & Range("Ai55") & vbNewLine & Range("Ai56") & vbNewLine & Range("Ai57") & vbNewLine & Range("Ai58") & vbNewLine & Range("Ai59") & vbNewLine & Range("Ai60") & vbNewLine & Range("Ai61") & vbNewLine & Range("Ai62") & vbNewLine & Range("Ai63") & vbNewLine & Range("Ai64") & vbNewLine & Range("Ai65") & vbNewLine & Range("Ai66") & vbNewLine & Range("Ai67") & vbNewLine & Range("Ai68") & vbNewLine & Range("Ai69") & vbNewLine & Range("Ai70") & vbNewLine & Range("Ai71") & vbNewLine & Range("Ai72") & vbNewLine & Range("Ai73") & vbNewLine & Range("Ai74") & vbNewLine & Range("Ai75"), , "Symbol oversigt tjeneste-frihed"

Sheets(Format(Now, "mmmm")).Select 

End Sub

 

[Peter_SSs]

If the cells are empty they do not show as 0 for me with your code. Are you sure that they do not contain 0 values, perhaps hidden by formatting?

Could we have some of your sample data with XL2BB for testing?

 

[KlausW]

If the cells are empty they do not show as 0 for me with your code. Are you sure that they do not contain 0 values, perhaps hidden by formatting?

Could we have some of your sample data with XL2BB for testing?

Hi Peter, yes the cells contain formulas. Can it help with understanding? Klaus W

 

[Peter_SSs]

Hi Peter, yes the cells contain formulas.

.. and do those formulas return zero in what you are calling 'empty' cells?
Could you post one of those formulas?


This would help even more ..

Could we have some of your sample data with XL2BB for testing?

 

[KlausW]

.. and do those formulas return zero in what you are calling 'empty' cells?
Could you post one of those formulas?


This would help even more ..

This is the formula in the celles =April!AI55

 

[KlausW]

Hi Peter
Is it like this

PBM.xlsm
	A		C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
5	April 2024
6	MANR:		150803			PBM			Oversergent					Klaus Wittusen															FRH						Tjenesteliste For Klaus Wittusen
7	Uge nr.		14							15							16							17
8			2. Påskedag
10			Mandag	Tirsdag	Onsdag	Torsdag	Fredag	Lørdag	Søndag	Mandag	Tirsdag	Onsdag	Torsdag	Fredag	Lørdag	Søndag	Mandag	Tirsdag	Onsdag	Torsdag	Fredag	Lørdag	Søndag	Mandag	Tirsdag	Onsdag	Torsdag	Fredag	Lørdag	Søndag	Mandag	Tirsdag	Onsdag
11			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
35	Planlagt TJ.			T	T	T	T			T	T	T	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S
36	Faktisk Tjeneste			T	T	T	T			T	T	T	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S	S		S	S
37	Fra
38	Til
50
51			Tjenesterejser indland uden overnatning:															Planlagt udbetaling							FG		EV		FTS		ESØ			####	Symboler
52			Dato:			Fra KL:		Til KL:		Dato:			Fra KL:		Til KL:			Planlagt																	S for Sejlads
53																																			V for Vagt
54																		Faktisk																	K for Kursus
55																																			FE for Ferie
56																																			M for AFS MF
57																																			A for AFS ESØ
58																																			AF for AFS. FTG
59																																			AW for AFS. Weekend døgn
60	Wsoft©																																		AFS. for Vagt døgn
61																																			P for AFS. P 17
62																																			SU for Udlån til anden enhed
63																																			T for Tjeneste
64																																			O for Orlov/Barsel/Omsorgsdage
65																																			SY for SYGEMELDT
66																																			FA for TRANRÆKKEFØLGE
67																																			KFT for Feriekompensationstimer
68																																			SF for særlig ferie
69
70
71
72
73
74
75
April

Cell Formulas
Range	Formula
A5	A5	=PROPER(TEXT(A2,"mmmm")) & " " & YEAR(A2)
C6	C6	=IFERROR(INDEX(Navn!$A$3:$E$17,MATCH($N$6,Navn!$D$3:$D$17,0),1),"")
F6	F6	=IFERROR(VLOOKUP($C$6,Navn!$A$3:$F$16,2,FALSE),"")
I6	I6	=IFERROR(VLOOKUP($C$6,Navn!$A$3:$F$16,3,FALSE),"")
N6	N6	=Navn!$C$1
AC6	AC6	=IFERROR(VLOOKUP($C$6,Navn!$A$3:$F$16,5,FALSE),"")
AI6	AI6	=PROPER(C4)&" "&N6
C7:AD7	C7	=IF(C10="Mandag",WEEKNUM(C9,1),"")
C8:H8	C8	=HelligdagsNavn(C9,0,0)
C10:AG10	C10	=PROPER(TEXT(C9,"dddd"))
C11:AG11	C11	=DAY(C9)
AH51	AH51	=Navn!AH10
A52	A52	=SUM(A53:A59)
AI51:AI75	AI51	='D:\[BM.xlsm]April'!AI52

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C10:AG11	Expression	=MÅNED(C$9)<>MÅNED($A$2)	text	NO
11:11,A10:AG10,AI10:XFD10,AH51	Expression	=A$3	text	YES
C10:AG11	Expression	=OG(UGEDAG(C$9;2)>5;MÅNED(C$9)=MÅNED($A$2))	text	NO

 

[Peter_SSs]

Thanks for the XL2BB sample data - much more useful.

Unfortunately, it does not directly resolve why you are getting those 0 values. Having copied your data (not formulas since I do not have the linked files or worksheets) to my test worksheet and running your code from post 1, this is what I get.

However, I think there may be a clue or two

... and do those formulas return zero in what you are calling 'empty' cells?

You did not answer that question of mine, but I think the answer may be 'yes' and that the cells are formatted to hide zero values. The reason that I am guessing that is that in your mini sheet above, cell A52 contains the formula.
=SUM(A53:A59)
Such a formula must return either a number or an error, it cannot return a null string "". Since I cannot see any values or formulas in A53:A59 I assume that the sum formula is returning a 0, hidden by formatting. I am guessing then the same sort of thing might be happening in col AI.

Another thing that suggests the same is this line in your code ..

If Range("ai52") = 0 Then Range("ai75") = ""

.. indicating the possibility of a 0 in col AI, not ""

Based on the above observations, see what this code does

Sub Afrundetrektangel1_Klik_v2()
  If Range("AI52") = 0 Then Range("AI75") = ""
  MsgBox Replace(Join(Filter(Split("|" & Join(Application.Transpose(Range("AI52:AI75").Value), "|#|") & "|", "#"), "|0|", False), vbLf), "|", ""), , "Symbol oversigt tjeneste-frihed"
  Sheets(Format(Date, "mmmm")).Select
End Sub

 

[KlausW]

Thanks for the XL2BB sample data - much more useful.

Unfortunately, it does not directly resolve why you are getting those 0 values. Having copied your data (not formulas since I do not have the linked files or worksheets) to my test worksheet and running your code from post 1, this is what I get.

View attachment 116239

However, I think there may be a clue or two

You did not answer that question of mine, but I think the answer may be 'yes' and that the cells are formatted to hide zero values. The reason that I am guessing that is that in your mini sheet above, cell A52 contains the formula.
=SUM(A53:A59)
Such a formula must return either a number or an error, it cannot return a null string "". Since I cannot see any values or formulas in A53:A59 I assume that the sum formula is returning a 0, hidden by formatting. I am guessing then the same sort of thing might be happening in col AI.

Another thing that suggests the same is this line in your code ..

If Range("ai52") = 0 Then Range("ai75") = ""

.. indicating the possibility of a 0 in col AI, not ""

Based on the above observations, see what this code does

Sub Afrundetrektangel1_Klik_v2()
  If Range("AI52") = 0 Then Range("AI75") = ""
  MsgBox Replace(Join(Filter(Split("|" & Join(Application.Transpose(Range("AI52:AI75").Value), "|#|") & "|", "#"), "|0|", False), vbLf), "|", ""), , "Symbol oversigt tjeneste-frihed"
  Sheets(Format(Date, "mmmm")).Select
End Sub

Good morning Peter_SSs
Thank you very much as it should be.
Have a nice day, many greetings from Denmark, Klaus W

 

[Alex Blakenburg]

I could be wrong but this seems to give the same result.

Sub Afrundetrektangel1_Klik_v2()
  If Range("AI52") = 0 Then Range("AI75") = ""
  MsgBox Join(Filter(Application.Transpose(Range("AI52:AI75").Value), 0, False), vbLf), , "Symbol oversigt tjeneste-frihed"
  Sheets(Format(Date, "mmmm")).Select
End Sub

 

[Peter_SSs]

I could be wrong but this seems to give the same result.

Sub Afrundetrektangel1_Klik_v2()
  If Range("AI52") = 0 Then Range("AI75") = ""
  MsgBox Join(Filter(Application.Transpose(Range("AI52:AI75").Value), 0, False), vbLf), , "Symbol oversigt tjeneste-frihed"
  Sheets(Format(Date, "mmmm")).Select
End Sub

Too risky I would say, Alex. vba Filter is a "contains" (or "does not contain") filter.
Example: If AI61 was "P for AFS. P 70" instead of "P for AFS. P 17" then it would be omitted from the message box.

Good morning Peter_SSs
Thank you very much as it should be.
Have a nice day, many greetings from Denmark, Klaus W

You're welcome. Thanks for the follow-up. I think we would be going on for ages trying to solve the issue without the XL2BB sample so please consider always giving some representative sample data and the expected results with XL2BB in any new threads you start. It will generally get you faster and better responses.