Possible to create an IF THEN macro in Excel? 1

[ScottNeth]

I have a consistant error on 280 spreadsheets that I'm really hoping I can write a macro for instead of doing them each by hand. I have a part number and a serial number that both need to be entered. The part numbers are consistant with the serial numbers, so I'm trying to find a way to write a script that says something similar to "IF (L3)=3EC15195 THEN (M3)=VACTEHWBAA" (there are six of these statements I would need to create).

 

[B827]

I would use a Function

Put this in a module with the appropriate changes

Function SelectType(CellRef As String) As String
On Error GoTo Err_SelectType
Select Case CellRef
Case "3EC15195"
SelectType = "VACTEHWBAA"
Case "3EC15196"
SelectType = "VACTEHWBAB"
Case "3EC15197"
SelectType = "VACTEHWBAC"
Case "3EC15198"
SelectType = "VACTEHWBAD"
Case "3EC15199"
SelectType = "VACTEHWBAE"
Case Else
SelectType = "No reference"
End Select


Exit_SelectType:
Exit Function

Err_SelectType:
SelectType = "Data error"
Resume Exit_SelectType
End Function

Then got the insert Function as normal and select User Defined from the list of options. You should see SelectType so select that as normal and it will work like a standard function. Sandy

 

[dsi]

Do you want these as a custom function or do you want the macro to go to each sheet and output the associated serial number depending on the part number? You were not real clear on what you wanted to do.

As far as coding, you should probably use the Select Case statement instead of a bunch of If statements. This code will go through each worksheet in the workbook, grab the part nummber (L3) and put the correct serial number in cell M#.

Sub UpdateSerialNumbers()
    Dim ws As Worksheet
    Dim sPartNo As String
    Dim sSerialNo As String
    
    For Each ws In ActiveWorkbook.Worksheets
        sPartNo = ws.Range("L3").Text
        If sPartNo <> &quot;&quot; Then
            Select Case sPartNo
                Case &quot;3EC15195&quot;: sSerialNo = &quot;VACTEHWBAA&quot;
                Case &quot;?????&quot;: sSerialNo = &quot;????&quot;
                'and so on
                Case Else
                    sSerialNo = &quot;UNKNOWN&quot;
            End Select
            ws.Range(&quot;M3&quot;) = sSerialNo
        End If
    Next ws
    
    MsgBox &quot;Done&quot;
End Sub

"IF (L3)=3EC15195 THEN (M3)=VACTEHWBAA"

 

[euskadi]

ScottNeth,

Sometimes I like to answer questions by not answering the question asked...

You don't necessarily need to do VBA in order to achieve what you're looking for. You can easily do this with a lookup function:

Create table (2 x 6) containing your results. Name this range "my_table" or whatever. Then, in the cell where you'd like your corrected SN, evaluating the part number in cell A1 enter this function:

=IF(ISNA(VLOOKUP(A1,my_table,2,FALSE)),"No reference",VLOOKUP(A1,my_table,2,FALSE))

Then just do a fill down for the 280 parts you need to check. No VBA required.

 

[dsi]

euskadi: that's 280 spreadsheets

 

[euskadi]

Oh well in that case... do some VBA. ;-)

 

[dsi]

Yeah, but your VLOOKUP suggestion beats hardcoding the values in my example.

 

[B827]

You have to be a little bit wary of VLOOKUP because it can be quite easy to set up to pick up the nearest value when no exact match is found. I think it's one of these things were everyone has their own preferred method. Personally I preferred code to nested IFs. Sandy

 

[dsi]

B827: the last argument of the VLOOKUP controls whether it requires an exact match or the nearest value. Anyway, you are correct, everyone has their own preferred method.