1

I am relatively new to VBA and I made a macro that has been working on my computer, but when I sent it to someone else, it errors out on this line:

If WorksheetFunction.Concat(ActiveSheet.Range("a2:n2").Value) <> "UnitOper UnitDeptProjectDateLine DescrAmountAccountProductJournal IDLine #RefStatusAffiliate" Then

I will include the entire macro below but I am trying to open a file using getopenfilename and make sure that the file has the correct content. Even when I sent the macro to people using excel 2016, it still doesnt work and errors out.

I tried something new and now it says type mismatch on the same line.

 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual


 Dim myFileName As Variant
 Dim sWorkbook, i As String
 Dim Macrotool As String
 Dim Countlines As Long




Macrotool = ActiveWorkbook.Name
myFileName = Application.GetOpenFilename

If myFileName <> False Then
Workbooks.Open (myFileName)
sWorkbook = ActiveWorkbook.Name
ActiveSheet.Range("O2") = "=concat(a2:n2)"



If ActiveSheet.Range("o2").Value <> "UnitOper UnitDeptProjectDateLine DescrAmountAccountProductJournal IDLine #RefStatusAffiliate" Then

    Workbooks(sWorkbook).Close False
    i = MsgBox("It looks like you are uploading the wrong file." & vbLf & "Please make sure you are using one of the following queries:" & vbLf & "" & vbLf & "'ACCOUNT_ACTIVITY_ANY_CF'" & vbLf & "'ACCOUNT_ACTIVITY_BU'" & vbLf & "'ACCOUNT_ACTIVITY_BU_OP'" & vbLf & "'ACCOUNT_ACTIVITY_BU_OP_DEP'" & vbLf & "'ACCOUNT_ACTIVITY_BU_OP_DEP_PRJ'", vbOKOnly, "Wrong Filed Used")
    Application.ScreenUpdating = True
    Workbooks(Macrotool).Activate
    Sheets("Home").Select
    GoTo OnError
End If
  • I stopped scrolling through your code for fear of developing RSI ;) – CallumDA Nov 03 '17 at 14:19
  • Not directly related to that specific error, but ...[nonetheless obligatory read](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) <~ you *need* to read this, carefully. – Mathieu Guindon Nov 03 '17 at 14:31
  • Avoid using `ActiveSomething` and precisely define your variables. Check **[this answer](https://stackoverflow.com/a/10717999/2687063)** for a detailed explanation. – simpLE MAn Nov 03 '17 at 14:31
  • Thanks for the suggestions! –  Nov 03 '17 at 14:37

1 Answers1

1

You are using fancy Excel version of 2016 and the "others" are using Excel 2013 or lower? Thus, they do not have WorksheetFunction.Concat and they get error 438.

The one that told you that they are using Excel 2016 and still get an error there are either not sure which version they are using or are getting error somewhere else.

Worst case scenario - go to those people with Excel 2016 and make sure that they are having exactly the same worksheet with exactly the same error on that line. Make sure that you check the ActiveSheet name as well.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    FWIW If the error is exactly where OP claims it is, this answer isn't a guess, it *is* the answer. – Mathieu Guindon Nov 03 '17 at 14:33
  • @Mat'sMug - Thanks! :) I am using 2013 and I got the exactly same error, using `WorksheetFunction.Concat()` – Vityata Nov 03 '17 at 14:34
  • I'm pretty sure there's an SO Q&A somewhere that has a UDF implementation of the `Concat` function that can be used in place of the Excel 2016 function. – Mathieu Guindon Nov 03 '17 at 14:38
  • Do only some versions of Excel 2016 have WorksheetFunction.Concat? –  Nov 03 '17 at 14:39
  • @jmartin0807 - that would be an interesting "feature" in Excel. So I guess, no. Every version should have it. – Vityata Nov 03 '17 at 14:41
  • @Mat'sMug - this one - https://stackoverflow.com/questions/36572697/udf-to-concatenate-values – Vityata Nov 03 '17 at 14:42
  • 1
    @jmartin0807 I don't know that but I strongly suspect the answer is "nope". In any case if you mean to fix your problem you'll need to concatenate the cell values yourself. See ^^^^ this post ^^^^. – Mathieu Guindon Nov 03 '17 at 14:42
  • I tried to fix it in a different way but now it is saying Type Mismatch. See updated question :) Also, it still works for me when I run it, but my coworker who has office 2016 now has a type mismatch error on that same line. –  Nov 03 '17 at 15:07
  • @Mat'sMug forgot to tag you –  Nov 03 '17 at 15:17
  • 1
    @jmartin0807 - try some simple debugging. Like `msgbox ActiveSheet.Range("o2").Value` The `O2` should be an error, in order to give `Type Mismatch`. And the problem should be somewhere in the active sheets. E.g., the `ActiveSheet` for you is not the `ActiveSheet` for your colleague. – Vityata Nov 03 '17 at 15:25
  • 1
    @jmartin0807 note that "chameleon questions" that change with every helpful answer are frowned upon. SO isn't a debugging service, the Q&A's primarily help people searching for solutions to similar problems. That type mismatch is a classic, already answered multiple times over. – Mathieu Guindon Nov 03 '17 at 15:34