Checking for multiple conditions

[NWE]

Hello,

I am writing some code that checks for two conditions using an IF statement. I am wondering is there a better way to structure this?



Sub Isolate_Errors()
Worksheets("Merged Differences").Activate
For Each c In Worksheets("Merged Differences").range("A2:A10000")


If Worksheets("Merged Differences").range("A1:A10000").Value Is Not Null Then
For Each c In Worksheets("Merged Differences").range("W2:W10000")
If Worksheets("Merged Differences").range("W2:W10000").Value Is Null Then
'Create a list of line items to review.
columnletter = Split(Cells(1, iCol).Address, "$")(1)
Else
'There is no error do nothing.

'Make a list of line items to review
Worksheets("Generalized Report").Activate
Worksheets("Generalized Report").range("F" & t) = "$" & columnletter & "$" & iRow
End If
Next c
Next c
End Sub


The conditions are If a cell in column A on "Merged differences" is not null and a cell in column W ("Merged Differences") is null, I want to isolate the value of the corresponding cell in column B on a list in another worksheet ("Generalized Report").

[rothstein]

Does this function do what you want (it places the output in Column A, shown in blue, of the "Generalized Report" sheet, so you might need to change that)...



Sub Isolate_Errorsx() Dim Sht As String, Arr As Variant
Sht = "'" & Sheets("Merged Differences").Name & "'!"
With Sheets("Merged Differences").Range("A1", Sheets("Merged Differences").Cells(Rows.Count, "A").End(xlUp))
Sheets("Generalized Report").Range("A1:A" & .Rows.Count).Value = Evaluate("IF((" & Sht & .Address & "<>"""")*(" & Sht & .Offset(, 22).Address & "="""")," & Sht & .Offset(, 1).Address & ","""")")
End With
Sheets("Generalized Report").Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

[NWE]

Actually it did. I am just trying to better understand the arguments you present so that'll take a moment. And yes by changing the output to "F" it did exactly what I needed it to do. Thank you. Was I making my code too complicated?

[rothstein]

Was I making my code too complicated?
No (although you appear to have some syntax problems in the code you posted), most (almost all?) programmers will fall back on loops when multiple cells are involved... I just tend of have a different approach to programming, that is all (I have been programming since 1981, so I have developed my own programming "style").

[snb]

or


Sub M_snb()
With Sheet1.Range("A2:A10000")
.Name = "snb_01"
.Offset(, 22).Name = "snb_02"
End With

sn = [if(snb_01="",if(snb_02="","",address(row(snb_01),column(snb_01))),"")]
Sheet1.Cells(1, 6).Resize(UBound(sn)) = sn
End Sub

[NWE]

I noticed it when I ran it through the debugger (I am still newish, learning more complex things over time). And nice work. Last question, If I wanted the list to start on "F4" on Reports, should this approach work?


worksheets("").Range("F4") = Sheets("Generalized Report").Columns("F").SpecialCells(xlBlanks).Cells.Delete

[rothstein]

I noticed it when I ran it through the debugger (I am still newish, learning more complex things over time). And nice work. Last question, If I wanted the list to start on "F4" on Reports, should this approach work?


worksheets("").Range("F4") = Sheets("Generalized Report").Columns("F").SpecialCells(xlBlanks).Cells.Delete

I would modify my code this way to do what you are asking for...



Sub Isolate_Errorsx() Dim Sht As String, NewRng As Range
Sht = "'" & Sheets("Merged Differences").Name & "'!"
With Sheets("Merged Differences").Range("A1", Sheets("Merged Differences").Cells(Rows.Count, "A").End(xlUp))
Set NewRng = Sheets("Generalized Report").Range("F4:F" & 3 + .Rows.Count)
NewRng.Value = Evaluate("IF((" & Sht & .Address & "<>"""")*(" & Sht & .Offset(, 22).Address & "="""")," & Sht & .Offset(, 1).Address & ","""")")
End With
NewRng.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

[NWE]

@roth-I actually switched it up to add it to another page for a more detailed evaluation.

@snb-What are the definitions of the spreadsheet..essentially if we are using that code, how do you define where it is going?

[NWE]

Hi

Looking over your code, I am hoping to better understand what this part means, particularly the "" symbols


Sheets("Generalized Report").Range("A1:A" & .Rows.Count).Value = Evaluate("IF((" & Sht & .Address & "<>"""")*(" & Sht & .Offset(, 22).Address & "="""")," & Sht & .Offset(, 1).Address & ","""")"

[大灰狼1976]

You can try this:

Debug.Print "IF((" & Sht & .Address & "<>"""")*(" & Sht & .Offset(, 22).Address & "="""")," & Sht & .Offset(, 1).Address & ","""")"
It's a worksheet function.