# Excel Equation converted to an excel macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### gundr1kr

I have an equation that I need help with putting into an excel Macro. The equation is as follows:
=IF(AND(ISBLANK(E2),ISNA(F2)),"XXXXXX",IF(AND(ISBLANK(E2),ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNA(F2)),E2,IF(AND(OR(ISBLANK(E2)="FALSE",E2<>"000000",E2<>"111111",E2<>"999999",E2<>"IOIOIO",E2<>"XXXXXX"),OR(ISNUMBER(F2),ISNA(F2))),E2,"No Criteria Met")))))

I need this put into a macro as either an if then else statement or a case statement. If anyone could help me out i would appreciate it

#### Zack Barresse

Not sure if this will work for you or not, untested...

Code:
``````dim e as range, f as range, c as range
set e = range("E2"): set f = range("F2"): set c = activecell
if e = "" and worksheetfunction.isna(f) then
c.value = "XXXXXX"
elseif e = "" and isnumeric(f.value) then
c.value = "AS SIGN LAN ID"
elseif e = "XXXXXX" and isnumeric(f.value) then
c.value = "ASSIGN LAN ID"
elseif e = "XXXXXX" and worksheetfunction.isna(f) then
c.value = e.value
elseif (e <> "" or e <> "0000 00" or e <> "111111" or e <> "999999" or e <> "IOIOIO" or e <> "XXXXXX") and (isnumeric(f) or worksheetfunction.isna(f)) then
c.value = e.value
else
c.value = "No Criteria Met"
end if``````
HTH

#### gundr1kr

Thanks for the help, but I have another quick question.

I want the range to change. I have created a loop to do it for more that E2 and F2 in my workbook. But when I do it, it will just use the information from E2 and F2. I want the result of G3 to be from E3 and F3. I want it to do it for as many numbers that are in the columns E and F. Can you please help?

Range("G2").Select
Dim i As Integer
Dim introwcount As Integer
introwcount = Range("B1").CurrentRegion.Rows.Count - 1
For i = 1 To introwcount
Dim e As Range, f As Range, c As Range
Set e = Range("E2"): Set f = Range("F2"): Set c = ActiveCell
If e = "" And WorksheetFunction.IsNA(f) Then
c.Value = "XXXXXX"
ElseIf e = "" And IsNumeric(f.Value) Then
c.Value = "ASSIGN LAN ID"
ElseIf e = "XXXXXX" And IsNumeric(f.Value) Then
c.Value = "ASSIGN LAN ID"
ElseIf e = "XXXXXX" And WorksheetFunction.IsNA(f) Then
c.Value = e.Value
ElseIf (e <> "" Or e <> "000000" Or e <> "111111" Or e <> "999999" Or e <> "IOIOIO" Or e <> "XXXXXX") And (IsNumeric(f) Or WorksheetFunction.IsNA(f)) Then
c.Value = e.Value
Else
c.Value = "No Criteria Met"
End If
ActiveCell.Offset(1, 0).Select
Next i

#### Zack Barresse

Maybe you could try this, without the selecting...

Code:
``````    Dim i As Long, intRowCount As Long
For i = 1 To Cells(Rows.Count, 5).End(xlUp).Row 'looks at column E
If Cells(i, 5) = "" And WorksheetFunction.IsNA(Cells(i, 6)) Then
Cells(i, 7).Value = "XXXXXX"
ElseIf Cells(i, 5) = "" And IsNumeric(Cells(i, 6).Value) Then
Cells(i, 7).Value = "ASSIGN LAN ID"
ElseIf Cells(i, 5) = "XXXXXX" And IsNumeric(fcells(i, 6).Value) Then
Cells(i, 7).Value = "ASSIGN LAN ID"
ElseIf Cells(i, 5) = "XXXXXX" And WorksheetFunction.IsNA(Cells(i, 6)) Then
Cells(i, 7).Value = e.Value
ElseIf (Cells(i, 5) <> "" Or Cells(i, 5) <> "000000" Or Cells(i, 5) <> "111111" Or Cells(i, 5) <> "999999" Or Cells(i, 5) <> _
"IOIOIO" Or Cells(i, 5) <> "XXXXXX") And (IsNumeric(Cells(i, 6)) Or WorksheetFunction.IsNA(Cells(i, 6))) Then
Cells(i, 7).Value = e.Value
Else
Cells(i, 7).Value = "No Criteria Met"
End If
Next i``````
The last row (end of the loop) is determined by the last cell value in column E.

HTH

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.