0

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!

Jayson Montgomery
  • 1
  • Should be `"=TEXT(CONCATENATE(""WOOD""," & CustPart & "),0)"` but I have no idea why you're using `0` as the second argument of `TEXT` when the first argument is `WOODsomething`. Did you mean to use `TEXT` *inside* `CONCATENATE`? – BigBen Apr 28 '21 at 02:24
  • Also note that `#N/A` is an error value, not the string `"#N/A"`, and there are [different ways](https://stackoverflow.com/questions/5143402/checking-for-n-a-in-excel-cell-from-vba-code) to check if the cell contains said error. – BigBen Apr 28 '21 at 02:26
  • Thanks! That's on me. I am re-using this from another part of the Macro for a number field and left that in there. Just stumbling my way through it. lol As for the #N/A, earlier in the Macro I copied the formulas in this column and Pasted Values. It doesn't seem to like that either. I will review the link you provided. – Jayson Montgomery Apr 28 '21 at 02:30
  • The easiest way is probably to use `Appllication.IsNA`. Or if you just want to check if it's an error and don't care about what type, then `IsError` – BigBen Apr 28 '21 at 02:36
  • Thanks again. I am getting a runtime error trying to create the Concatenate formula as you described. What am I doing wrong? Also, is there a way for me to buy you a beer for the help? – Jayson Montgomery Apr 28 '21 at 02:43
  • Probably easier to just do `"=CONCATENATE(""WOOD"",J" & counter & ")"`. Eh, helping is enough of a reward in itself, but I appreciate the thought. – BigBen Apr 28 '21 at 02:49
  • Awesome! Thanks so much for the help! Here is what I ended up with and it works like a champ: Lastrow = Worksheets("Current").UsedRange.Rows.Count Range("AF2").Select For counter = 2 To Lastrow Dim PartLine As String Dim CustPart As String PartLine = "AF" & counter CustPart = Cells(counter, "J").Value If IsError(Cells(counter, "Z")) Then Range(PartLine).Select ActiveCell.Formula = "=CONCATENATE(""WOOD"",J" & counter & ")" Else Range(PartLine).Value = Cells(counter, "Z") – Jayson Montgomery Apr 28 '21 at 04:14

0 Answers0