Help on modify existing VBA to allow comparison of 2 text cells to work

[ItalianPlatinum]

Hello - running into an annoying problem where my comparison of two columns doesn't work when I check for a simple true/false statement. I am comparing Column K to Column U with formula K13=U13, data is the same but says false.

Column K (format of row K is TEXT)

Column U

    .Range("U13:U" & lr).NumberFormat = "General"
    .Range("U13:U" & lr).Formula = "=IFERROR(VLOOKUP(D13,'NAS D'!$A$6:$Y$10000,8,0),"""")"
    .Range("U13:U" & lr).NumberFormat = "@"

even if i do a sample test it doesn't work - but does if i click into column U. how do I get VBA to do the same and click into each cell to get it to work? To note data set is LARGE over 10k rows. so clicking into each cell would kill performance. any help is appreciated. thanks in advance.

 

[6StringJazzer]

I am guessing that the data in NAS D doesn't really match. Find a spare cells where you can put

=LEN(K13)

and

=LEN(U13)

to see if they match. If they do not match, look for extra spaces either in K13 or in the matching cell in sheet NAS D.

 

[ItalianPlatinum]

I am guessing that the data in NAS D doesn't really match. Find a spare cells where you can put

=LEN(K13)

and

=LEN(U13)

to see if they match. If they do not match, look for extra spaces either in K13 or in the matching cell in sheet NAS D.

They match 9 to 9 same characters and all. Just not sure why I must physically click into column U, then press enter in order for it to work? that part i dont understand.

 

[6StringJazzer]

Well, something is different between those two cells, if you have correctly showed your formula (the code doesn't really matter).

Do you have any way to share your file so we can look at the data?

I must physically click into column U, then press enter

I don't understand what you mean by this. What are you doing that requires this?

 

[ItalianPlatinum]

it is confidential. But I can mock-up a test that interacts the same way

I don't understand what you mean by this. What are you doing that requires this?

At the end of the code I export the sheet removing all formulas. when reviewing what is occurring i manually put the formula in (K13=U13) and compared where it again was "false" but then after clicking into the cell (U13) and pressing enter the condition changed to "TRUE"

 

[ItalianPlatinum]

I think I inadvertently found it. I will test the next few days to validate. the location of my vlookup the column was set as "general." So i think that would make my formatting inconsistent for the compare.

 

[6StringJazzer]

Formatting doesn't matter for a compare.

 

[ItalianPlatinum]

No not the location of the formula where the formula was sourcing for the data. So I think and could be wrong. if it pulls a vlookup of a "general" format then compares it to a text it was failing. i did it on my test ill send it to you shortly.

 

[ItalianPlatinum]

VBA Mockup_formatting.xlsm
	A	B	C	D	E	F	G
1						Current	New
2	other data	other data	UNIQUE SKU	VLOOKUP SKU (GENERAL	VLOOKUP SKU TEXT)	COMPARE GENERAL	COMPARE TEXT
3	1	A	92133390	92133390	92133390	FALSE	TRUE
4	2	B	92133391	92133391	92133391	FALSE	TRUE
5	3	C	92123391	92123391	92123391	FALSE	TRUE
6	4	D	97123391	97123391	97123391	FALSE	TRUE
7	5	E	97123E91	9.7123E+95	97123E91	FALSE	TRUE
8	6	F	971ABE91	971ABE91	971ABE91	TRUE	TRUE
9	7	G	97623391	97623391	97623391	FALSE	TRUE
10	8	H	97623791	97623791	97623791	FALSE	TRUE
11	9	I	97623701	97623701	97623701	FALSE	TRUE
12	10	J	97623711	97623711	97623711	FALSE	TRUE
TEST

Cell Formulas
Range	Formula
D3:D12	D3	=IFERROR(VLOOKUP(B3,Inventory!A:B,2,FALSE),"")
E3:E12	E3	=IFERROR(VLOOKUP(B3,Inventory!D:E,2,FALSE),"")
F3:F12	F3	=IF(B3<>"",C3=D3,"")
G3:G12	G3	=IF(C3<>"",C3=E3,"")

VBA Mockup_formatting.xlsm
	A	B	C	D	E
1	GENERAL	GENERAL		TEXT	TEXT
2	A	92133390		A	92133390
3	B	92133391		B	92133391
4	C	92123391		C	92123391
5	D	97123391		D	97123391
6	E	9.71E+95		E	97123E91
7	F	971ABE91		F	971ABE91
8	G	97623391		G	97623391
9	H	97623791		H	97623791
10	I	97623701		I	97623701
11	J	97623711		J	97623711
Inventory

 

[Peter_SSs]

Column U

.Range("U13:U" & lr).NumberFormat = "General"
.Range("U13:U" & lr).Formula = "=IFERROR(VLOOKUP(D13,'NAS D'!$A$6:$Y$10000,8,0),"""")"
.Range("U13:U" & lr).NumberFormat = "@"

Note that changing the number format of column U to Text after it has a number entered by the formula, does not change that number to text.
So I believe that you are still comparing the number 92133390 to the text "92133390" & hence the FALSE result.

If you want the VLOOKUP to return the number as text, try changing the formula to this and leave the column formatted as General

.Range("U13:U" & lr).Formula = "=IFERROR(VLOOKUP(D13,'NAS D'!$A$6:$Y$10000,8,0)&"""","""")"