Hi All,
Please help me to get out of this issue.
I have attached the file and written the cold but i stuck when i want the agent name against its breaks except Total for #####. I tried the loop but could not apply the logic that how i could run the loop through particular range and skip the cells.
This code is used to filter the raw data.
Sub Filter_Data()
Dim sh As Worksheet
Dim SumSh As Worksheet
Set SumSh = ThisWorkbook.Sheets(3)
Set sh = ThisWorkbook.Sheets(2)
sh.Cells.UnMerge
sh.Columns.AutoFit
sh.Range("A1:B1").EntireColumn.Delete
sh.Range("B1:C1").EntireColumn.Delete
sh.Range("C1").EntireColumn.Delete
sh.Range("D1").EntireColumn.Delete
sh.Range("E1").EntireColumn.Delete
sh.Range("F1").EntireColumn.Delete
sh.Range("H1:I1").EntireColumn.Delete
sh.Range("I1:J1").EntireColumn.Delete
Dim rng As Range
lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lastrow)
Do
For i = 1 To rng.Count
If Cells(i, 1).Value = "" Then
Cells(i, 1).EntireRow.Delete
End If
Next i
Loop Until WorksheetFunction.CountBlank(rng) = 0
For i = 1 To rng.Count
If Left(Cells(i, 1), 4) = "Date" Then
Cells(i, 1).EntireRow.Delete
End If
Next i
sh.Rows.AutoFit
sh.Range("A1").EntireColumn.Insert
For i = 1 To rng.Count
If WorksheetFunction.Find("Agent:", Cells(i, 2)) = 1 Then
For j = 1 To rng.Count
If WorksheetFunction.Find("Total for", Cells(j, 2)) = 1 Then
Cells(i, 1) = Right(Cells(i, 2).Value, Len(Cells(i, 2).Value) - WorksheetFunction.Find(":", Cells(i, 2).Value) - 1)
End If
Next j
End If
Next i
End Sub
Second code in module need to be write to get the expected result.
Sub Testing()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Input File")
Dim rng As Range
Set rng = sh.Range("B1", Range("B1").End(xlDown))
Dim rng1 As Range
R = 1
Set rng1 = sh.Range("A1:A" & R)
For i = rng1.Count To rng.Count
For j = 1 To rng.Count
If Len(Cells(j, 1)) = "" Then
Cells(j, 1).Value = Value
End If
Next j
Next i
End Sub
