Advertisement
Guest User

Untitled

a guest
Apr 23rd, 2017
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. Sub saveMaxAndDate()
  3.     Dim resWorksheet As Worksheet, objectsWorksheet As Worksheet, debitWorksheet As Worksheet
  4.     Set resWorksheet = Worksheets("Sheet3")
  5.     Set objectsWorksheet = Worksheets("Sheet2")
  6.     Set debitWorksheet = Worksheets("Sheet1")
  7.    
  8.     Dim row As Integer, j As Integer
  9.     Dim max As Double
  10.     Dim maxDate As Date
  11.     Dim maxObjects As String
  12.    
  13.     Dim commaPos As Integer, commaPosNext As Integer
  14.     Dim startMutual As Boolean
  15.     Dim mutualBeginDate As Date, mutualEndDate As Date
  16.    
  17.     For row = 2 To debitWorksheet.Range("C2").End(xlDown).row
  18.         resWorksheet.Cells(row, 1) = debitWorksheet.Cells(row, 3)
  19.         col = 2
  20.         max = -1
  21.         startMutual = False
  22.         Do While Not IsEmpty(debitWorksheet.Cells(1, col + 2))
  23.             If Not IsEmpty(debitWorksheet.Cells(row, col + 2)) And debitWorksheet.Cells(row, col + 2).Value > max Then
  24.                 max = debitWorksheet.Cells(row, col + 2).Value
  25.                 maxDate = debitWorksheet.Cells(1, col + 2).Value
  26.                 maxObjects = objectsWorksheet.Cells(row, col + 2).Value
  27.             End If
  28.            
  29.             commaPos = InStr(objectsWorksheet.Cells(row, col + 2).Value, ",")
  30.             commaPosNext = InStr(objectsWorksheet.Cells(row, col + 3).Value, ",")
  31.            
  32.             If startMutual Then
  33.                     If IsEmpty(objectsWorksheet.Cells(row, col + 3)) Or commaPosNext = 0 Then
  34.                         startMutual = False
  35.                         mutualEndDate = objectsWorksheet.Cells(1, col + 2).Value
  36.                         resWorksheet.Cells(row, 4) = mutualEndDate
  37.                     End If
  38.             End If
  39.            
  40.             If Not IsEmpty(objectsWorksheet.Cells(row, col + 2)) Then
  41.                
  42.                 If Not startMutual And commaPos <> 0 And commaPosNext <> 0 Then
  43.                     startMutual = True
  44.                     mutualBeginDate = objectsWorksheet.Cells(1, col + 2).Value
  45.                     resWorksheet.Cells(row, 2) = "Yes"
  46.                     resWorksheet.Cells(row, 3) = mutualBeginDate
  47.                 End If
  48.             End If
  49.            
  50.             col = col + 1
  51.         Loop
  52.        
  53.         If max > 0 Then
  54.             resWorksheet.Cells(row, 5) = max
  55.             resWorksheet.Cells(row, 6) = maxDate
  56.             resWorksheet.Cells(row, 7) = maxObjects
  57.         End If
  58.        
  59.     Next row
  60. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement