mardi 27 avril 2021

How to use CONCATENATE inside an IF ELSE Statement in Excel Macro [duplicate]

I am attempting to create a new column ("AF") to bring in an existing Internal Part Number from another column ("Z") if the part number is already listed. But if that field is set to #N/A, I want to concatenate the word "CST" with another column ("J") Which includes the Customer Part Number to create the value in my new column. Here is what I tried that does not work:

Lastrow = Worksheets("Current").UsedRange.Rows.Count
Range("AF2").Select
For counter = 2 To Lastrow
Dim PartLine As String
Dim PartNum As String
Dim CustPart As String
    PartLine = "AF" & counter
    PartNum = Cells(counter, "Z").Value
    CustPart = Cells(counter, "J").Value
If (Cells(counter, "Z")) <> "#N/A" Then
Range(PartLine).Value = PartNum
ElseIf (Cells(counter, "Z")) = "#N/A" Then
Range(PartLine).Select
ActiveCell.Formula = "=TEXT(CONCATENATE(""WOOD"",CustPart),0)"
End If
Next counter

Thanks for any help you can offer!

Aucun commentaire:

Enregistrer un commentaire