Testing a column, change contents

[lieb39]

Hello everyone,

I'm writing a big macro to automate manual workings of a excel sheet that would normally take a good day.

I'm having trouble figuring this one out - a column named 'account numbers' (Q) should have account numbers in them, which is a ten digit number, beginning with 7 (7 xxx xxx xxx).

Sometimes it has other information in it, which doesn't match to this rule. In that case, the contents of the field needs to be replaced with 71 000 000 01. In the next row where the contents doesn't match up, it needs to be replaced with 71 000 000 02, and so on.

This needs to occur until the entire populated column (Q) has been checked and replaced where required - so it stops when it detects and empty field.


Can anyone point me to the right direction to do this?

Thanks,
Daniel

[StephenR]

Can you post a small sample workbook?

[lieb39]

It's attached.

I had to take out the data but the columns are copied.

The first two entries (rows) are OK - but the last two (in red) are the ones that need to be changed to:
710000001
710000002
(and so on)

The macro would just need to run through the C/S Account Number and do as I described.

Thanks so much,
Daniel

[StephenR]

Here is one approach:

Please Login or Register  to view this content.

[lieb39]

Thank you so much! I've been working on getting this to work for such a long time.

Thanks again

Cheers

[StephenR]

My pleasure.

[lieb39]

I'm trying to re-use the same code to help me populate a formula into a new column.
The formula itself is "=RC[-2]*RC[-1]+5.95"

The Code I'm using is:

For Each r In Range("W2", Range("W2").End(xlDown))
r = "=RC[-2]*RC[-1]+5.95"
Next r

It runs fine but it fills out the entire sheet down to the last row (and takes forever) unless I break the code. Is it possible to just populate to the last row that has data?

Thanks,
Daniel

[StephenR]

Perhaps you have nothing in W except W2, so this is a better formulation:

Please Login or Register  to view this content.

[lieb39]

Perhaps you have nothing in W except W2, so this is a better formulation:

Please Login or Register  to view this content.

Yep - Googled it and found this:

Range("W2:W" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=RC[-2]*RC[-1]+5.95"

Thanks