Do untill command

Adrac

Active Member
Joined
Feb 13, 2014
Messages
280
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a macro that looks in A and finds a name, then finds a word of text in a cell the copies information.
I would like it to keep finding the word cells until it reaches another word cell:

Example:

Code:
Cells.Find(What:="Bob", After:=ActiveCell).Activate
Cells.Find(What:="Inbound", After:=ActiveCell).Activate
ActiveCell.Offset(0, 2).Range("A1").Copy
ActiveCell.Offset(0, 16).Range("A1").Select
ActiveSheet.Paste
***this ia where i want it to go to***
do this command above until it finds the word 'Wrokgroup' in a cell then next command (person)

Can anyone help?

Adrac
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I don't know what ranges precisely you are working with since you are using select code and aren't really defining it to start with, but my suggestion would be to find the first instance of the "Bob" and the last instance of "Bob", if all instances of this are are all found next to each other than you can use 1 simple copy paste method with no loop.
 
Upvote 0
Hello Brain,

This is my problem, i dont have another instance of 'bob' ill give you a demo of what the report looks like. also its never the same so one day bob will have inbound once and somtimes twice.

A</SPAN>
C</SPAN>
bob</SPAN>
inbound</SPAN>
5</SPAN>
inbound</SPAN>
10</SPAN>
outbound</SPAN>
7</SPAN>
workgroup
trish</SPAN>
inbound</SPAN>
8</SPAN>
outbound</SPAN>
5</SPAN>
workgroup
frank</SPAN>
inbound </SPAN>
5</SPAN>
inbound </SPAN>
6</SPAN>
outbound</SPAN>
4</SPAN>

<TBODY>
</TBODY>
 
Upvote 0
ill be pasting it to coloumn Q and gather the results then paste that result into another worksheet. but i only want to know how i can loop through the inbound.im not sure im making myself clear.
 
Upvote 0
You could loop through only the inbound like so (I truly think you should be able to use code that doesn't use Loops which is better):

Code:
Sub LoopThroughInbound()
Dim LR As Range, Ce As Range
Dim WS1 As Worksheet

Set WS1 = Worksheets("Sheet1") 'Rename Sheet1 as needed
Set LR = WS1.Range("A" & Rows.Count).End(xlUp)
Set LR = WS1.Range(Cells(1, 1), LR)
WS1.Range(Cells(1, 1), LR).AutoFilter Field:=1, Criteria1:="Inbound"
Set LR = LR.SpecialCells(xlCellTypeVisible)
For Each Ce In LR
    Ce.Offset(0, 16).Value = Ce.Offset(0, 1).Value
Next
WS1.AutoFilterMode = False
End Sub
 
Upvote 0
Hello Brian,

thank you for your suggestion but isnt what im going for. what i need is the result of each user and the factor its not always in the same place. so in this case bob has 15 inbound calls so to count 15, then take that result to another workbook.
so: 1) find user 'bob' 2) get total result in all inbound calls for him 3) total transfered to results workbook 4) then do the other people
but somtimes the user is not in so there needs to be a 'on error next' type of thing.

Sorry if i wasnt clear before.

Adrac
 
Upvote 0
ok let me explain this again lol

Below is data that comes in, what i need to do is extract that data and enter it into another spreadsheet.
so i need to extract who has what. ie Bob Baker has a result in C6,C7,C8 and total that up into one figure then move it to another worksheet.
The location of the data changes every day and sometime Bob has a day off so nothing is in it at all.

Hope this helps

A
B
C
1
User Name: bakerb (Active)
2
Agent Name: Baker, Bob
3
4
Interaction Type / Taken From
Originated
Completed
5
Queued Call
6
Inbound
16
16
7
Inbound Overflow
1
1
8
Inbound Overflow 2
1
1
9
User Total:
18
18
10
Workgroup: WAK01
11
User Name: barnesk (Active)
12
Agent Name: Barnes, Kim
13
14
Interaction Type / Taken From
Originated
Completed
15
Inbound Queued Call
16
Inbound
9
9
17
Inbound Overflow
1
1
18
Outbound Direct Call
19
<N A>
7
5
20
User Total:
17
15
21
Workgroup: WAK01
22
User Name: daviesn (Active)

<TBODY>
</TBODY>
 
Upvote 0
It seems that something like this would work for your case. I wrote it in such a way that it should not need on error resume next statements. Also you can write this to loop through a range of cells that contains the names you are searching for, but I wasn't sure where or if that even resided anywhere so O worked an array in for you if it be helpful:
Code:
Option Explicit
Sub Inbound()
Dim nBnd As Double, x As Integer
Dim Rng As Range, CE1 As Range, Rw As Range, CE2 As Range
Dim RcrdSh As Worksheet, DtSh As Worksheet
Dim RcrdWb As Workbook, DtWb As Workbook
Dim NameA(4) As String 'the 4 will be adjusted to fit your size
'You don't have to use an array to do this if you have your names somewhere that can be looped through

NameA(1) = "Bob"
NameA(2) = "Kim"
NameA(3) = "Jobe"
NameA(4) = "Empty"

Set RcrdWb = Workbooks("Results.xlsx") 'adjust to workbook to which you are recording
Set DtWb = Workbooks("data.xlsx") ' adjust to sheet name of the recording workbook
Set RcrdSh = RcrdWb.Worksheets("Sheet1") 'likewise with workbook you are getting data from
Set DtSh = DtWb.Worksheets("Sheet1") 'and sheet you will be getting it from

For x = 1 To 4 'The 4 will likely need to be adjusted with your NameA array
    Set CE1 = DtSh.Range("A:A").Find("*" & NameA(x), After:=Cells(1, 1))
    If Not CE1 Is Nothing Then
        Set Rw = DtSh.Range(CE1, DtSh.Cells(Rows.Count, "A").End(xlUp)).Find("Workgroup*", After:=CE1)
        If Rw Is Nothing Then Set Rw = DtSh.Cells(Rows.Count, "A").End(xlUp)
        Set Rng = Range(CE1, Rw)
        nBnd = Application.WorksheetFunction.SumIf(Rng, "inbound*", Rng.Offset(0, 2))
        Set CE2 = RcrdSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        CE2 = NameA(x)
        CE2.Offset(0, 1) = "Inbound"
        CE2.Offset(0, 2) = nBnd
    End If
Next

End Sub
 
Upvote 0
Excellent this is more like it, thank you. Can i add a command so i can see what its doing as im not getting any results or to tailor make it please?</SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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