Assign Chinese character formula to string variable

[donfaison]

I have a table I download weekly of over 200 chinese products, to sort and pick out the dozen values I actually need, I filter the table using the chinese name, then copy and paste to another spreadsheet. This was easy when there were only three but now I'm looking for a way to be able to do this automatically from a list of the chinese characters. An example: gasoline is 汽油, but to sort the table, I have to use an assigned string variable using a formula.

Dim CC as string
CC = ChrW(27773) & ChrW(27833)
ActiveSheet.Range("$A$1:$K$225").AutoFilter Field:=1, Criteria1:=CC

This works to find 汽油. Actually assigning CC directly to the cell containing the Chinese text (CC = Sheets("List").Range("A1").value or CC = Sheets("List").Range("A1").text both return CC = "??")

I would like to be able to reassign the CC variable for each item (as they change on occasion) rather than have separate variables for each item as I now have to, causing me to copy sections of code over anytime I introduce a new variable or change the values for some that I have.

The calculations to convert the Chinese characters into the ChrW(#'s) involves first splitting the Chinese name, looking up an AscW() value for each character, then combining them back into a text formula,
Cell X123's formula is ="ChrW("&B123&") & ChrW("&C123&")"
I've tried assigning CC directly,
CC = Sheets("List").Range("X123").value
However CC then = "ChrW(27773) & ChrW(27833)" (with quotes).

Any suggestions?

 

[pgc01]

Hi

I don't really understand.
If I have the filter value in a cell I can just refer to it.

For ex., if I have the table in A:B and I write the value that you posted in E1, this code works for me:

Sub Test()
Dim r As Range

Set r = Range("A1").CurrentRegion

r.AutoFilter field:=1, Criteria1:=Range("E1").Value
End Sub

I tried with this data

Book1
	A	B	C	D	E	F
1	F1	F2		Filter by	汽油
2	汽油	s
3	水	y
4	水	n
5	汽油	y
6	水	n
7	水	n
8
Sheet2

and got the result I expected:

Book1
	A	B	C	D	E	F
1	F1	F2		Filter by	汽油
2	汽油	s
5	汽油	y
8
Sheet2

 

[yky]

I wonder why you use the formula ="ChrW("&B123&") & ChrW("&C123&")". I mean why you enclose the formula with double quotation marks? What if you remove them, using just =ChrW("&B123&") & ChrW("&C123&") ?

 

[donfaison]

Hi

I don't really understand.
If I have the filter value in a cell I can just refer to it.

For ex., if I have the table in A:B and I write the value that you posted in E1, this code works for me:

Sub Test()
Dim r As Range

Set r = Range("A1").CurrentRegion

r.AutoFilter field:=1, Criteria1:=Range("E1").Value
End Sub

I tried with this data

<deleted> I need to assign the chinese word to a variable, and assigning the cell's value does not work

 

[donfaison]

I wonder why you use the formula ="ChrW("&B123&") & ChrW("&C123&")". I mean why you enclose the formula with double quotation marks? What if you remove them, using just =ChrW("&B123&") & ChrW("&C123&") ?

did you actually manage this? the above "ChrW("&B123&") & ChrW("&C123&")" results in

ChrW(27773) & Chrw(27833)

which, when assigned to a variable in VBA= 汽油 . Trying it without the quotes results in #NAME? for me.

 

[yky]

This works to find 汽油. Actually assigning CC directly to the cell containing the Chinese text (CC = Sheets("List").Range("A1").value or CC = Sheets("List").Range("A1").text both return CC = "??")

When you said both return "??", where did you see the question marks? In the watch window/immediate window? If so, you need to set your language in Windows. Check out this thread:

Testing for a unicode character in VBA

I got some great help recently in learning how to return extended Unicode characters such as the up and down arrows from a UDF. Now I need to learn how to test for those same codes when read from the worksheet by a different UDF. Here's my code. The 4 statements after the comments are trying to...

www.mrexcel.com

 

[yky]

Is something like this what you are after?

 

[donfaison]

Is something like this what you are after?

yes, BUT I am downloading a *.csv file and putting it into Excel. Although what you recommend works, 1) sometimes the names change and I would not find the string. That's why I would like to use a changeable list. 2) if I copy the Chinese from the excel cell, and paste in VBA, the pasted result is "??". [str = Range("A5").value or .text, msgbox str ==> "??"] Those are the reasons why I am deconstructing the Chinese characters and then reconstructing them. I only have a few Chinese-related excel files and I don't read Chinese, so I am keeping my language as English.

 

[yky]

yes, BUT I am downloading a *.csv file and putting it into Excel. Although what you recommend works, 1) sometimes the names change and I would not find the string. That's why I would like to use a changeable list. 2) if I copy the Chinese from the excel cell, and paste in VBA, the pasted result is "??". [str = Range("A5").value or .text, msgbox str ==> "??"]

If you add Chinese to Windows, this will not happen. Chinese characters will show up correctly.

Those are the reasons why I am deconstructing the Chinese characters and then reconstructing them. I only have a few Chinese-related excel files and I don't read Chinese, so I am keeping my language as English.

The method I described in the link ADDs a language. It doesn't replace the default language. You can have two or more languages. In other words, you can have English as your default language and Chinese as a secondary language. Both can be displayed properly in VBA. There is no downside I can see.