HI All i am relatively new to VBA but have been an excel use for many years
I need to write a macro that will search a specific column of data labelled customer and under a separate column input the reps name
[TABLE="width: 135"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Sales Rep[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Rep A[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]Rep B[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Rep C[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]Rep A[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD]Rep C[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD]Rep B[/TD]
[/TR]
[TR]
[TD]and so on[/TD]
[TD]and so on[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The example above can be as long as 500 lines some time longer. I would like to associate the right customer with the right Sales Rep so that when I run my clean up macro the data is peeped quickly for inputing into my Monthly sales report.
Currently I am using data validation and Lookup to do this but have to rest the data table each month. A simple active worksheet macro would be much more efficient.
Many thanks
Mark
Below is the macro I am in the process of putting together by virtue of finding something suitable online and then adapting for my needs as I am just starting to learn VBA. I am sure that with time this can be cleaned mu and made to be much more eloquent
Sub Sales_Data_Cleanup()
ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Sub Delete_Lotnumber_Evaluation()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ActiveWorkbook.ActiveSheet
lastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("C1:C" & lastRow)
' filter and delete all but header row
With rng
.AutoFilter Field:=1, Criteria1:=".Lot Number"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False
Set ws = ActiveWorkbook.ActiveSheet
lastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("C1:C" & lastRow)
' filter and delete all but header row
With rng
.AutoFilter Field:=1, Criteria1:=".Evaluation"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False
End Sub
Sub Add_Rep_Name()
Dim customer As Range, result As String
Set customer = Range("B1:B63")
If customer = Array("Adelaide Pathology Partners", "Alfred Health", "Austin Health", "Clinipath Pathology", "Department of Health Government of WA", "Dorevitch Pathology Primary Health", "Healthscope Pathology", "Melbourne Health", "Monash Health", "Melbourne Path Services Pty Ltd", "SA Pathology", "St Vincents Hospital Melbourne Ltd", "The Royal Children's Hospital", "Western Diagnostic Pathology", "Western Regional Central Supply") Then
result = "Victoria"
End If
Range("c2").Value = result
End Sub
I need to write a macro that will search a specific column of data labelled customer and under a separate column input the reps name
[TABLE="width: 135"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Sales Rep[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]Rep A[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]Rep B[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Rep C[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]Rep A[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD]Rep C[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD]Rep B[/TD]
[/TR]
[TR]
[TD]and so on[/TD]
[TD]and so on[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The example above can be as long as 500 lines some time longer. I would like to associate the right customer with the right Sales Rep so that when I run my clean up macro the data is peeped quickly for inputing into my Monthly sales report.
Currently I am using data validation and Lookup to do this but have to rest the data table each month. A simple active worksheet macro would be much more efficient.
Many thanks
Mark
Below is the macro I am in the process of putting together by virtue of finding something suitable online and then adapting for my needs as I am just starting to learn VBA. I am sure that with time this can be cleaned mu and made to be much more eloquent
Sub Sales_Data_Cleanup()
ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Sub Delete_Lotnumber_Evaluation()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ActiveWorkbook.ActiveSheet
lastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("C1:C" & lastRow)
' filter and delete all but header row
With rng
.AutoFilter Field:=1, Criteria1:=".Lot Number"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False
Set ws = ActiveWorkbook.ActiveSheet
lastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("C1:C" & lastRow)
' filter and delete all but header row
With rng
.AutoFilter Field:=1, Criteria1:=".Evaluation"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False
End Sub
Sub Add_Rep_Name()
Dim customer As Range, result As String
Set customer = Range("B1:B63")
If customer = Array("Adelaide Pathology Partners", "Alfred Health", "Austin Health", "Clinipath Pathology", "Department of Health Government of WA", "Dorevitch Pathology Primary Health", "Healthscope Pathology", "Melbourne Health", "Monash Health", "Melbourne Path Services Pty Ltd", "SA Pathology", "St Vincents Hospital Melbourne Ltd", "The Royal Children's Hospital", "Western Diagnostic Pathology", "Western Regional Central Supply") Then
result = "Victoria"
End If
Range("c2").Value = result
End Sub
