View Full Version : IF-Then-Else in VBA help needed

09-23-2007, 07:55 AM
Dear VBAX experts,

I believe this is simple for you but it is really tough for me. I am able to do it with If-Then-Else in formula but I would also like to have a resolution in VB Procedure. The answer is in the last column but like it to be in VB.

Hope to get some help from VBAX forum. many thanks in advance ;-)

Bob Phillips
09-23-2007, 08:20 AM
With ActiveCell
If IsError(.Offset(0, -7).Value) Then
.Value = Range("A" & .Row).Value & " AddDate_" & Format(Range("I" & .Row).Value, "m/d/yy ") & _
Range("AE" & .Row).Value & " Est.Closure_#N/A DaysAge_" & Range("AX" & .Row).Value
.Value = Range("A" & .Row).Value & " AddDate_" & Format(Range("I" & .Row).Value, "m/d/yy ") & _
Range("AE" & .Row).Value & " Est.Closure_" & Format(Range("AS" & .Row).Value, "m/d/yy") & " DaysAge_" & Range("AX" & .Row).Value
End If
End With

09-23-2007, 11:24 AM
Dear xld,

I got a run-time error '13' Type Mismatch where AS3, AS8 has #N/A. Isnt it suppose to work as you are following the logic in my XL formula?

Bob Phillips
09-23-2007, 12:06 PM
Is that slapping my wrists?

09-23-2007, 12:56 PM
Dear xld,

sorry about bothering you. I had tried for about an hour +, cant seem to get the VB right for the #N/A (sigh..). I got to go to sleep now, 3.56 AM. If you have time, can you help to look at that. Thanks in advance.

09-23-2007, 01:09 PM
Dear xld,

My English is no good. sometimes I really donot know how to ask an appropriate yet a simple question. In this case, by no means I'm to be rude to a great helper like you who help me so much in past few cases. My great gratitude to you all the times. Please accept my sincere apology.

09-23-2007, 02:31 PM
If IsError(.Offset(0, -6).Value) Then I notice that in your formula solution, if there is an error, the last term is repeated.
XLD's code does not do this. Which is correct?

09-23-2007, 02:41 PM
Thanks sir. apology for my error. shall be more careful next time.

Bob Phillips
09-23-2007, 02:59 PM
So are you sorted now, or do you still have a problem?

09-23-2007, 03:08 PM
Dear xld, it is done and worked perfectly. Thank you very much for your help.