Using Vlookup on other Workbook whichs path is stored in a Cell

Editing a Sway using the website is not supported on mobile phones. If you want to edit on the phone, you need to download the Sway mobile app (of which there is currently only an iOS version). Otherwise, you'll need to continue editing on a tablet or
desktop.

Hope this helps!

Jason

Many thanks for the suggestions. They did not help, though. I deleted the template Book.xltx in the start-up folder and made sure there is none anywhere on the C drive. After the close / open /close / open procedure I am back to A1 style.

There is no PERSONAL.XLSB on my C drive. I checked also the spelling in lower case. But when I open an existing workbook directly in the file explorer, the I do get actually two books: the one I wanted and an empty Book1.xlsx. Is this the other workbook
your mention? Is it associated with the root cause? I would be grateful for a sound solution.

For the time being I implemented a Autpen VBA procedure, which is setting the R1C1 style at opening of the workbook. But that is a bit clumsy and I would appreciate a more professional solution.

Hi J

Here is the formula that would count the number of ; + 1 in a cell, which if all the persons were separated by a ; would work

(Pleas Note, you will need to replace A1 with the appropriate cell reference)

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + 1

However from your screenshot, sometimes a ; is used and other times a , is used to separate the persons names, and in that case, this is the formula you need

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + LEN(A1)-LEN(SUBSTITUTE(A1,",","")) + 1

Weil die Dateiendung fehlt, das Workbooks Objekt braucht den kompletten Namen.

Der Code unten könnte hilfreich sein. Und Du solltest zuerst das Objekt auf NOTHING prüfen, sonst gibt's den nächsten Fehler in Deinem Code bei
.Row

Andreas.



Function GetWorkBook(ByVal WorkBookName As String) As Workbook

'Return the workbook that name begins with or is WorkBookName, Nothing if not open

Dim fso As Object 'FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

'Path given?

If Len(fso.GetParentFolderName(WorkBookName)) > 0 Then

'Compare the full path of each open workbook

For Each GetWorkBook In Workbooks

If StrComp(GetWorkBook.FullName, WorkBookName, vbTextCompare) = 0 Then

Exit Function

End If

Next

ElseIf InStrRev(WorkBookName, ".") > 0 Then

'We must exact match if an extension is given

On Error GoTo ExitPoint

Set GetWorkBook = Workbooks(WorkBookName)

Else

'Without an extension it can be a new file too

On Error GoTo SearchIt

Set GetWorkBook = Workbooks(WorkBookName)

Exit Function

SearchIt:

On Error GoTo ExitPoint

For Each GetWorkBook In Workbooks

If StrComp(fso.GetBaseName(GetWorkBook.Name), WorkBookName, vbTextCompare) = 0 Then

Exit Function

End If

Next

End If

ExitPoint:

End Function

In my mind not very helpful.

Would have preferred a light-gray outline of the table (like in past versions), which does not get printed. In that way one could easily see where the table is located and where the different cells are. It is still possbile today, when activating the "Textbegrenzungen",
but then you get also a grey line on every text line of the document, which does not at all contribute to a clear overview of the document.

Thx anyhow for your answer.