Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel Number Repositioning

Posted on 2011-09-11
Medium Priority
267 Views
Hello Experts,

I'm not even sure how to word this request.

Some of the experts that have assisted me in the past are aware that I'm embarking on a new career trading stocks and shares.

In this request whenever a change has occured in any of the cells in row 3 that change will placed in the row 4 below. When another change occurs in any of the cells in row 3 then that will replace the current values in row 4 and the those values in row 4 will now be placed in row 5 and so on.....

This is probably the most challenging request I've ever asked.

So lets say in the current value in cell b3 is 50 it then changes to 150, the old value of 50 will now be pushed into cell b4. Now, cell b3 has now changed from 150 to 275, the value of 150 will now be pushed into b4 and the old value in b4(50) will be pushed into b5. So each time the value changes in B3 everything will be pushed down one and so on ...

I really hope you guys/grls can help me out.

Cheers
0
Question by:cpatte7372
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 26
• 12

LVL 81

Expert Comment

ID: 36519724
You didn't say whether you wanted the entire line to be moved down or just cells in column B. Both options are shown in this Worksheet_Change sub (goes in code pane for worksheet).

You will also need to clarify how you want the formatting handled. The line highlighting doesn't look too good  after a few cells have been inserted.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Dim oldVal As Double, newVal As Double
Set targ = Range("B3")  'Watch these cells
Set cel = Intersect(Target, targ)
If cel Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = cel.Value
Application.Undo
oldVal = cel.Value
If oldVal <> newVal Then
'Shift just cell B3 down
cel.Insert Shift:=xlShiftDown
cel.Offset(-1, 0).Value = newVal

'Shift entire row down
'cel.EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=cel.EntireRow
'cel.Offset(-1, 0).EntireRow.Value = cel.EntireRow.Value
'cel.Offset(-1, 0).Value = newVal
End If
Application.EnableEvents = True
End Sub
0

Author Comment

ID: 36519734
Hi byundt

Thanks for responding. I very much would like the entire line. I was just using the single cell example as an illustration.

Going to check out worksheet now...

0

LVL 81

Expert Comment

ID: 36519742
cpatte7372,
I had the code temporarily turned off by putting an "x" in front of the sub name. If it doesn't work, please remove the "x" and try again.

I have subsequently repaired the file in the Comment.

0

LVL 81

Expert Comment

ID: 36519751
cpatte7372,
If you want to push the entire line down, copy formulas and formats for the newly inserted line and retain the latest price, then try this revision to my previous code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Dim oldVal As Double, newVal As Double
Set targ = Range("B3")  'Watch these cells
Set cel = Intersect(Target, targ)
If cel Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = cel.Value
Application.Undo
oldVal = cel.Value
If oldVal <> newVal Then
'Shift entire row down
cel.EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=cel.EntireRow
cel.EntireRow.Copy cel.Offset(-1, 0).EntireRow
cel.Offset(-1, 0).Value = newVal
End If
Application.EnableEvents = True
End Sub

0

Author Comment

ID: 36519753
Hi byundt,

Whenever I make these request, I always forget to mention the values are dynamic. I have uploaded your attachment with a slight modification.

So lets say the values in all cells in row 3 are getting their numbers from the changes in the values in row 25. Now, if I change in value occurs in any of the cells in row 25 all values currently in row 3 will move down to row 4 and so on....

0

Author Comment

ID: 36519817
Dude,

This is looking really good. I'm excited.... Is it possible to have the range carried over to all cells in row 3?

This is amazing........ whooooaa
0

Author Comment

ID: 36519868
Ouch!

byundt, I think it may be problem as I do need it to be dynamic. I was playing around with the spreadsheet and I realised that if its dynamic it may be a problem to get it to work.

Is there a way around this?
0

LVL 81

Expert Comment

ID: 36519917
I changed the code to watch cell B25 and move rows 3-12 down with each change.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Dim oldVal As Double, newVal As Double
Set cel = Range("B3")
Set targ = Range("B25")  'Watch these cells
Set targ = Intersect(Target, targ)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = targ.Value
Application.Undo
oldVal = targ.Value
If oldVal <> newVal Then
'Shift entire row down
cel.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=cel.EntireRow
cel.EntireRow.Copy cel.Offset(1, 0).EntireRow
cel.EntireRow.Offset(1, 0).Formula = cel.EntireRow.Value
cel.Value = newVal
targ.Value = newVal
Rows(24).Delete
End If
Application.EnableEvents = True
End Sub

I also used Conditional formatting to get the color bars highlighting every fourth row using a Formula is criteria of:
=MOD(ROW(),4)=2

0

Author Comment

ID: 36519922
Hi byundt,

You still around, mate?
0

LVL 81

Expert Comment

ID: 36519992
It is worth noting that the Worksheet_Change sub responds to changes initiated by the user. If your values update due to external data sources, then you may need to use a different type of event sub, such as the Worksheet_Calculate sub I had suggested to you in an earlier thread.

Note: this approach requires that your worksheet have at least one cell with a volatile formula, such as =NOW(). I chose cell Q1 for this purpose.

