0

All, I am working part of my code where I want to update filtered noncontiguous cells with index / match. Core of my code is working in proper manner in another case but here seems wrong and do not know what is the reason behind. Working turn endless and could cause that no last row find in section here: Set rngI = usedR.Resize(usedR.Rows.Count - 1).Offset(5).Columns("I:I").SpecialCells(xlCellTypeVisible). Checked with debug.print the result which shows me the end as wrong...$I$174:$I$1046999...proper has to be $I$174:$I$197...what could be the reason behind? Another question using lastrow calculation..on this way this doesnt work, Dim lastrow As Long, lastrow = rngD(Rows.Count, 1).End(xlUp).row I have to correct like this to count...lastrow = rngD(rngD.Rows.Count, 1).End(xlUp).row. What's the reason behind that once working on first way, once only if I double type range. This code is in Personal folder if it counts anyway

Sub Macro2()

Dim wbD As Workbook: Set wbD = Workbooks("dashboard-advanced.xls")
Dim wsD As Worksheet: Set wsD = wbD.Sheets("Sheet1")
Dim rngD As Range: Set rngD = wsD.Range("A:C")

Dim wbCallLCL As Workbook: Set wbCallLCL = Workbooks("CALL_REPORT.xlsx")
Dim wsCallLCL As Worksheet: Set wsCallLCL = wbCallLCL.Sheets("LCL")
Dim rngCallLCL As Range: Set rngCallLCL = wsCallLCL.Range("A:V")

rngCallLCL.autofilter Field:=10, Criteria1:=Blanks

Dim lastrow As Long
lastrow = rngD(rngD.Rows.Count, 1).End(xlUp).row

Dim usedR As Range, rngI As Range, A As Range, C As Range

Set usedR = wsCallLCL.UsedRange
Set rngI = usedR.Resize(usedR.Rows.Count - 5).Offset(1).Columns("I:I").SpecialCells(xlCellTypeVisible)
 For Each A In rngI.Areas
     For Each C In A.Cells
            res = Application.Match(C.Value, wsD.Range("A2:" & "A" & lastrow), 0)
        
        If IsError(res) Then
                C.Offset(, 1).Value = ""
            Else
                C.Offset(, 1).Value = Application.WorksheetFunction.Index(wsD.Range("B2:" & "B" & lastrow), res, 0)
    
        End If
   
   Next C
 Next A
    
End Sub
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
user19102522
  • 3
  • 3
  • `UsedRange` is unreliable in finding the last row, see [this](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) for a description why. – BigBen Jul 28 '22 at 13:14
  • `Dim lastrow As Long, lastrow = rngD(Rows.Count, 1).End(xlUp).row` this should be two rows, or joined using `:` rather than `,`. You wouldn't usually use the `rngD` range - just the worksheet will do `Dim lastrow As Long: lastrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row`. – Darren Bartrup-Cook Jul 28 '22 at 14:37
  • Make sure you have `Option Explicit` at the top of your module - you said the only way you could get that line to work is removing the `Dim` part. Without `Option Explicit` you don't have to declare variables first - so if later on you accidently use `lastrowq` instead of `lastrow` it will happily use the new variable and give you an incorrect result. – Darren Bartrup-Cook Jul 28 '22 at 14:40
  • BigBen thx solved...modified my row on this way and now working in proper manner in a sec – user19102522 Jul 28 '22 at 14:51
  • Darren thx for your comments sorry if I mislead you...not Dim where deleted, I only not wanted to double type...so difference is: I have to type before and in round bracket too set range I want to work with...some cases not..why? With your suggestion anyway with wsD.Cells even doesnt work, send me run tim error 1004, application defined or object defined error... – user19102522 Jul 28 '22 at 15:04

0 Answers0