Problem in looping with rows

[lorenzo79]

Dear all ,

i have a problem in looping rows.
i have below macro that should looping column B from row13 ,instead is also looping row12 (that correspond to my table heather) ...
i have tried the code below but not working ...
can someone help please .
basicaly i need to find last row of column B and loop back untill row13 .
Many tks to anyone for help .

here below the code :

Dim lRow As Long
Dim Ring As Range

lRow = Cells(Rows.Count, 2).End(xlUp).Row
 
For Each Ring In Range("B13:B" & lRow)
  ActiveSheet.Unprotect
  Dim Rng As Range
  Dim foundrange As Range

  Dim wb As Workbook
  Dim fFilename As String

  test1 = Ring.Value
  Ring.EntireRow.Copy
  Ring.Offset(0, 18).Value = "OK"
  fFilename = "X:\CargoluxItalia\COMMON\RISK ANALYISIS\riskregister.xlsm"
  SetAttr fFilename, vbNormal
  Set wb = Workbooks.Open(fFilename)
  Set foundrange = Workbooks("riskregister").Sheets("sheet1").Range("b:b").Find(test1, , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
    If Not foundrange Is Nothing Then
        Workbooks("riskregister").Sheets("Sheet1").Select
        foundrange.Offset(0, -1).Select
        selection.PasteSpecial
        Application.CutCopyMode = False
    Else
        LastRow = Workbooks("riskregister").Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row + 1
        Workbooks("riskregister").Sheets("Sheet1").Rows(LastRow).PasteSpecial
        Application.CutCopyMode = False
        Ring.Offset(0, 12).Value = OK
    End If
    
Next

 

[Joe4]

Do you have any data in column B past row 12?
If not, then your "lRow" calculation may be returning 12 (or lower).

If you do not want to run your code if the last row is 12 or lower, after the "lRow" calculation, you can add this line of code:

If lRow < 13 Then Exit Sub

 

[lorenzo79]

cross reference

 

[Joe4]

Did you even attempt my suggestion?

 

[lorenzo79]

HI, sure i have just now tried but will exit the SUB without performing it for the other rows...

 

[lorenzo79]

i have also try this but still not working

lRow = Cells(Rows.Count, 2).End(xlUp).Row -12

 

[Joe4]

i have also try this but still not working

lRow = Cells(Rows.Count, 2).End(xlUp).Row -12

I don't see what that would accomplish.
What exactly are you trying to do there?

What exactly is the value of "lRow"?
If you are not sure, add a Message Box after the calculation in your code:

lRow = Cells(Rows.Count, 2).End(xlUp).Row
MsgBox lRow

 

[lorenzo79]

dear All ,
problem solved.

sorry for messed up with other thread.

 

[Joe4]

How was it solved?
What was the issue?

 

[lorenzo79]

insted of using for each to go trought the various rows i user below code to have the loop starting from row 13 :

For r = 13 To lRow