VBA With Worksheets Statement Limitations?

[QueenOfLean]

Hi guys, I'm new to posting but have been using this forum to answer Excel questions for years. I hope I post this right and if not, please tell me so I can fix it.

Problem: I'm cleaning up some code I wrote a few years ago. Instead of referencing the worksheet every time, I'm trying to use the With End With statement. I've had luck with it in other macros but this particular one isn't playing nice. I get error 1004 when trying to change a cells interior color, but it is definitely the cell reference that is a problem. Is it because I'm looping or using arrays inside the With statement? What are the limitations to using the With statement? I've googled for 2 days with no luck. My code is below. Thanks in advance!

With Worksheets("Linelist")


    Set found = .Cells.Find(What:="Bowen Sort Code", LookAt:=xlWhole, MatchCase:=True)
    HR = found.Row
    Set found = .Cells.Find(What:="END OF LIST - INSERT NEW ROWS ABOVE HERE", LookAt:=xlWhole, MatchCase:=True)
    LastRow = found.Row - 1
    Set found = .Cells.Find(What:="Database Label", LookAt:=xlWhole, MatchCase:=True)
    dbCol = found.Column
    Set found = .Cells.Find(What:="Use Mat. Unit Cost (Y)", LookAt:=xlWhole, MatchCase:=True)
    mpCol = found.Column
    Set found = .Cells.Find(What:="Qty LF", LookAt:=xlWhole, MatchCase:=True)
    lfCol = found.Column
    Set found = .Cells.Find(What:="Pipe", LookAt:=xlWhole, MatchCase:=True)
    pipeCol = found.Column
    Set found = .Cells.Find(What:="HNDLG Labor Unit", LookAt:=xlWhole, MatchCase:=True)
    miscCol = found.Column - 1
    
    LastCol = .UsedRange.Columns.Count + 1
    Set llRange = .Range(Cells(1, 1).Address, Cells(LastRow, LastCol).Address)
    
    .Cells(HR, pipeCol).Interior.Color = RGB(255, 0, 0)
    ReDim llArray(1 To LastRow, 1 To LastCol)
    llArray = llRange.Value
    
    'loop through array and color cells if no match
    'Note rows are first in both cell reference and array
    For i = HR + 1 To LastRow
        If llArray(i, dbCol) = "" Then
        GoTo SkipRow
        End If
        If IsError(llArray(i, pipeCol)) Then
            If llArray(i, pipeCol) = CVErr(xlErrNA) Then
'.Range reference is working here
                .Range(.Cells(i, 4), .Cells(i, 7)).Interior.Color = RGB(255, 0, 0)
                GoTo SkipRow
            End If
        End If
        
        'match handling & material for pipe & fittings
        For j = lfCol To miscCol Step myStep
            'find matching handling & material columns
            If llArray(HR, j) = "Qty LF" Then
                matchString = "Pipe"
            Else
                matchString = Mid(llArray(HR, j), 5, Len(llArray(HR, j)))
            End If
            Set found = .Cells.Find(What:=matchString, LookAt:=xlWhole, MatchCase:=True)
            handCol = found.Column
            matchString = matchString & " $ea"
            Set found = .Cells.Find(What:=matchString, LookAt:=xlWhole, MatchCase:=True)
            matCol = found.Column
            If llArray(i, j) = "" Then
                'Haven't made this one true yet, but I'm sure it would error as well
                .Cells(i, j).Interior.Color = xlNone
                .Cells(i, handCol).Interior.Color = RGB(146, 208, 80)
                .Cells(i, matCol).Interior.Color = RGB(155, 194, 230)
            Else
                If IsNumeric(llArray(i, handCol)) And llArray(i, handCol) > 0 Then
                    Sheets("Linelist").Cells(i, handCol).Interior.Color = RGB(146, 208, 80)
