seting formula in cel by VBA

[VSR]

Hi All, I'm looking for help about "Err 1004" in row below:
Range("A1").Formula = "=INDEX('[fileA.xls]Sheet1'!$C:$D;MATCH(A2;'[fileA.xls]Sheet1'!$C:$C;0);2)"

[charles]

VSR,

Could you post a copy of the workbook. To do so when you reply you will see a tab below the msgbox marked "Upload" click on it and then you can select the file you wish to attach.

[VSR]

it's for example only with abstract data, when formula find equal to cell "A2" in "fileA", "colC", value from same row, but from col "D", is appears in cell "A1". It,s work in cell in sheet from workbook, perfect.

[charles]

HI,

Take a look at this file.
You change the value in "E2" and the result will be displayed in "E1"

[Sisyphus]

Hi,
This is a tacky business, not solved as easily as it looks. Long ago I wrote a number of functions to do this job. Here they are:

Code: Select all

Private Function RefCell(ByVal R As Long, _
                         ByVal C As Long, _
                         Optional ByVal Typ As Long, _
                         Optional ByVal VSh As Variant) As String
    ' cell(R, C)
    ' in Sheet(VSh) using XlReferenceType
   
    RefCell = ConvertColNum(C) & CStr(R)
    Typ = IIf(Typ, Typ, 4)
    RefCell = Application.ConvertFormula(RefCell, xlA1, xlA1, Typ)
    If VarType(VSh) = vbError Then VSh = ""
    If Val(VSh) Then VSh = CStr(VSh)
    If Len(VSh) Then _
       RefCell = InBrackets(Trim(VSh), 39) & "!" & RefCell
End Function


Using the values for R (Row) and C (Column) the function returns a cell reference like A1.
You can supply the optional Typ argument to specify the type of reference, like $A1 etc.
The Typ argument must be one of the xlReferenceType constants:

xlAbsolute 1
xlAbsRowRelColumn 2
xlRelative 4
xlRelRowAbsColumn 3

The final, optional, argument is Vsh. So, the function can return something like 'Sheet Two'!$R$32

The next function is AsFunction. It wraps the FUNC string in brackets and places the function's name before it, like

=SUMIF(B1:B22, 2)

In this example "=SUMIF" would be the FUNC argument, "B1:B22" and 2 elements of the ParamArray. You can have as many as you like. The = sign must be handed down so as to enable you to nest functions.

Code: Select all

Private Function AsFunction(ByVal FUNC As String, _
                            ParamArray Items() As Variant) As String
    ' Return [=][FUNC](strFunc)
   
    Dim Fn As Integer, Fs As String
    Dim S As String
   
    For Fn = 0 To UBound(Items)
        Fs = Trim(CStr(Items(Fn)))
        If Len(Fs) And Len(S) Then
            If Right(S, 1) <> "," Then S = S & ","
        End If
        S = S & Fs
    Next Fn
    AsFunction = Trim(FUNC) & InBrackets(S)
End Function


The final function in the trio, InBrackets, supports the other functions. All it does is to enclose the argument Expr in brackets. Called without further argument, normal round brackets will be added. However, you can pass an optional argument Quote to produce other kinds of brackets. The most common is Chr(34) to embrace a string, like "My string"

Code: Select all

Private Function InBrackets(ByVal Expr As String, _
                            Optional ByVal Quote As Long) As String
    ' Insert Expr between brackets or Chr(Quote)
   
    Dim Q1 As String, Q2 As String
   
    Q1 = Chr(IIf(Quote, Quote, 40))
    Q2 = Chr(IIf(Quote, Quote, 41))
    InBrackets = Q1 & Expr & Q2
End Function


Sticking with my above example of =SUMIF(B1:B22, 2) the code would look like this:

Code: Select all

AsFunction("=SUMIF", RefCell(1, 2) & ":" & RefCell(22, 2), 2)

To nest formulae, like in INDEX / MATCH, you would construct the inside formula first, store it in a string, then construct the outside formula using the prepared string as one of the elements in the ParamArray. I haven't tried the line of code and haven't used the system in a long time. But the above procedures are all tried and proven and by tinkering with it a little you can work out how to solve your problem. The tools are here!

[VSR]

IT'S SOLVED, MAY BE MY EXPLANATION WAS URONG.