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:

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Agent[/TD]
[TD="class: xl66, width: 64"]AgentID[/TD]
[TD="class: xl66, width: 64"]Queue[/TD]
[TD="class: xl66, width: 64"]Calls Inbound[/TD]
[TD="class: xl66, width: 64"]Abandons[/TD]
[TD="class: xl66, width: 64"]AHT IB[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Christoffer[/TD]
[TD="class: xl68, width: 64, align: right"]114110[/TD]
[TD="class: xl68, width: 64"]OSD_Q_Viasat_Teknik_IPTV[/TD]
[TD="class: xl69, width: 64"]46[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:12:41[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Christoffer[/TD]
[TD="class: xl68, width: 64, align: right"]114110[/TD]
[TD="class: xl68, width: 64"]OSD_TA_Viasat_Teknik_IPTV[/TD]
[TD="class: xl69, width: 64"]9[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:23:17[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 192, colspan: 3"]agent total[/TD]
[TD="class: xl71, width: 64"]159[/TD]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl72, width: 64"]00:13:15[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Henrik[/TD]
[TD="class: xl68, width: 64, align: right"]130080[/TD]
[TD="class: xl68, width: 64"]OSD_TA_Viasat_Sales_Teknik[/TD]
[TD="class: xl69, width: 64"]3[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:08:40[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Henrik[/TD]
[TD="class: xl68, width: 64, align: right"]130080[/TD]
[TD="class: xl68, width: 64"]OSD_Q_Viasat_Teknik_IPTV[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:00:00[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 192, colspan: 3"]agent total[/TD]
[TD="class: xl71, width: 64"]68[/TD]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl72, width: 64"]00:11:39[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Johan[/TD]
[TD="class: xl68, width: 64, align: right"]120017[/TD]
[TD="class: xl68, width: 64"]OSD_TA_Viasat_Teknik[/TD]
[TD="class: xl69, width: 64"]15[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:07:54[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Johan[/TD]
[TD="class: xl68, width: 64, align: right"]120017[/TD]
[TD="class: xl68, width: 64"]OSD_TA_Viasat_Sales_Teknik[/TD]
[TD="class: xl69, width: 64"]3[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:24:38[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 192, colspan: 3"]agent total[/TD]
[TD="class: xl71, width: 64"]104[/TD]
[TD="class: xl71, width: 64"]0[/TD]
[TD="class: xl72, width: 64"]00:10:15[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Simon[/TD]
[TD="class: xl68, width: 64, align: right"]130561[/TD]
[TD="class: xl68, width: 64"]OSD_Q_Viasat_Teknik_IPTV[/TD]
[TD="class: xl69, width: 64"]90[/TD]
[TD="class: xl69, width: 64"]1[/TD]
[TD="class: xl70, width: 64"]00:09:22[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Andersson Simon[/TD]
[TD="class: xl68, width: 64, align: right"]130561[/TD]
[TD="class: xl68, width: 64"]OSD_TA_Viasat_Teknik_IPTV[/TD]
[TD="class: xl69, width: 64"]26[/TD]
[TD="class: xl69, width: 64"]0[/TD]
[TD="class: xl70, width: 64"]00:14:10[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 192, colspan: 3"]agent total[/TD]
[TD="class: xl71, width: 64"]299[/TD]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl72, width: 64"]00:09:15[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Fishboy]

Hi Niklas, welcome to the boards.

This is untested as yet, but try this:

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

 

[holmen]

Hi Niklas, welcome to the boards.

This is untested as yet, but try this:

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

Hi and thank you.

I tried it but i still won't work.
in debug it says
"ActiveCell.Offset(-1, 0).Value= application-defined of object defined error"<application-defined or="" object-defined="" error="">

I'm thinking I can't modify the "Selection.Replace" command like this and I need to write a script that moves down one one cell at at time.
Then if it finds "agent total" it should set it as active cell, copy data from activecell with offset (-1, 0) and paste it in active cell.
then resume the walk down the column.

I know what I want to do but how to write the function is a mystery.
I have been trying to google similar functions but when I try to make sense of the code I'm missing the basic knowledge to make sense of it.

Any more ideas would be great
Until then I keep googling away.

//Niklas</application-defined>

 

[SpreedsheetCrusader]

will this work my brother ?

Sub TESTING()

Dim i


For i = 5 To 16


If Cells(i, 4) = "Total" Then
   Cells(i, 4) = Cells(i - 1, 4)
End If


Next i


End Sub

basically for the name range i am asking Excel to look for the word Total if he/she finds it he/she replace it with the name from the cell above it ... not a brilliant loop but it works.

however i wonder how can we add a feature to unmerge those nasty total cells when found.


may the force be with you

 

[SpreedsheetCrusader]

Oh la la la just add unmerge command:

Sub TESTING()

Dim i


For i = 5 To 16


If Cells(i, 4) = "Total" Then
   Cells(i, 4) = Cells(i - 1, 4)
[COLOR=#ff0000]   Cells(i, 4).UnMerge[/COLOR]
   
End If


Next i


End Sub

 

[SpreedsheetCrusader]

see if it helps i uploaded the file for ya , safe to download

http://www.filedropper.com/searchreplace


may the force be with you

 

[Fishboy]

Hi again Niklas,

Have done some testing this time and have something which I believe should help you.

Sub Test_Replace()
Set MyRng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each cell In MyRng
    If cell.Value = "Agent Total" Then
    cell.UnMerge
    cell.Value = cell.Offset(-1, 0).Value
        End If
    Next
End Sub

 

[Fishboy]

Updated to also copy the correct cell alignment from the cell above after the cell is unmerged:

Sub Test_Replace()
Set MyRng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each cell In MyRng
    If cell.Value = "Agent Total" Then
    cell.UnMerge
    cell.Value = cell.Offset(-1, 0).Value
    cell.HorizontalAlignment = cell.Offset(-1, 0).HorizontalAlignment
        End If
    Next
End Sub

 

[holmen]

Awesome, thank for al the help.
It's now working and I nearly have the whole thing done.
There is just some small fixing left to.

The last problem I have is with this script.

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

I am deleting all rows that don't ends with "." but I need to keep the last row that says "total" (and don't have a ".")
I'm trying to figure out how to exclude "total" from deleting.

any input?

 

[Fishboy]

Awesome, thank for al the help.
It's now working and I nearly have the whole thing done.
There is just some small fixing left to.

The last problem I have is with this script.

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

I am deleting all rows that don't ends with "." but I need to keep the last row that says "total" (and don't have a ".")
I'm trying to figure out how to exclude "total" from deleting.

any input?

Try Changing:

With .Range("A1:A" & lRow)
            [COLOR=#ff0000][B].AutoFilter Field:=1, Criteria1:="<>*.*"[/B][/COLOR]
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

To:

With .Range("A1:A" & lRow)
[B][COLOR=#ff0000]            .AutoFilter Field:=1, Criteria1:="<>*.", _[/COLOR][/B]
[B][COLOR=#ff0000]        Operator:=xlAnd, Criteria2:="<>total"[/COLOR][/B]
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With