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)"
This forum has been moved to TeachExcel.com
Ask all future questions in the New Excel Forum.
ExcelKey
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
xlAbsolute 1
xlAbsRowRelColumn 2
xlRelative 4
xlRelRowAbsColumn 3
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.=SUMIF(B1:B22, 2)
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
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
AsFunction("=SUMIF", RefCell(1, 2) & ":" & RefCell(22, 2), 2)
Return to Macros and VBA Questions
Users browsing this forum: No registered users and 115 guests