Restricting the search to one column

I am trying to get a macro that converts Japanese digits to their Roman counterparts but does it only in a specific column. My original version just selected the J column and then Cells.Replace ran just fine on just that column. I have discovered, however, that the users may make the crucial column either J or K, so I changed it to search for the header first, and then select that column. It replaces in the entire sheet, however.

Here's what I've got (I've only given you one digit replacement. The What doesn't display properly because this forum doesn't display Japanese):

Please Login or Register  to view this content.

I've also tried

Please Login or Register  to view this content.

When I step through I do see it find and select the correct column, but it still replaces in the whole sheet.

I am very confused because according to the documentation as I understand it, the Find Method is inherently limited to the range you call it on.

Anyone out there know how to fix this?

Thank you!

ctap

Even with your code, it's still replacing in all the columns. Sigh. At least I'm not too much of an idiot for not being able to figure this out. (Knock on wood!)

I'll have to come up with a sample to attach my code to; my test workbook contains information I can't release. It's not going to work on systems that don't run Japanese, though, is it? The character it's searching for is two-byte.

Thanks for the suggestions! Reducing the steps is always good.

More when I've got a usable sample to attach!

ctap

There are three worksheets. Sheet 1 and Sheet 2 are where the conversions should happen; Sheet 3 is a sheet without the "Bank/Post" column, just to make sure that error stays caught.

If I'm right about how this should look on non-Japanese systems, you should only be able to read the data in the Price column. Everything else should be unreadable. Once the macro runs (the problem is ConvertNum, but you can run ConvertWorkbook--it just does it on all the sheets in the workbook), you should only be able to read the numbers in Price and Bank/Post Account, but not Item number. Those numbers (whether they are in column A or column B) should remain untouched. In other words, only the Bank/Post Account numbers should be converted, and they should be converted regardless whether Bank/Post Account is column C or column D. (Or J and K in actuality. We've two kinds of payment sheets.)

Of course the thing may just plain not work on non-Japanese systems. I don't know. I don't have access to a machine that isn't Japanese savvy, so I can't try it out.

The point, for those who are curious, is that it's easier not to switch keyboards in the middle of typing, so much of the time folks don't. The bank's online system, however, will only handle one-byte numbers, so you can only copy and paste if they're one byte. If they're two, you have to convert them or retype, the first taking too much time and the second prone to human error.

Thanks so much for the help so far and thank you for any additional wisdom offered!

ctap

In desperation, I have redrafted this macro so that the user selects the cells they want to run it on and then runs it. There is a problem, however, if the user has previously done a search on the whole workbook and therefore the Find dialog is already set to "Within: Workbook": the macro then runs on the whole workbook, all cells.

Is there VBA code to set the Replace so that it is "Within: Worksheet"?

Thanks!

ctap

Thanks, Simon! When I tried running it that way I got:

Run-time error '1004':
Unable to get the Find property of the Range class

Does that make more sense to you than it does to me? ^_^;;;

ctap

This is the version with the code you suggested. The earlier version is up a little higher in the thread.

It seems insane that there is no way to set the Replace to do only within the Workbook. Then I could just set it up so that the user selects the correct column and the macro runs on the selection. I tried it, though, with the Find dialog already set to Within: Workbook (as it so often is), and then it replaces all the two-byte numbers.

Thank you!

ctap

The range I want it to work on is the entire column that it finds the "Bank/Post" cell in. I thought that was what it was already selecting. I don't understand what your code is doing if not that.

I am at an utter loss now. I don't know how to change it to make it work. The range is the whole problem.

I wish the boss would just pay for someone who knows what they're doing instead of leaving these things to me. . . .

ctap

Originally Posted by Simon Lloyd

Here, this works (well the code runs but i have no idea what it is supposed to do)

Simon, I very much appreciate all the effort you went to. It's more generous than I had any right to hope for. Unfortunately it's still converting the numbers outside the column I need it restricted to.

I have no idea why that's so. It seems to depend on whether the Find/Replace dialog is already set to Within:Workbook or not. That's why I was hoping there was some way to force it to set that to Within:Worksheet.

I hate the thought of leaving remembering to change that to our users. You would think that VBA would address that particular aspect of a Replace, but I can't find it anywhere.

Thanks so much for all the effort! Especially considering you were working without really knowing what the point was! ^_^

ctap

That works great for a single sheet with cells selected, which is great for what we want! I can tell the users to select whichever column is "Bank/Post" on that sheet and then run the macro and it will work.

It doesn't answer how it would work on a whole workbook, but I am quite probably the only one in the organization who wants to do that. (They'll be using new sheets as they come in; I'm the one who handles the records of times gone by and might want to run this on previous collections.)

I've sent you a small bit of sample data, so you can try the variations you have in mind. This will work for us, though, if you want to get on other projects.

Thanks so much, Simon! You are very generous with your time and skill.

ctap