If with Loop

[KishorKK]

Hi Friends,

when i am executing this one i'm getting error,

Sub loops_if2()

Dim marks As Integer

    For marks = 1 To Range("myloopcount").Value

    If Cells(marks + 1, "b").Value >= 40 Then

    Cells(marks + 1, "c").Value = "Pass"

    Else

    Cells(marks + 1, "c").Value = "Fail"

End If

Next marks

End Sub

can some one explain me

 

[PCosta87]

Hi @KishorKK

Tried replicating here and it works fine.
Could you please upload sample file?

Thanks

 

[KishorKK]

sure

 

[KishorKK]

check in Sub loops_if2()

for me i am getting error. could you pls check it

 

[KishorKK]

then how to write suggest

 

[PCosta87]

then how to write suggest

Was going to edit previous post but opted for deleting it...

Since you are looping until last row maybe something like

Sub loops_if2()

Dim marks As Integer


    For marks = 1 To Columns("B").Cells(Rows.Count).End(xlUp).Row - 1
   
    If Cells(marks + 1, "b").Value >= 40 Then

    Cells(marks + 1, "c").Value = "Pass"
   
    'MsgBox Cells(marks + 1, "c").Value & " Pass," & " and his marks is " & Cells(marks + 1, "b").Value

    Else

    Cells(marks + 1, "c").Value = "Fail"

End If

Next marks

End Sub

 

[KishorKK]

this one i got , so we cannot write


I have few questions here why we need to write

marks+1, i am not getting this "Range("myloopcount").Value"

 

[PCosta87]

this one i got , so we cannot write


I have few questions here why we need to write

marks+1, i am not getting this "Range("myloopcount").Value"

Well, I would write it differently but in any case...

"marks + 1" because you have "marks" starting at 1 and the first row you want to evaluate is 2 so 1+1 (you could just replace with "For marks = 2 to Columns("B").Cells(Rows.Count).End(xlUp).Row" and use just "marks" instead of "marks + 1":

Sub loops_if2()

Dim marks As Integer


    For marks = 2 To Columns("B").Cells(Rows.Count).End(xlUp).Row
 
    If Cells(marks, "b").Value >= 40 Then

    Cells(marks, "c").Value = "Pass"
 
    'MsgBox Cells(marks, "c").Value & " Pass," & " and his marks is " & Cells(marks, "b").Value

    Else

    Cells(marks, "c").Value = "Fail"

End If

Next marks

End Sub

"Range("myloopcount").Value" refers to a named range called "myloopcount" where you would have the number of the last row of the range you are evaluating... in this case it would be "8" (probably a formula to determine this, making it dynamic). Since you don't have any range named "myloopcount" you can't use it. Other solution would be to name some cell "myloopcount" and use a count formula there to return the number of the last row of your range.

 

[KishorKK]

Ok, thanks a lot bro

a good explanation

 

[PCosta87]

Ok, thanks a lot bro

a good explanation

Don't mention it