Inserting a row
 

Hi

Im not quite sure what Im doing wrong...

Based on a value in a list box, I want to insert a row in the prope
alphabetical order.

This is what I have:

Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

For i = 4 To Range("A65536").End(xlUp).Row
If Range("A" & i) Value ListNew.Value And
Range("A" & i - 1).Value < ListNew.Value Then
Rows(i).Insert
Exit For
End If
Next i


End Sub

Any ideas or help will be REALLLY appreciated!

Thanks!
Sheen

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

Why not just paste it at the end, then sort the column:

Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

Range("A" & Range("A65536").End(xlUp).Row+1).Value = ListNew.Value

Range("A4:A" & Range("A65536").End(xlUp).Row).Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo

Range("A4").Select

End Su

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

Hi
thanks for that tip but for some reason it gives me an error...

The debugging tool points to this line:

Range("A4:A" & Range("A65536").End(xlUp).Row).Select

Any ideas

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

I tested it on my machine Win2K, Excel 2002 with no problems. I cannot
think what the error might be... If you didn't cut/paste, there may be
a typo in your code? Possible a space somewhere in the Range
function?

I'm at a loss...

K


---
Message posted from http://www.ExcelForum.com/

Inserting a row
 

That might be the problem - Im using Excel 2000

THanks

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

It shouldn't matter if the line:

Range("A" & Range("A65536").End(xlUp).Row+1).Value = ListNew.Value

works. It is essentially doing the same thing...



--
Message posted from http://www.ExcelForum.com

Inserting a row
 

Ugh i dont know =

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

If the line is inserted correctly, just record a macro while doing
sort and paste the results (after looking them over) in place of m
sort solution.



--
Message posted from http://www.ExcelForum.com

Inserting a row
 

This doesn't work either...its not the sorting thats the problem...If
remark out the sorting code and just leave the insertion process n
error message is returned but the company anme is not added to th
first empty cell...

Does anyone have some ideas?

Thank

--
Message posted from http://www.ExcelForum.com

Inserting a row
 

Let's do some testing... Change your macro to read this:

Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

Msgbox Range("A65536").End(xlUp).Row+1
Msgbox ListNew.Value

End Sub

This will tell you where the new row was found and what value
is to be placed in it. Let me know what it returns. The problem ma
be that there is something in a lower row than your last row of data (
space?) and the new company name is pasted below it.



--
Message posted from http://www.ExcelForum.com

Inserting a row
 

I got it to work...

THe problem was that it wasn't reading where it should insert the ne
company.

This is what I have:

Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

Count = 0

For i = 1 To Sheet2.Range("A65536").End(xlUp).Row
Count = Count + 1
Next i

Sheet2.Range("A" & Count + 1).Value = ListNew.Value

Sheet2.Range("A4:A" & Sheet2.Range("A65536").End(xlUp).Row).Select
Selection.sort Key1:=Sheet2.Range("A4"), Order1:=xlAscending
Header:=xlNo

End Sub

And it properly inserts the new company and sorts it.

THanks for all your help

--
Message posted from http://www.ExcelForum.com