Worksheet_Change(ByVal Target As Range) update horizontally.

**Bhaveen52** · 02-10-2010, 01:54 AM

hi!

I'm using Windows XP and Office XP.

My query is :
I have the following code. which is updating data in "A1" to Column "B" for next 30 rows i.e."b1","b2","b3" and so on till ("b30")next 30 rows.

For my new project in which I have 500 cells i.e. "A1" to "A500"(which is vertically updating into different sheets, excel add-in name is traderxl). And I want to update them horizontally. i.e. the data will update to "f1","g1","h1" and so on till("am1") next 30 columns(in a single sheet).
I tried a lot with my knowledge but couldn't make it. So, your help as usual is needed.

Thanks in advance!

Please Login or Register  to view this content.

**DonkeyOte** · 02-10-2010, 03:20 AM

Welcome to the Board.

First off - I see you tried to use CODE tags, you added the opening tag correctly but forgot to add the Closing CODE tag [*/CODE*] (without *) ... I've modified for you on this occasion.

So if I understand you're saying you want updates to A1 to be placed in F1 onwards, correct ?

If we assume B:E may / may not be blank then perhaps the below will work for you (untested)

Please Login or Register  to view this content.

I would make the point though that if A1:A500 are updating via a formula / DDE the Change event won't fire as and when the values are recalculated.

**Bhaveen52** · 02-10-2010, 03:49 AM

Thank you very much, Sir.

It's the exactly thing I was trying to do.

Thanks once again!

**Bhaveen52** · 02-10-2010, 09:13 AM

Dear Sir,

Thank you for your precious help.

The code is working fantastically. There is only one problem. In column "E" I have set moving average 30 formula i.e. =sum(f1:am1)/30. So, when the updating data reaches to column "AN", column "F" should deleted. Then only I'll have correct average of last 30 minutes data.

Can you please do it for me?

Thanks and regards

**DonkeyOte** · 02-10-2010, 11:33 AM

You reference 30 values commencing from Col F - on which basis the below assumes last value should be placed in Col AI (or before where fewer than 30 records exist)
(not sure where AN comes into it ?)

Please Login or Register  to view this content.

**Bhaveen52** · 02-10-2010, 01:11 PM

Sorry Sir,

I did mistake. you are right. "AN" comes nowhere in this code.

But, you did it perfactly.

Thanks a ton.

Thank you Sir, Thank you so much.