Looping problem -

[Sphinx404]

Greetings Experts,

I'm trying to look at the value of a cell in a range. In this case, the range is range("AK2", range("AK" & myLastRow)

If the cell contains "L" input =IF(AL2=AJ2,TRUE, FALSE)) - 3 cells down (in "AN2")
if the cell contains "KG" input =IFERROR(IF(OR(AJ2<am2*0.95,aj2>AM2*1.05),""Outside Limits"",""Within Limits""),""Error"") - 3 cells down (in "AN2")

I have never actually written a looping code before because I can't seem to get my ranges on point and I over think it creating variables for every single range... .. I've been using the examples/solutions I've received off of this forum previously, but I'm continually getting 1004 object-defined error.

I obviously did something wrong, but it looks pretty good to me. Any ideas? What am I not taking into account?

Sub weights()


Dim myWorksheet As Worksheet
Dim myLastrow As Long
Dim mycell As Range


Set myWorksheet = Worksheets("HazShipper")
myLastrow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row


For Each mycell In Range("AK2", Range("AK" & myLastrow))


If mycell.Value = "L" Then mycell.Offset(, 3).[COLOR=#ff0000]Formula [/COLOR]= "=IF(AL2=AJ2,TRUE, FALSE))"
If mycell.Value = "KG" Then mycell.Offset(, 3).[COLOR=#ff0000]Formula [/COLOR]= "=IFERROR(IF(OR(AJ2<am2*0.95,aj2>AM2*1.05),""Outside Limits"",""Within Limits""),""Error"")"


Next mycell


End Sub

Thank you gents.</am2*0.95,aj2></am2*0.95,aj2>

 

[V_Malkoti]

My guess
"=IF(AL2=AJ2,TRUE, FALSE))" : Extra closing parentheses
"=IFERROR(IF(OR(AJ2AM2*1.05),""Outside Limits"",""Within Limits""),""Error"")" : Missing operator between cell references

 

[Rick Rothstein]

Set myWorksheet = Worksheets("HazShipper")
myLastrow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row

For Each mycell In [B][COLOR="#FF0000"]Range("AK2", Range("AK" & myLastrow))[/COLOR][/B]

The range (both of them) are pointing to the active sheet... my guess is the active sheet is not the HazShipper sheet. If I am right, change the For Each line of code to this (note I simplified your Range call somewhat)...

For Each mycell In myWorksheet.Range("AK2:AK" & myLastRow)

and see if that solves your problem.

 

[Sphinx404]

The range (both of them) are pointing to the active sheet... my guess is the active sheet is not the HazShipper sheet. If I am right, change the For Each line of code to this (note I simplified your Range call somewhat)...

For Each mycell In myWorksheet.Range("AK2:AK" & myLastRow)

and see if that solves your problem.

Thanks Rick! Appreciate you guys showing me the ropes.

 

[Sphinx404]

@Rick Rothstein @V_Malkoti I'm still working on the coding for the above issue. I am unsure on how to code the following code.

Everything is more or less how I think it should be, but I know the organization is messed up. Could you take a look at the code below and let me know what is missing and/or why this isn't going to work.

Sub weights()


Dim myWorksheet As Worksheet
Dim myLastRow As Long
Dim mycell As Range
Dim mycell2 As Range


Set myWorksheet = Worksheets("HazShipper")
myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row


[COLOR=#ff0000]For Each mycell In myWorksheet.Range("AK2:AK" & myLastRow)[/COLOR]
[COLOR=#ff0000]For Each mycell2 In myWorksheet.Range("AD2:AD" & myLastRow)[/COLOR]


If mycell.Value = "L" [COLOR=#ff0000]& mycell2.Value[/COLOR] = "UN3363" Then
    mycell.Offset(, 3).Formula = "=IF(AL2=AJ2,TRUE,FALSE)"
If mycell.Value = "L" Then
    mycell.Offset(, 3).Formula = "<al2*0.95,aj2>=IFERROR(IF(OR(AJ2<<al2*0.95,aj2>AL2*0.95,AJ2>AL2*1.05),""Outside Limits"",""Within Limits""),""Error"")"
Else
    mycell.Offset(, 3).Formula = "=IF(AL2=AJ2,TRUE,FALSE)"
End If


[COLOR=#ff0000]Next mycell2[/COLOR]
[COLOR=#ff0000]Next mycell[/COLOR]

End Sub

Looping, at this point in my learning... is my downfall. I really just need to sit down and take a quick online course.

Thank you!

</al2*0.95,aj2></al2*0.95,aj2>

 

[Rick Rothstein]

@Rick Rothstein @V_Malkoti I'm still working on the coding for the above issue. I am unsure on how to code the following code.

Everything is more or less how I think it should be, but I know the organization is messed up. Could you take a look at the code below and let me know what is missing and/or why this isn't going to work.

Sub weights()


Dim myWorksheet As Worksheet
Dim myLastRow As Long
Dim mycell As Range
Dim mycell2 As Range


Set myWorksheet = Worksheets("HazShipper")
myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row


For Each mycell In myWorksheet.Range("AK2:AK" & myLastRow)[/COLOR]
For Each mycell2 In myWorksheet.Range("AD2:AD" & myLastRow)[/COLOR]


If mycell.Value = "L" & mycell2.Value = "UN3363" Then
    mycell.Offset(, 3).Formula = "=IF(AL2=AJ2,TRUE,FALSE)"
[B][COLOR="#FF0000"]Else[/COLOR][/B]If mycell.Value = "L" Then
    mycell.Offset(, 3).Formula = "<al2*0.95,aj2>=IFERROR(IF(OR(AJ2<<al2*0.95,aj2>AL2*0.95,AJ2>AL2*1.05),""Outside Limits"",""Within Limits""),""Error"")"
Else
    mycell.Offset(, 3).Formula = "=IF(AL2=AJ2,TRUE,FALSE)"
End If


Next mycell2
Next mycell

End Sub

Looping, at this point in my learning... is my downfall. I really just need to sit down and take a quick online course.

Assuming your loops will do what you want them to, they are set up correctly. The problem with your code is the first If..Then statement. You put its code on the next line which made it not complete. There are a couple of options available to you. One, you could move its code line up onto the same line as the Then; two, you could put an "End If" statement below that code line; or, three, you can do what I show in red above which I think should perform the logic you want as well.

 

[V_Malkoti]

The loops in your code are nested loops, i.e., inner loop will be executed for all of its values for each iteration of outer loop. What that means is:
(suppose myLastRow = 5)
Outer Loop Iteration1 : AK2
Inner Loop Iteration1 : AD2
Inner Loop Iteration2 : AD3
Inner Loop Iteration3 : AD4
Inner Loop Iteration4 : AD5
Outer Loop Iteration2 : AK3
Inner Loop Iteration1 : AD2
Inner Loop Iteration2 : AD3
Inner Loop Iteration3 : AD4
Inner Loop Iteration4 : AD5
Outer Loop Iteration3 : AK4
...

However if you want to just iterate over each row and pick up values of both AD and AK from that same row, you need a single loop.

For i=2 to myLastRow
  Set myCell = myWorksheet.Range("AK" & i)
  Set myCell2 = myWorksheet.Range("AD" & i)

  If myCell.Value = "L" And myCell2.Value <> "UN3363" Then
      mycell.Offset(, 3).Formula = "=IFERROR(IF(OR(AJ2<AL2*0.95,AJ2>AL2*1.05),""Outside Limits"",""Within Limits""),""Error"")"      
  Else 
      mycell.Offset(, 3).Formula = "=IF(AL2=AJ2,TRUE,FALSE)"
  End If

Next