0

The code works "beautifully"(it needs to be cleaned up etc.) but if I delete rows or modify rows in Worksheets("Material For Quotation") and rerun the code it defaults to the max rowcount (1,048,576). I've searched around and read the Microsoft stuff and I cant figure it out. What I am stumped on is that the code should run independent of what's in the worksheet, but the minute I delete or modify the worksheet rowcount doesn't work.

Does anyone have insight?

Sub QuoteToMaterial()

Dim wb As Workbook
Dim ws, wscheck, ws1, ws2 As Worksheet

Dim i, j, k, l As Integer
Dim dict As New Dictionary
Dim Str As String

Dim key As Variant

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Material For Quotation")
i = ws1.Cells(Rows.Count, 2).End(xlUp).row
j = 1
'init dictionary
For j = 2 To i
    If dict.Exists(Trim(UCase(ws1.Cells(j, 3).value))) = True Then
        dict(Trim(UCase(ws1.Cells(j, 3).value))) = dict(Trim(UCase(ws1.Cells(j, 3).value))) + ws1.Cells(j, 4).value
    Else
        dict.Add ws1.Cells(j, 3).value, ws1.Cells(j, 4).value
    End If
    
Next j

i = wb.Worksheets.Count
j = 1

For k = 1 To i
    Set wscheck = wb.Worksheets(k)
    Select Case True
        Case wscheck.Name = "Summary"
            GoTo Loopiter 'loop iterate
        Case wscheck.Name = "Material For Quotation"
            GoTo Loopiter
        Case wscheck.Name = "Pricing Summary"
            GoTo Loopiter
        Case wscheck.Name = "Installation 1"
            GoTo Loopiter
        Case wscheck.Name = "Installation 2"
            GoTo Loopiter
        Case wscheck.Name = "Inst Worksheet 1"
            GoTo Loopiter
        Case wscheck.Name = "Inst Worksheet 2"
            GoTo Loopiter
        Case Else
           
        Set ws = wb.Worksheets(k)
        'inserts $ per unit into the right place
        For l = 4 To 101
            
            If dict.Exists(ws.Cells(l, 9).value) = True Then
                ws.Cells(l, 11).value = dict(ws.Cells(l, 9).value)
            End If
            
        Next l

        j = j + 1
     End Select
Loopiter:
Next k

    

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Matt_PEI
  • 1
  • 1
    And by that you meant that `i` is set to that count? Sidenodes though: just something I found that looks "wrong" is: `dict(Trim(UCase(ws1.Cells(j, 3).value))) = dict(Trim(UCase(ws1.Cells(j, 3).value))`. Did you mean to add `cells(j,4)` instead? Another small note is that you can rewrite your `Select Case` a whole lot shorter. Also, the way you dim your variables.....you'll find you are using a lot of default appointed variables since you effectively not dimming these the same. – JvdV Nov 17 '20 at 12:52
  • Not likely that `i` is greater than 32,767 since you declared it as Integer. Should be `Long`. So, not sure what is resetting to max row count. – Darrell H Nov 17 '20 at 13:01
  • @DarrellH, as per my previous comment, `i` is actually **not** declared as Integer, but however as `Variant/Empty` on declaration. Valid point on the `Long` type though =) – JvdV Nov 17 '20 at 13:03
  • @JvdV, you are correct, if he would have been successful at dimming them he would have had the problem of `Integer` vs `Long`. – Darrell H Nov 17 '20 at 13:11
  • It's not clear, how and when the row count "defaults" to 1,048,576 the max number of rows, when `i` is calculated only once at the begining `i = ws.Cells(Rows.Count, 2).End(xlUp).Row`. Could you please elaborate on this. – EEM Nov 17 '20 at 14:09

1 Answers1

0

Sorry Guys the answer to this problem is that we ending up putting table formatting on columns 1-4 down to row 1,048,576. So using ws.cells(rows.count,2) goes to the bottom because they are all taken up by a table.

to correct either remove the table formatting and just cell fill to get the look or use the listobject and get the number of filled rows there.

Error in finding last used cell in Excel with VBA

Sorry all I didn't realize the problem until everyone focused on the messy code lol

Thx.

Matt_PEI
  • 1