Splitting Cell Values

[twsmale]

I'm using VBA to query and modify an Excel database. The database contains information about servers and their locations. Most of the rack mount servers have the location column as something like "G-05". Those are easy. When it comes to the blade servers, the locations are listed as "G-05E2S09" or something like that.

What I'm trying to do is build an "add server" function. I need the search to query the rack and enclosure (which I can do) then I need to split the cell and insert a new row right after the next lowest slot number.

For example, the location column may look like this:

G-05E2S02
G-05E2S03
G-05E2S04
G-05E2S06
G-05E3S01...etc.

I need to be able to add a new row for say, G-05E2S05.

If this makes sense, I would greatly appreciate some input. I am a VBA beginner, but have pretty solid knowledge of programming techniques.

Thanks!
Tom

[AndreRet]

You need to find the next available empty row in the column and then paste the data into that column. The following example will give you an idea on how to achieve this -

Public Sub CopyRows()
    Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Range("A65536").End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column H
        ThisValue = Range("H" & x).Value
        If ThisValue = "ir" Then
            Range("A" & x & ":AG" & x).Copy
            Sheets("a").Select
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        ElseIf ThisValue = "RR" Then
            Range("A" & x & ":AG" & x).Copy
            Sheets("b").Select
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        End If
    Next x
End Sub