Macro code needed to shift cells right

Hi Guys , hope all is well

My problem: I currently have transaction amounts in the currency column, in reality these values supposed to be in the transaction column which one column right.

Hence, I need a macro code which will do the following below

1. Lookup cell range H2:H19936
2. Select all non-string values which are not a currency but a value eg: £125
3. Then shift all these non-string values one cell right to the correct column where the amounts belong

Large currency report-jan2013.xlsx

Please see the attachment I've added for more clarity. Look at column H

Thanks

Does this help?

Please Login or Register  to view this content.

Thanks for your generous contribution Marvin, John & Alan.

My idea is to delete all the blank rows after all the blank cells are entered with a unique number. I will start a new post for this

I will relay my thoughts of whether it worked very shortly

Thanks
Mohammed

Hi Marvin

The first code which you outlined worked successfully, thanks very much for that.

However, I now need two more codes which will firstly allow me to

A) Shift all the currencies which are in column G into H
B) Fill in all blank cells within column G with a unique number

In other words, after all the currencies are shifted into Col H, there will be blank cells in col G. Hence, for all these blank cells I would like a code which will assign a unique value such as 1-100 for each cell that is empty in col G

After this I will use excels F5 button to delete all blank rows

If I need to dedicate a new post for this, please inform me

Thanks
Mo

Hi Alan

Thanks for your code, it worked perfectly.

I now need to shift all currency codes from col G into H

Then assign a unique number to all blank cells in Col G

Any possible codes for this mate?

:-)

Thanks
Mo

Hi John

Will this shift all the currencies from Col G into H?

Thanks
Mo

Yes it does. It cuts the Range("G:H") and shifts it right one cell. It seemed like it was what you needed.

Hi John

The macro works successfully, however the only problem is that when it moves the currencies into col H, the corresponding transaction amounts disappear from col I.

Why is this?

Mo

Based on the sample provided. The macro goes down Column G vrs H and finds the non numerical data. It thens selects those cells and the cell adjacent and cuts it and pastes it to column to the right. It appears to do what you want when I tested it. The transaction amounts which were in Column H would be shifted at the same time. If there are currency indicators in Column G that don't have a misaligned transaction amount, then I could see where that may cause problems. In that case the macro would have to be amended. see below

Please Login or Register  to view this content.

HI John

The amended macro now works fine

With regards to your comment "if there are currency indicators in Column G that don't have a misaligned transaction amount, then I could see where that may cause problems. In that case the macro would have to be amended"

Could you elaborate on this?

In your sample in Post 1. There no situations where there was a misaligned currency designator in Column G where there was no misaligned transaction amount in Column H. I therefore tampered with your sample to create such occurances, and amended the macro to handle those situations. I'm glad to hear that you have a working solution, and that I could help out. Also thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Thanks John, I will dedicate a new post for the same worksheet but a diff problem. Should be amateur for you guys

Mohammed