Code instead of formula?

[deep6]

Hello All,

Could someone explain to me how I would write VBA code that would accomplish the same thing as the following formula?

=IF(AND(ISTEXT(A2),H2=1),"Completed",IF(AND(ISTEXT(A2),H2=0),"In Progress",""))

Ideally, this would return "Completed", "In Progress", or just be left blank in Column W, and would need to address rows 2 through 65536.

 

[tactps]

Sub macro()
Dim cell As Range
Range("w2:w" & [a65536].End(xlUp).Row).Select
Selection.ClearContents
For Each cell In Selection
If Len(cell.Offset(0, -22)) > 0 Then 'this may need to change
If cell.Offset(0, -15).Value = 1 Then cell.Formula = "Completed"
If cell.Offset(0, -15).Value = 0 And Len(cell.Offset(0, -15).Text) > 0 Then cell.Formula = "In progress"
End If
Next cell
MsgBox ("Done")
End Sub

 

[deep6]

Thank you, tactps!

Your suggestion works perfectly. I'd like to learn something here, so could I trouble you to explain the following portion of your code? I can follow the rest of it, but the use of "Len" is a little "fuzzy" to me.

Len(cell.Offset(0, -15).Text)

Thank you again!

 

[tactps]

This checks how many characters are in the output in the cell.

Therefore if the cell is blank, it will have 0 characters. If it is not blank, it will be >0.

This does not look to see whether it is a number or whether it is text, only that the result shown in that cell is not "".

So:
If cell.Offset(0, -15).Value = 0 And Len(cell.Offset(0, -15).Text) > 0
If you had a blank cell, its value would be zero, so I am checking whether it is zero, plus whether it not blank.

 

[deep6]

Thank you for the explanation. Now the wheels are spinning in my head about how I could have used that approach to some other things that I've recently worked on.

 

[tactps]

Spinning head is good - it either means you're thinking or drinking.

Good either way (lol)