Private Sub Worksheet_Calculate()
Dim cel As Range, targ As Range
Static oldVal As Double
Dim newVal As Double
Set cel = Range("B3")
Set targ = Range("B25")

Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = targ.Value
If oldVal <> newVal Then
'Shift entire row down
cel.EntireRow.Insert Shift:=xlShiftDown
cel.EntireRow.Copy cel.Offset(-1, 0).EntireRow
cel.EntireRow.Offset(-1, 0).Formula = targ.EntireRow.Value
oldVal = newVal
Rows(24).Delete
End If
Application.EnableEvents = True

End Sub
0

Author Comment

ID: 36520016
byundt,

This is looking brilliant.

Couple of questions:

I understand what you mean when you say you've changed the code to watch cell B25, but not sure what you mean by 'and move rows 3-12 down with each change'?

Is it possible to change the code to watch for all cells B25 through N25?

Cheers

This is looking genius mate.
0

Author Comment

ID: 36520023
Which I guess might mean also watching cells B3 through to N3, but not sure.

Cheers
0

Author Comment

ID: 36520070
Hi byundt,

Would I need to change the following?

Set cel = Range("B3")
Set targ = Range("B25")
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 36520074
I modified the code to watch cells B25:N25 for changes. If so, a new row is inserted at row 3 and the remaining rows pushed down. If a row is pushed past row 23, it is deleted.

Private Sub Worksheet_Calculate()
Dim cel As Range, targ As Range
Static oldVal(14) As Double
Dim i As Long, j As Long
Dim newVal As Double
Set cel = Range("B3")
Set targ = Range("B25")

Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = targ.Value
For j = 2 To 14     'Watch columns B through N on row 25 for changes
If oldVal(j) <> targ.Cells(1, j - 1) Then
'Shift entire row down
cel.EntireRow.Insert Shift:=xlShiftDown
cel.EntireRow.Copy cel.Offset(-1, 0).EntireRow
cel.EntireRow.Offset(-1, 0).Formula = targ.EntireRow.Value
For i = 2 To 14
oldVal(i) = targ.Cells(1, i - 1).Value
Next
Rows(24).Delete
Exit For
End If
Next
Application.EnableEvents = True

End Sub
0

Author Comment

ID: 36520146
Mate, this is absolute magic. If this works in realtime tomorrow when the stock market opens at a minimum you will have saved me over \$200/month in subscription fees alone. If I can successfully use it to trade then I a am on my way to a successful career.

There is something that I need to tweak on it. I'm just going to try it myself, but I'm sure I'll screw it up, so hopefully you'll still be online for the next 20 minutes.

0

Author Comment

ID: 36520173
byundt,

As I thought, my modification failed. I moved the cell values from row 25 to row 3.

I changed For j = 2 To 14 For j = 17 To 29

And changed Rows(24).Delete to  Rows(1000).Delete, but it keeps on failing with subscript out of range, and it won't be the rows down....

0

Author Comment

ID: 36520195
So byundt,

Basically I want everything to remain the same apart from where row 3 gets it numbers from - now located in cells Q3 through to AC3.

I thought I could sort it but .....
0

Author Comment

ID: 36520251
Hi byundt,

Are you still around mate.... I'm trying to make the modifications myself but failing miserably....
0

LVL 81

Expert Comment

ID: 36520274
cpatte7372,
Try the code like this:

Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long
Set targ = Range("Q3:AC3")  'Watch these cells for changes
nCols = targ.Columns.Count
Set rg = Range("B3:N3")     'Put changed values here, pushing old values down
Set rg2 = rg.Offset(0, -1).Resize(1, nCols + 2)

Application.EnableEvents = False
Application.ScreenUpdating = False
For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
If oldVal(j) <> targ.Cells(1, j) Then
'Shift data down
rg2.Insert Shift:=xlShiftDown
rg2.Copy rg2.Offset(-1, 0)
rg.Offset(-1, 0).Formula = targ.Value
For i = 1 To nCols      'Capture values of data for next run
oldVal(i) = targ.Cells(1, i).Value
Next
Exit For
End If
Next
Application.EnableEvents = True

End Sub

And please don't forget the volatile formula (see cell Q1). If you don't have one, the code won't trigger.

0

Author Comment

ID: 36520289
Hi byundt,

Thanks for sticking around mate. Unfortunately, it doesn't work as you got it work before the change. I would very much like everything to work as before but instead of getting the values from B25:N25 its now getting its values from Q3:AC3.

The rows don't go down as before, neither will it go down if any value changes..
0

LVL 81

Expert Comment

ID: 36520303
My guess is that events were turned off and you encountered a fatal error. If so, events never got restored and the macro won't trigger.

You have three choices:
1) Restart Excel and reopen the file
2) Run the following statement in the Immediate pane
Application.EnableEvents = True
3) Run the following macro:
Sub Restore()
Application.EnableEvents = True
End Sub

You
0

Author Comment

ID: 36520315

I just needed to restart Excel and it worked fine.

Listen mate, I really can't thank you enough.

