Advertisement
Guest User

Untitled

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