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

H

holmen

New Member
Joined
Sep 29, 2015
Messages
4
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:
Code: 
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
 
Sort by date Sort by votes
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.

Code: 
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"
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
holmen said:
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.

Code: 
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"
Click to expand...
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
How do I replace multiple cells of text with the first Number value above them? (w/o macros)
2
Replies
13
Views
884
alansidman
A
F
  • Question Question
VBA to Find and Replace in A Dynamic Range
Replies
0
Views
888
FrenchCelt
F
P
  • Solved
Macro that Adds +1.value to the Cell Above It.
Replies
4
Views
423
Poker Joe
P
4
  • Question Question
VBA - Dynamic Cell Shifting
Replies
4
Views
2K
4EverStudent
4
I
  • Question Question
VBA: Insert new row below active cell, copy data in specific cells above the new row.
Replies
1
Views
784
mumps
mumps

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top