macro corrections

[Tonyk1051]

in tab 1 named (Not on a Category) column D (Vendors) is a list of random vendors. In tab 3 named (Vendor Policies) column A is a list of every vendor. Any vendors in the Not on a Category tab that matches the vendors in the Vendor Policy and has a yes in column J Write TT in column H of the Not on a Category Tab....macro bombs out everytime i run it...

[/code]
Sub testing()
Dim ws1 As Worksheet, ws3 As Worksheet
Dim lastRow1 As Long, lastRow3 As Long
Dim i As Long, j As Long

Set ws1 = ThisWorkbook.Sheets("Not on a Category")
Set ws3 = ThisWorkbook.Sheets("Vendor Policies")

lastRow1 = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row
lastRow3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastRow1
For j = 2 To lastRow3
If ws1.Cells(i, "D").Value = ws3.Cells(j, "A").Value And ws3.Cells(j, "J").Value = "yes" Then
ws1.Cells(i, "H").Value = "TT"
Exit For
End If
Next j
Next i
End Sub
[/code]

heres the link to test

Repair Items67576.xlsm

Shared with Dropbox

www.dropbox.com

 

[Tonyk1051]

Hi Tony,

maybe change the procedure to use AutoFilter on both sheets instead of looping though the entire ranges:

Public Sub MrE_1227851_1701809()
' https://www.mrexcel.com/board/threads/macro-corrections.1227851/
Dim lngCellsVis As Long
Dim rngHelp As Range
Dim rngCell As Range
Dim wsVenPol As Worksheet
Dim wsNotCat As Worksheet

Set wsNotCat = ThisWorkbook.Worksheets("Not on a Category")
Set wsVenPol = ThisWorkbook.Worksheets("Vendor Policies")

With wsVenPol
  If .AutoFilterMode Then .AutoFilterMode = False
  .Range("A1").CurrentRegion.AutoFilter Field:=10, Criteria1:="<>"
  If WorksheetFunction.CountIf(.Range("J:J"), "Yes") > 0 Then
    Set rngHelp = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)
  End If
  .ShowAllData
End With

If Not rngHelp Is Nothing Then
  With wsNotCat
    If .AutoFilterMode Then .AutoFilterMode = False
    For Each rngCell In rngHelp
      .Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:=rngCell.Value
      lngCellsVis = .Cells(.Rows.Count, "D").End(xlUp).Row
      If lngCellsVis > 1 Then
        .Range("H2:H" & lngCellsVis).SpecialCells(xlCellTypeVisible).Value = "TT"
      End If
    Next rngCell
    .ShowAllData
  End With
End If

end_here:
Set rngHelp = Nothing
Set wsVenPol = Nothing
Set wsNotCat = Nothing

End Sub

Ciao,
Holger

hi, i ran the macro on a new work same tab names but it had 2500 lines and i got run time 9 script out of range error. when i click debug
Set wsNotCat = ThisWorkbook.Worksheets("Not on a Category") this is highlighted yellow...

 

[HaHoBe]

Hi Tony,

code must be in the workbook you are working on due to ThisWorkbook (change to ActiveWorkbook if workbooks differ and make sure that the workbook to run the procedure on is the active one, this also goes for code residing in a personal macro workbook personal.xlsb) and check the sheet name of worksheet to proceed.

Holger

 

[Tonyk1051]

Hi Tony,

code must be in the workbook you are working on due to ThisWorkbook (change to ActiveWorkbook if workbooks differ and make sure that the workbook to run the procedure on is the active one, this also goes for code residing in a personal macro workbook personal.xlsb) and check the sheet name of worksheet to proceed.

Holger

Sorry man, I made the change to active workbook and it didn’t something completely different…

 

[HaHoBe]

Hi Tony,

I located the error to be in line

          For Each rngTT In Intersect(wsNotCat.UsedRange, wsNotCat.Columns(4).SpecialCells(xlCellTypeVisible))

It seems that building a range of only one visible cell will lead to a behaviour I usually identify to be valid on CheckSpelling.

The following code works for me only on Column D in Worksheets("Not on a Category") where the range will be at least the header and one visible cell:

Public Sub MrE_1227851Update2()
' https://www.mrexcel.com/board/threads/macro-corrections.1227851/
Dim rngCell       As Range
Dim rngTT         As Range
Dim wsVenPol      As Worksheet
Dim wsNotCat      As Worksheet

Set wsNotCat = ThisWorkbook.Worksheets("Not on a Category")
Set wsVenPol = ThisWorkbook.Worksheets("Vendor Policies")

With wsVenPol
  If .AutoFilterMode Then .AutoFilterMode = False
  .Range("A1").CurrentRegion.AutoFilter Field:=13, Criteria1:="No"
End With
If WorksheetFunction.CountIf(wsVenPol.Range("M:M"), "No") > 0 Then
  For Each rngCell In wsVenPol.Range("A2", wsVenPol.Cells(wsVenPol.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)
    With wsNotCat
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1").CurrentRegion.AutoFilter Field:=23, Criteria1:="Damaged"
      .Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:=rngCell.Value
      If .Cells(.Rows.Count, "D").End(xlUp).Row > 1 Then
        For Each rngTT In .Range("D1", .Cells(WorksheetFunction.Max(.Cells(.Rows.Count, "D").End(xlUp).Row, 2), "D")).SpecialCells(xlCellTypeVisible)
          If rngTT.Row > 1 Then rngTT.Offset(0, 4).Value = "TT"
        Next rngTT
      End If
    End With
  Next rngCell
End If
If wsNotCat.AutoFilterMode Then wsNotCat.AutoFilterMode = False
wsVenPol.ShowAllData

end_here:
Set wsVenPol = Nothing
Set wsNotCat = Nothing

End Sub

I hope this code works for you as well.

Ciao,
Holger