PROBLEM: Jump To A Cell Reference Within An Excel Formula

Danke für den Versuch, aber das löst das Problem leider immer noch nicht

http://www.iqaccountingsolutions.co...dent-cells-in-excel-with-a-keyboard-shortcut/

Hier wird die Funktionsweise der Tastaturkombination schön gezeigt... Gibt es das auch in der deutschen Version?

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

Nee, das geht so nicht. A) muss jedes " verdoppelt werden, weil " ist in VBA ein reserviertes Zeichen und B) musst Du FormulaLocal statt der FormulaR1C1Local nehmen.

Und mit ActiveCell.Offset(0, 10) würde ich niemals arbeiten, weil dann landet die Formel je nach aktueller Zelle irgendwo.

Schau Dir das Beispiel unten mal an und versuche es nach dem Schema nachzubauen.

Andreas.

Sub Example_HowToCreateAFormulaWithVBA()

 'Copy the formula from Excel as is

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&"Whatever"

 'double each " sign inside:

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever""

 'surround the whole formula with " signs

 ' "=INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever"""

 'Replace the cell references with a "placeholder" (any unique string that you like and doesn't exists elsewhere)

 ' "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 'and assign to a string:

 Dim MyDefFormula As String

 MyDefFormula = "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 

 'Now create the Range objects and refer to the real cells

 Dim a As Range, b As Range, c As Range

 Set a = Range("A1") 'A single cell

 Set b = Range("B2:B50") 'Multiple cells

 Set c = Range("C2", Range("C" & Rows.Count).End(xlUp)) 'From C2 to last used cell in column C

 'Note:

 ' You can refer to cells in an other sheet (note the dot in front of Range!):

 'With Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 ' Or other already opened files:

 'With Workbooks("This.xlsx").Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 

 'Make a copy of your default formula into a work string

 Dim MyFormula As String

 MyFormula = MyDefFormula

 'Replace the placeholders with the cell references

 MyFormula = Replace(MyFormula, "#A#", a.Address)

 'Note:

 ' The first arguments of Range.Address controls where the $ appears in the formula

 MyFormula = Replace(MyFormula, "#B#", b.Address(0, 0))

 'Note:

 ' The part 'External:=True' is necessary if you refer to other sheets or files

 ' But it works also if the reference is in the active sheet

 MyFormula = Replace(MyFormula, "#C#", c.Address(External:=True))

 

 'Write the formula into the cell

 Range("A2").Formula = MyFormula

 

 'Note:

 ' If your locale language is not English you can use the FormulaLocal property instead

 ' But in this case the code works only on PCs with your locale settings

 'Range("A2").FormulaLocal = MyFormula

 

 'Note:

 ' If you want to create array formulas use the FormulaArray property

 ' But in this case use must use English formulas, a FormulaArrayLocale doesn't exists

 'Range("A2:A10").FormulaArray = MyFormula

End Sub

Ich habe in meiner Excel Datei Verlinkungen zu einer Quelldatei eingebaut. Leider ist es mir bisher nur gelungen anhand der Registerkarte
-->Daten

-->Verbindungen

-->Verknüpfung bearbeiten

--> Quelle öffnen

die Quelldatei zu öffnen.

Hmm, die Verlinkung wäre dann eine Formel ala

='Z:\[DateiA.xlsx]Tabelle1'!$A$1

oder

=[DateiA.xlsx]Tabelle1!$A$1

wenn die Datei offen wäre oder

=Tabelle2!N20

oder

=G9

in dieser Datei?

Und wenn die Datei noch geschlossen ist dann automatisch öffnen?

Das läßt sich mit einem Makro machen. Kopier den Code in ein reguläres Modul Deiner PERSONAL.XLSB

Andreas.

Sub JumpToLink()

'Jumps to a link source from cell formula, opens the file if not already open

Dim Links, Item

Dim Formula As String, Temp As String

Dim Wb As Workbook

'We should have a formula like this

'='Z:\[FileA.xlsx]Sheet1'!$A$1

If Not ActiveCell.HasFormula Then Exit Sub

'Install an error handler, many things can fail

On Error GoTo Errorhandler

'Get all links in this file

Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'And the formula which refers to that file

Formula = ActiveCell.Formula

'Remove the [] for easier search

Temp = Replace(Formula, "[", "")

Temp = Replace(Temp, "]", "")

'Search for the file

For Each Item In Links

If InStr(1, Temp, Item, vbTextCompare) > 0 Then

'Found, file already open?

For Each Wb In Workbooks

If StrComp(Wb.FullName, Item, vbTextCompare) = 0 Then

Exit For

End If

Next

'No, open the file

If Wb Is Nothing Then Workbooks.Open Item

'Jump to the cell

Application.Goto Range(Mid(Formula, 2))

'Done

Exit Sub

End If

Next

'A cell in this file is also possible

Application.Goto Range(Mid(Formula, 2))



Exit Sub

Errorhandler:

If Err.Source = "" Then Err.Source = Application.Name

Debug.Print "Source : " & Err.Source

Debug.Print "Error : " & Err.Number

Debug.Print "Description: " & Err.Description

If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _

Err.Description & vbNewLine & vbNewLine & _

"Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then

Stop 'Press F8 twice

Resume

End If

End Sub

Hi Minhokiller,
<br />thanks for your effort. The translation didn't understand that I wanna have a complete row colored in reference to to values of certain cells.
<br />Best reguards
<br />Wil
<br />P.s.: Ich weiß, Deutsches Forum.
<br />