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

Thread Starter
Joined
Jun 26, 2006
Messages
19
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
 
Joined
Jul 25, 2004
Messages
5,458
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

Thread Starter
Joined
Jun 26, 2006
Messages
19
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
 
Joined
Jul 25, 2004
Messages
5,458
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top