garypea123
Board Regular
 Joined
 Mar 16, 2020
 Messages
 80
 Office Version

 365
 Platform

 Windows
Hi, I have the below macro written and I would like to to understand if I can can possibly apply this macro to run on
Monday Recieved
Tuesday Recieved
Wednesday Recieved
Thursday Recieved
Friday Recieved
I am sure that there is a neater way of doing this as opposed to writing the same script 5 times.
Sub Run
Sheets("Monday Recieved").Select  I assume that this will not be needed.
Columns("E:M").Select
Columns("E:M").EntireColumn.AutoFit
Range("E12").Select
Columns("J:J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B1").Select
ActiveCell.FormulaR1C1 = "Supplier"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Classification"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Band"
Dim c As Range, r As Range
Dim name As String
Const to_find As String = "ordered"
Set r = Sheet1.Range("F1:F" & Sheet1.Range("F" & Sheet1.Rows.Count).End(xlUp).Row)
Debug.Print r.Address
For Each c In r
If StrConv(c, vbLowerCase) = to_find Then
name = c.Offset(0, 1)
Else
c.Offset(0, 4) = name
End If
Next c
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[4]="""","""",IF(RC[4]=""Received"","""",IF(AND(RC[4]>=0,RC[1]<>""""),VLOOKUP(RC[1],Vlookup!C[3]:C[4],2,0),"""")))"
Range("C2:C" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[3]="""","""",IF(RC[3]=""Received"","""",IF(AND(RC[3]>=0,RC[1]<>""""),VLOOKUP(RC[1],Vlookup!C[2]:C[1],2,0),"""")))"
Range("D2:D" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Sub End
Thanks,
Gary
Monday Recieved
Tuesday Recieved
Wednesday Recieved
Thursday Recieved
Friday Recieved
I am sure that there is a neater way of doing this as opposed to writing the same script 5 times.
Sub Run
Sheets("Monday Recieved").Select  I assume that this will not be needed.
Columns("E:M").Select
Columns("E:M").EntireColumn.AutoFit
Range("E12").Select
Columns("J:J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B1").Select
ActiveCell.FormulaR1C1 = "Supplier"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Classification"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Band"
Dim c As Range, r As Range
Dim name As String
Const to_find As String = "ordered"
Set r = Sheet1.Range("F1:F" & Sheet1.Range("F" & Sheet1.Rows.Count).End(xlUp).Row)
Debug.Print r.Address
For Each c In r
If StrConv(c, vbLowerCase) = to_find Then
name = c.Offset(0, 1)
Else
c.Offset(0, 4) = name
End If
Next c
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[4]="""","""",IF(RC[4]=""Received"","""",IF(AND(RC[4]>=0,RC[1]<>""""),VLOOKUP(RC[1],Vlookup!C[3]:C[4],2,0),"""")))"
Range("C2:C" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[3]="""","""",IF(RC[3]=""Received"","""",IF(AND(RC[3]>=0,RC[1]<>""""),VLOOKUP(RC[1],Vlookup!C[2]:C[1],2,0),"""")))"
Range("D2:D" & Range("F" & Rows.Count).End(xlUp).Row).FillDown
Sub End
Thanks,
Gary