Problem with macro for replacing cell data with content 1 cell above active cell.

[holmen]

Hi!

I'm having some trouble converting raw-data to a format I can put to use in a pivotable.
At my work we pull data a couple of times a day from a external program, the formatting of that data makes for allot of manual labour before we can present it.
I'm trying to write a VBA scripts that cycles through column A of the data and replace all instaces of "agent total" with the name of the agent.
The name of the agent is always one cell above the active cell.

The only way I have worked with macros before is with the "record macro" function so i'm basically new to this.

I'm using Excel2013
and Windows Xp

I have tried to modify a search an replace recorded macro by editing the replacement value with all kinds of things but nothing remotely works.
I tried yo use a ActiveCell.Offset(-1, 0) to get the content of the cell above

This is how far I come:

Columns("A:A").Select
Selection.Replace What:="agent total", Replacement:= ActiveCell.Offset(-1, 0), LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The actual data look something like this:

Agent	AgentID	Queue	Calls Inbound	Abandons	AHT IB
Andersson Christoffer	114110	OSD_Q_Viasat_Teknik_IPTV	46	0	00:12:41
Andersson Christoffer	114110	OSD_TA_Viasat_Teknik_IPTV	9	0	00:23:17
agent total	159	1	00:13:15
Andersson Henrik	130080	OSD_TA_Viasat_Sales_Teknik	3	0	00:08:40
Andersson Henrik	130080	OSD_Q_Viasat_Teknik_IPTV	0	0	00:00:00
agent total	68	1	00:11:39
Andersson Johan	120017	OSD_TA_Viasat_Teknik	15	0	00:07:54
Andersson Johan	120017	OSD_TA_Viasat_Sales_Teknik	3	0	00:24:38
agent total	104	0	00:10:15
Andersson Simon	130561	OSD_Q_Viasat_Teknik_IPTV	90	1	00:09:22
Andersson Simon	130561	OSD_TA_Viasat_Teknik_IPTV	26	0	00:14:10
agent total	299	1	00:09:15

<tbody>
</tbody>

I know I must be making some basic mistake but I have no idea where to start correcting.
If anyone of you know how to make this work It would be very appreciated.

//Niklas

 

[holmen]

Thanks a bunch.
I had to make some minor modification but I got it to work.
The file now works like a charm.

The final script ended up like this.

Sub Fixa_data()


    Sheets("rawdata").Select
    
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
        
        Columns("A:A").Select
        
        Dim i


        For i = 1 To 10000


        If Cells(i, 1) = "Agent total" And Cells(i + 0, 6) > "00:00:00" Then
        Cells(i, 1) = Cells(i - 1, 1) & "."
       
End If


Next i


Dim ws As Worksheet
    Dim lRow As Long


    Set ws = ThisWorkbook.Sheets("rawdata")


    With ws
        .AutoFilterMode = False


        lRow = .Range("A" & .Rows.Count).End(xlUp).Row


        With .Range("A1:A" & lRow)
             .AutoFilter Field:=1, Criteria1:="<>*.*", Criteria2:="<>total"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With


        .AutoFilterMode = False
        
    End With
    
    
    Columns("A:A").Select
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    
    Sheets("Pivotable").Select
    


    MsgBox "Done"

 

[Fishboy]

Thanks a bunch.
I had to make some minor modification but I got it to work.
The file now works like a charm.

The final script ended up like this.

Sub Fixa_data()


    Sheets("rawdata").Select
    
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
        
        Columns("A:A").Select
        
        Dim i


        For i = 1 To 10000


        If Cells(i, 1) = "Agent total" And Cells(i + 0, 6) > "00:00:00" Then
        Cells(i, 1) = Cells(i - 1, 1) & "."
       
End If


Next i


Dim ws As Worksheet
    Dim lRow As Long


    Set ws = ThisWorkbook.Sheets("rawdata")


    With ws
        .AutoFilterMode = False


        lRow = .Range("A" & .Rows.Count).End(xlUp).Row


        With .Range("A1:A" & lRow)
             .AutoFilter Field:=1, Criteria1:="<>*.*", Criteria2:="<>total"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With


        .AutoFilterMode = False
        
    End With
    
    
    Columns("A:A").Select
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    
    Sheets("Pivotable").Select
    


    MsgBox "Done"

Glad to help mate. I am teaching myself most of this as I go along so I am happy to to keep trying to solve new problems. I am pleased we eventually got there in the end