Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Muy code is not going through loops 2) and 3) below, just cant figure it out
Many Thanks Public Sub coi3() Dim fin As Workbook Dim fin2 As Workbook Dim vArr As Variant Dim vArr2 As Variant Dim rCell As Range Dim rDest As Range Dim sDest As Range Dim i As Long Dim j As Long Dim FoundClient As Boolean '1)Opens Team CB and Team MS Workbooks,define arrays in line with Client Lists Set fin = Application.Workbooks.Open( _ "C:\My Documents\Business Plans\TeamCB.xls") Set fin2 = Application.Workbooks.Open( _ "C:\My Documents\Business Plans\TeamMS.xls") vArr = Array("Hudson", "HSBC", "C&W") vArr2 = Array("ACCENT", "AMEX", "SHELL") FoundClient = False For Each rCell In Range("D1:D" & _ Range("D" & Rows.Count).End(xlUp).Row) With rCell ' Check for Team CB's client: For i = LBound(vArr) To UBound(vArr) If .Value = vArr(i) Then Set rDest = fin.Worksheets(vArr(i)).Cells( _ 25, 1).End(xlUp).Offset(1, 0) ..EntireRow.Copy Destination:=rDest FoundClient = True End If Next i 1)' If CB's client can skip, otherwise: If Not FoundClient Then 2)' Check for Team MS's client: For j = LBound(vArr2) To UBound(vArr2) If rCell.Value = vArr2(j) Then Set sDest = fin2.Worksheets(vArr2(j)).Cells( _ 25, 1).End(xlUp).Offset(1, 0) ..EntireRow.Copy Destination:=sDest FoundClient = True End If Next j 3)' If neither was found, then check your other condition (executiveis CB): If Not FoundClient Then If .Offset(0, 3).Value = "CB" Then ..EntireRow.Copy _ Destination:=fin.Worksheets("OTHER").Cells(25, 1).End(xlUp).Offset(1, 0) End If End If End If End With Next rCell End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
Looping | Excel Programming | |||
looping every third row | Excel Programming | |||
Looping | Excel Programming | |||
Need Looping Help | Excel Programming |