Editing a Range

[cdmcnamara]

Hi,

So I am attempting to run a macro that contains some if then statements that will change the text in a cell given certain circumstances. I am having an issue specifying the range in that it begins in cell A2 and it goes to the last data cell in the sheet and no farther. The issue with just selecting a whole column is that if the a cell is blank, it fills it in with no and it leaves me with 65,xxx nos in column A. Attached is the workbook with the sheet the code would be executed on and hopefully what the solution would look like. Also i have attached the troublesome macro I have written.

Please Login or Register  to view this content.

Thanks in advance.

[Mallycat]

There are a couple of issues with your code.

Firstly your set range staement is referring to the row number of the last cell in the range, not the actual range. Note I have changed it so that it now refers to a range that starts at A2 and ends in the last cell in the range (edit: you have to delete all the data in columns B:D in your sample sheet.

Secondly your For Each loop should not include .cells at the end

Please Login or Register  to view this content.

[shg]

Here's another way:

Please Login or Register  to view this content.

[cdmcnamara]

MallyCat,
Thanks for your help but when I run that on a sheet with more than one column it changes all the values in the sheet, I was hoping to have it only edit one column, which I specify.

Thanks1

[shg]

Or, without a loop,

Please Login or Register  to view this content.

[cdmcnamara]

Thanks to both of you guys, shg's code seemed to work for a specified column.

[cdmcnamara]

Shg,

The .SpecialCells(xlCellTypeConstants, xlTextValues) macro runs great the first time you run it, but if you run it again it takes out all the Nos and and replaces them with Yes, is there anyone you can make it so if it says no in it dont replace it with yes?

Thanks!

[shg]

You'd have to go back to the loop:

Please Login or Register  to view this content.