IF function for column 1 -- Then cells in column 2 = column 3

[Tleong]

Hi All,

I was hoping someone may be able to assist me with a relatively simple macro. I have a sheet of data, but only need to focus on 3 columns (S, T, and U) and begins on row 7, however there are blank rows. I need a macro that can go through the sheet performing the following task:

If Cell "U,7" <> 0 Then Cell "T,7".value = "S,7"
Else, Cell "T,7".value should remain the same
Next Row

Ideally, the macro should be "skipping" if a blank cell is found in column U and going on to the next one.

Lastly, the 'new' values in column T will need to have an equal sign or apostrophe preceding the value. This is necessary to retain the value in future steps. I hope I clarified it well and thanks all for looking!!

[Richard Buttrey]

Hi,

Are you sure you need a macro for this. And are you sure this is absolutely necessary. It seems to me that you're wanting exactly the same formula in T7 as you have in S7. Although there seems a contradiction since your narrative mentions having an = sign before the value (implying you want a formula), whereas you add 'OR an apostrophe', which would imply a constant label value/string.

Won't

=IF(U7<>0,S7,"")

copied down be simpler?

or if you do want a formula
=IF(U7<>0,"="&S7,"")

[Tleong]

Hi Richard,

Thanks for your prompt reply! The reason I believe a macro is necessary is because I need to repeat this process through 3 worksheets (the column and row references remain the same). Additionally, since I need to retain the value of cells in column T IF "U,7" does not equal 0; an IF function creates a circular reference. Apologies if I didn't explain it properly. The macro would be either retaining the value of cells in column T OR replacing it with the value in column S.

Regarding the equal or apostrophe sign; I need either/or in front of the values in the cells in column T because the next program used after this macro updates the whole spreadsheet, but retains the value of cells where it begins with an equal or apostrophe sign. I want to retain the values produced from this macro.

[Richard Buttrey]

Hi,

I think it's time to upload your workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[Tleong]

Hi Richard,

Please see below link for sample spreadsheet - it contains a before and after scenario; with the columns I referred to in this thread in blue. Please note the 'changed' values in column AM have the equal sign. Please let me know how I can further clarify. Thanks again for your continued assistance!

https://docs.google.com/file/d/0B6EU...Z1VVVWdW8/edit

[Richard Buttrey]

Hi,

Would you upload the workbook here please.

Two reasons.
1. This is a public forum for the benefit of all and not everyone will have a google docs account
2. The google docs version of Excel is an extremely 'lite' version and no good for any serious Excel stuff.

[Tleong]

Hi Richard,

Apologies - Please see attached for the sample spreadsheet. Thank you!!

COMPARE SAMPLE.xls

[Richard Buttrey]

Hi,

Does the following macro help

Please Login or Register  to view this content.

[Tleong]

Hi Richard - Thank you so much for your assistance! This works perfectly and I am now just trying to add the apostrophe in front of the values. So far I've gotten this:

Please Login or Register  to view this content.

However - this creates an apostrophe in front of zero i.e.: '0 when the cell is blank when I would like it to skip over it instead. Additionally, the numbers are currently rounded to 3 decimal places, when I would like it to be 2. Any thoughts?

Moderators note: code tags added for you - this time

[Tleong]

I think I may have figured out the '0 and blank cell issue. Please let me know your thoughts:

Please Login or Register  to view this content.

Moderators note: code tags added for you - this time

Dear Moderator - Apologies for the oversight. Thank you for adding the code tags for me!!

[FDibbins]

Tleong...
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)