CODEWITHSUNDEEP
excelvbaexcel-formula
J_Go
J_Go

Reputation: 21

VBA using .Formula in Excel returns 0 when the Range.Value is Empty

if the result of the .Formula = "='C:\data\[adxl364.xls]ADXL364_QC'!A1" is empty, it returns a value of 0 in my Worksheet.

what should I add to the code for it not to return a value of 0 when the Cell is empty?

Sub GetRange()
    With Range("A:Z")
        .Formula = "='C:\data\[adxl364.xls]ADXL364_QC'!A1"
        .Formula = .Value
    End With
End Sub

Upvotes: 0

Views: 1179

Answers (2)

user6432984
user6432984

Reputation:

You need to modify your formula to return an empty string when "..!A1" does not = 0.

Sub GetRange()
    With Range("A:Z")
        .Formula = "=If('C:\data\[adxl364.xls]ADXL364_QC'!A1 > 0,'C:\data\[adxl364.xls]ADXL364_QC'!A1,Text(,))"
        .Formula = .Value
    End With
End Sub

I use Text(,) to return an empty string instead of doubling up the double quotation marks (e.g. """"):

"=If('C:\data\[adxl364.xls]ADXL364_QC'!A1 > 0,'C:\data\[adxl364.xls]ADXL364_QC'!A1,"""")"

Upvotes: 1

Vincent G
Vincent G

Reputation: 3188

You can use the function: =IF(ISBLANK('C:\data\[adxl364.xls]ADXL364_QC'!A1);"";'C:\data\[adxl364.xls]ADXL364_QC'!A1)

Upvotes: 1

Related Questions