VBA setting formula for a cell causes "Wrong data type" error

[undercups]

Below is a function I am trying to debug and the problem I have is with
the line "Range("A1").FormulaR1C1 = strFormula".

When I run the formula the cell displays the error "#Value". I look
into the error checking log and thats where I see the message "A value
used in the formula is of the wrong data type".

Stepping through the code shows that the Range statement to be causing
the problem

All variables are of string, the strFormula contains the expected
value, the cell is of type "general". I have tried variations such as
Range("A1").select/ActiveCell.formula, formula / formula R1C1. So why
am I getting the error?

Here is the source code :-

Function CreateLink(strTargetName As String, strCellName As String) As
String

Dim strFileName As String
Dim strPathName As String
Dim strFormula As String
Dim strReturnValue As String

Dim intCount As Integer

Application.Volatile

'Get the file name
strFileName = Range("B32").Value ' the cell contains the formula
"=CELL("filename")

' Find the current path
strPathName = Left$(strFileName, InStr(strFileName, "\[") - 1)

' Remove the current directory as linked spreadsheet is 1 level up
intCount = Len(strPathName)
Do Until Mid$(strPathName, intCount, 1) = "\"
intCount = intCount - 1
Loop

'Build up the link
strFormula = "='" & Left(strPathName, intCount) & "[" & strTargetName &
"]'!" & strCellName

Range("A1").Formula = strFormula
Calculate

strReturnValue = Range("A1").Value

CreateLink = strReturnValue

End Function

Thanks in anticipation

Duncan

 

[Bob Phillips]

If you are passing the cell name as say H10, then perhaps you need

Range("A1").Formula = strFormula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[undercups]

Bob

I have already tried that. No joy.

 

[Dave Peterson]

This formula:
=CELL("filename")
returns the name of the worksheet that was active when excel calculated--not the
name of the path/workbook/worksheet holding that formula.

I'd use
=cell("filename",a1)
so that I'd get that info about the cell with the formula.

But VBA offers another way to get the path directly:

msgbox activeworkbook.path

And if you remember your old DOS days, you could go one level up a path by
doing:
cd ..
(two dots meant up a level)

Maybe this will give you an idea for an alternative:

Option Explicit
Sub testme()

Dim myPath As String
Dim strFormula As String
Dim myVar As Variant

myPath = ActiveWorkbook.Path & "\..\"

strFormula = "='" & myPath & "[book1.xls]sheet1'!$a$1"

With ActiveSheet.Range("c3")
.Formula = strFormula
Application.Calculate
myVar = .Value
.ClearContents
End With

MsgBox myVar
End Sub

I hardcoded the name of the workbook/worksheet/range address--but that was just
to show that it could work.

Ps. My formula would look like:

='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1

If you're not passing a global range name, you may want to include the sheet
name.

pps. John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

Below is a function I am trying to debug and the problem I have is with
the line "Range("A1").FormulaR1C1 = strFormula".

When I run the formula the cell displays the error "#Value". I look
into the error checking log and thats where I see the message "A value
used in the formula is of the wrong data type".

Stepping through the code shows that the Range statement to be causing
the problem

All variables are of string, the strFormula contains the expected
value, the cell is of type "general". I have tried variations such as
Range("A1").select/ActiveCell.formula, formula / formula R1C1. So why
am I getting the error?

Here is the source code :-

Function CreateLink(strTargetName As String, strCellName As String) As
String

Dim strFileName As String
Dim strPathName As String
Dim strFormula As String
Dim strReturnValue As String

Dim intCount As Integer

Application.Volatile

'Get the file name
strFileName = Range("B32").Value ' the cell contains the formula
"=CELL("filename")

' Find the current path
strPathName = Left$(strFileName, InStr(strFileName, "\[") - 1)

' Remove the current directory as linked spreadsheet is 1 level up
intCount = Len(strPathName)
Do Until Mid$(strPathName, intCount, 1) = "\"
intCount = intCount - 1
Loop

'Build up the link
strFormula = "='" & Left(strPathName, intCount) & "[" & strTargetName &
"]'!" & strCellName

Range("A1").Formula = strFormula
Calculate

strReturnValue = Range("A1").Value

CreateLink = strReturnValue

End Function

Thanks in anticipation

Duncan

 

[Guest]

Range("A1").Formula = strFormula
Calculate

would be illegal in a User defined function used in a worksheet.

A formula can only return a value to the cell in which it is used. It can't
change other cells or event he cell in which it is contained. Commands like
that will cause a #Value.

I didn't see where you are using the arguments in your function.