I'm sooooooo looking forward to trying this out tomorrow. I will close it once I've put it through its paces when the market opens.

Thanks again dude.

Cheers
0

Author Comment

ID: 36520327

Just one last thing mate. I wanted to change then length of values from Q3:AC3 to Q3:V3, but it won't work if I make that change.

I'm really sorry to be a nuisance

Hopefully, you haven't turned you PC off.

Cheers
0

Author Comment

ID: 36520357

For some reason, the formula only works if I click run macro or F5
0

Author Comment

ID: 36520368

I'm sure you must be tired, may be we could pick it up tomorrow.
0

LVL 81

Expert Comment

ID: 36520371
I changed the code to watch just cells in Q3:V3. It wasn't clear, however, whether you want to copy the data from Q3:V3 or from Q3:AC3 over to B3:N3. I assumed the latter, but show a commented out statement for the former in this macro:
Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long
Set targ = Range("Q3:V3")  'Watch these cells for changes
nCols = targ.Columns.Count
Set rg = Range("B3:N3")     'Put changed values here, pushing old values down
Set rg2 = rg.Offset(0, -1).Resize(1, rg.Columns.Count + 2)

Application.EnableEvents = False
Application.ScreenUpdating = False
For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
If oldVal(j) <> targ.Cells(1, j) Then
'Shift data down
rg2.Insert Shift:=xlShiftDown
rg2.Copy rg2.Offset(-1, 0)
'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value              'Copy over just data in Q3:V3
rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value   'Copy over data in Q3:AC3
For i = 1 To nCols      'Capture values of data for next run
oldVal(i) = targ.Cells(1, i).Value
Next
Exit For
End If
Next
Application.EnableEvents = True

End Sub
0

Author Comment

ID: 36520400

Thats great. Going to test it now...
0

Author Comment

ID: 36520429
Mate,

That is spot on..... yeaaaawwww.

Cheers mate.
0

LVL 81

Expert Comment

ID: 36520442
Please post back in this thread tomorrow after you test on live data. I am curious to know if it is triggered successfully.

And don't forget to put a cell somewhere in the real worksheet with the formula:
=NOW()

0

Author Comment

ID: 36520492

I certainly will.

If it does work it will be e biggest breakthrough for a trader. I have already mentioned it to a couple of colleagues who are now making offers to me to work with them on projects. I really hope it works. So excited.
0

Author Comment

ID: 36522921

Not sure if you're at your PC, but just wanted to say the spreadsheet is working like a dream.

I modified the spreadsheet to only push down rows B3:G3, but it still pushes down H3.

Also I removed the original conditonal formatting and added conditonal formatting in Colums D and E. Basically, if one is greater than the other then highlight but if you take a look at the cell values its highlighting even when one cell is less than the other.. Is there a reason for that? Or will it require a formula?

Cheers
EE-The-Tapev10.xlsm
0

Author Comment

ID: 36523014
Oh, I think I know why..... the formula is pushing the colours down as well, rather than just keeping the colours as a result of the conditional formatting. Is there a way to overcome that?

Cheers
0

Author Comment

ID: 36524132

I figured out the conditional formatting still unsure why HÂ£ still gets pushed down....
0

Author Comment

ID: 36524670

There appears to be delay of a second between each value being updated.... Any thoughts?
0

Author Comment

ID: 36525184

I hope you've been having a pleasant day.

I believe the problem was with conditional format being pushed down. I tried doing a conditional format on the first row but the formula pushes the format to the second row and beyond. Is there anyway to prevent the formating being pushed down.

Cheers
0

LVL 81

Expert Comment

ID: 36525802
Will revert to this question a couple of hours from now.
0

LVL 81

Expert Comment

ID: 36526413
It looked to me like your conditional formatting was messed up. I rewrote it so the higher value in a row in coumns D and E would be green and the lower would be red. If a tie, then no conditional formatting.

I also rewrote the macro so it would fit the revised range.
Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long
Set targ = Range("Q3:V3")  'Watch these cells for changes
nCols = targ.Columns.Count
Set rg = Range("B3:G3")     'Put changed values here, pushing old values down
Set rg2 = rg    'Push formatting and formulas down in range rg2

Application.EnableEvents = False
Application.ScreenUpdating = False
For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
If oldVal(j) <> targ.Cells(1, j) Then
'Shift data down
rg2.Insert Shift:=xlShiftDown
rg2.Copy
rg2.Offset(-1, 0).PasteSpecial xlPasteValues
rg2.Copy
rg2.Offset(-1, 0).PasteSpecial xlPasteFormats
'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value              'Copy data from same number of columns as targ
rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value   'Copy data into same number of columns as rg
For i = 1 To nCols      'Capture values of data for next run
oldVal(i) = targ.Cells(1, i).Value
Next
Exit For
End If
Next
Application.EnableEvents = True

End Sub
EE-The-Tapev10Q27302334.xlsm
0

Author Closing Comment

ID: 36572950
Brad, you started off something really amazing here mate.

Cheers
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month4 days, 12 hours left to enroll