'.Cells reference is not working here
                    .Cells(i, j).Interior.Color = xlNone
                Else 'if handling is 0 turn both cells red
                    .Cells(i, handCol).Interior.Color = RGB(255, 0, 0)
                    .Cells(i, j).Interior.Color = RGB(255, 0, 0)
                End If
                If UCase(llArray(i, mpCol)) = "Y" And Not IsNumeric(llArray(i, matCol)) And llArray(i, matCol) < 0 Then
                    .Cells(i, matCol).Interior.Color = RGB(255, 0, 0)
                    .Cells(i, j).Interior.Color = RGB(255, 0, 0)
                Else
                    .Cells(i, matCol).Interior.Color = RGB(155, 194, 230)
                    .Cells(i, j).Interior.Color = xlNone
                End If
                If myStep = 2 Then
                    Set found = .Cells.Find(What:=matchString, LookAt:=xlWhole, MatchCase:=True)
                    trackCol = found.Column
                    .Cells(i, trackCol).Interior.Color = RGB(255, 192, 0)
                    If llArray(i, trackCol) > llArray(i, j) Then
                        .Cells(i, trackCol).Interior.Color = RGB(255, 0, 0)
                    End If
                End If
            End If
        Next j


SkipRow:


    Next i


End With

PS I feel like my code doesn't look right in the preview. Can someone tell me how to get it to show in that little window like I normally see?

 

[SpillerBD]

Its not working where you invoke Worksheets twice?
once with the WITH statement and then again with SHEETS?

 

[QueenOfLean]

Thanks SpillerBD. I think you are referring to this piece of code:

If IsNumeric(llArray(i, handCol)) And llArray(i, handCol) > 0 Then
    Sheets("Linelist").Cells(i, handCol).Interior.Color = RGB(146, 208, 80)
'.Cells reference is not working here
    .Cells(i, j).Interior.Color = xlNone

I pulled the extra sheet reference out, but I still get the error with .cells. Any other thoughts?

 

[dmt32]

Hi,
just a thought, using all those Range.Find methods you should validate that each one has returned the required result & if not, inform user

Untested but maybe something along following lines will help you

Sub QueenOfLean()
    
    Dim Found As Range
    Dim Search As String
    Dim Arr(1 To 7) As Long
    Dim i As Integer
    Dim wsLinelist As Worksheet
    
    Set wsLinelist = ThisWorkbook.Worksheets("Linelist")
    
    With wsLinelist
        For i = 1 To 7
'search values
            Search = Choose(i, "Bowen Sort Code", "END OF LIST - INSERT NEW ROWS ABOVE HERE", "Database Label", _
                                "Use Mat. Unit Cost (Y)", "Qty LF", "Pipe", "HNDLG Labor Unit")
'search
            Set Found = .Cells.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
            
'confirm search value found
            If Not Found Is Nothing Then
'add values to array
                If i < 3 Then Arr(i) = Found.Row Else Arr(i) = Found.Column
            Else
'inform user
                MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
                Exit Sub
            End If
'clear variable
            Set Found = Nothing
        Next i
            
'intialize variables
            HR = Arr(1)
            LastRow = Arr(2) - 1
            dbCol = Arr(3)
            mpCol = Arr(4)
            lfCol = Arr(5)
            pipeCol = Arr(6)
            miscCol = Arr(7) - 1
            
            LastCol = .UsedRange.Columns.Count + 1
        Set llRange = .Cells(1, 1).Resize(LastRow, LastCol)
            
'rest of code
            
        End With
End Sub

here I have placed all 7 searches in a For Next loop & if record found, place required value in array (column or row) if not, msgbox displayed to inform user & exit sub
If all 7 searches found, Array is used to populate your variables or you could dispense with variables & just use array in your code.

Just an idea but hope of some help

Dave

 

[QueenOfLean]

Thanks Dave! I'm new to arrays, and now I have another use for one! The search values better be found because they are column headers which are protected plus users are warned not to change them. Therefore, my message box will not be as nice as yours.

 

[QueenOfLean]

OMG! It was a dimensioning error! I apparently got too happy dimensioning strings and accidentally dimensioned some columns as strings. So sorry for wasting all of your time! Thanks to Dave for the quick code to set my column variables anyhow. I've used that in several macros already.