|
I have an automated Excel spreadsheet that pulls in data from an Oracle database. My table is sorted by Name, alphabetically. The users want one name to be taken out of alphabetical order and always appear on the top. To do this, there is code written to find this line and move it to the top. The code is below:
Cells(1, 1).Select
Do While ActiveCell.Row < intLastRow And bSegmentAverage = False
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
If ActiveCell.Value = "SEGMENT AVERAGE" Then
intSegmentRow = ActiveCell.Row
Range(ActiveCell.Address, Cells(ActiveCell.Row + 2, intLastColumn)).Select
Selection.Cut
bSegmentAverage = True
intSegmentRow = ActiveCell.Row
Range(Cells(8, 1), Cells(10,intLastColumn)).Rows.Insert
End If
Loop
Now, usually the first time this is run, it works perfectly. It takes the range of cells I want to move and inserts them at the spot I want, moving all the other rows down. What seems to happen, though, is the second time the program is run it has a tendency to try to overwrite the cells where I am trying to insert, rather than insert them and move the lower rows down.
Is there a better way to do this or can I specify somehow to never overwrite the existing contents and always shift the rows to below?
I am new to using VBA with Excel and this is my first post on EE, so please excuse me if I am unclear.
Thanks, Jody
|
|
Jody,
I havn't time to look at your code, but the following should work.
'---------------------------------------------
Sub test()
Dim Rg As Range
Dim RgFound As Range
Dim s As String
s = "SEGMENT AVERAGE"
Set Rg = Range("A1:A30") '<------ change here
Set RgFound = Rg.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
If Not RgFound Is Nothing Then ' if the string is found
If RgFound.Address <> Rg.Cells(1).Address Then 'if found cell is not first (else nothing to do)
Range(Rg.Cells(1), RgFound.Offset(-1, 0)).Copy Rg.Cells(2, 1)
Rg.Cells(1) = s
Application.CutCopyMode = False
End If
End If
End Sub
'---------------------------------------
Regards,
Sebastien
|