Trying to do a For each and IFAnd in VBA code

[bino1121]

Below is the code I am currently attempting to write. The background is I have a list of orders with other information A:J ( don't want to make a table if it can be avoided) I need the code to evaluate D2 & lr = 10 AND F2 & lr ="" (blank) then if that statement is true put "ON HOLD" into cell I2. I need this to evaluate for a variable range but then loop through the if condition to check all the rows in that variable range so the next cell to evaluate would be D3 AND F3 spit out "ON HOLD" in I3 if that is true otherwise leave blank if false .

Sub ForEach_IfAnd()

Dim cell as Range

lr = Sheets(1).Cells.Find("*", Cells(1,1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

For each cell in Sheets(1).Range("?????" & lr)

If sheets(1).Range("D2") = 10 And Sheets(1).Range("F2") = "" _
Then
Range("I2") = "ON HOLD"
Else
Range("I2") = ""
End If

Next cell

End Sub

 

[Aniruddah Alam]

Thanks bino1121 for your post. From your code, it's not clear to me what you want to do with the variable lr. Are you trying to locate the last row of your dataset by manually inserting a "*" on the last row and then finding its row number? If that is the case, then I am giving an improved code that will automatically calculate the last used rows without the need for manual insertion of "*". I am also assuming that your dataset starts from 2nd row (A2). So our loop will begin at r=2 to the last used row in column A. Here is the full code:

Sub List_Order()

Dim r As Integer
Dim last_used_row As Double
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1") 'change the Sheet name accordingly

last_used_row = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'last used row of column A

For r = 2 To last_used_row  'starting the loop from 2nd row
    If ws.Range("D" & r).Value = 10 And ws.Range("F" & r).Value = "" Then
        ws.Range("I" & r).Value = "ON HOLD"
    Else
        ws.Range("I" & r).Value = ""
    End If

Next r

End Sub

Give the code a try and let us know if everything works as expected. If you need any help or have any questions, don't hesitate to reach out to us.

Regards
Aniruddah
Team Exceldemy

 

[bino1121]

Thanks bino1121 for your post. From your code, it's not clear to me what you want to do with the variable lr. Are you trying to locate the last row of your dataset by manually inserting a "*" on the last row and then finding its row number? If that is the case, then I am giving an improved code that will automatically calculate the last used rows without the need for manual insertion of "*". I am also assuming that your dataset starts from 2nd row (A2). So our loop will begin at r=2 to the last used row in column A. Here is the full code:

Sub List_Order()

Dim r As Integer
Dim last_used_row As Double
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1") 'change the Sheet name accordingly

last_used_row = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'last used row of column A

For r = 2 To last_used_row  'starting the loop from 2nd row
    If ws.Range("D" & r).Value = 10 And ws.Range("F" & r).Value = "" Then
        ws.Range("I" & r).Value = "ON HOLD"
    Else
        ws.Range("I" & r).Value = ""
    End If

Next r

End Sub

Give the code a try and let us know if everything works as expected. If you need any help or have any questions, don't hesitate to reach out to us.

Regards
Aniruddah
Team Exceldemy

seems like it is working good thank you so much! could you explain a couple things about it? How come last row is dim as double? and how did you come up with the Idea for r=2 or could you explain the r=2 part and how it works I have not used used the for loop yet and I could not figure out how to get it to work I kept getting mismatch errors and the code would not loop through to the last row just to the first selection I put in

 

[Aniruddah Alam]

seems like it is working good thank you so much! could you explain a couple things about it? How come last row is dim as double? and how did you come up with the Idea for r=2 or could you explain the r=2 part and how it works I have not used used the for loop yet and I could not figure out how to get it to work I kept getting mismatch errors and the code would not loop through to the last row just to the first selection I put in

Dear Bino,
1. I actually made a mistake in declaring the variables 'r' and 'last_used_row' as Integer and Double, respectively, instead of Long. As the Integer data type can only hold values ranging from -32768 to 32768, it is safer to use Long type when there are chances of exceeding the range, as it can hold much larger integer values (-2,147,483,648 to 2,147,483,648). Therefore, I kindly request you to correct the variable types to Long.

2. In your initial post, you mentioned that the code requires the evaluation of D2. Therefore, I assumed that the loop should begin from the second row of the worksheet since there might be header values in the first row. However, if this is not the case, you may change the loop to start from the row number where your data actually begins.

3. Regarding the For Next loop:

For r = 2 To last_used_row
.
.
Next r

Here, the For Next loop will iterate for each integer value of r from 2 to last_used_row with step 1 and execute the subsequent command line before Next r (To learn more, you can read this comprehensive article on For Next loop). However, in your given code you have used the For Each loop that works slightly differently from the For Next loop, hence it was not appropriate to use in your case.

Hope you got the answers to your questions.

Best Regards
Aniruddah