replace missing values

[mcyang]

I am trying to run this macro using a command button in a tab named
"Start". I basically need this macro to retrieve data from Essbase in
tab "Ess-Act", then replace missing values with "0" and then copy the
range to tab "Comm". When I run, everything seems to work except the
replace #missing with "0". I get a warning message "Excel cannot find
any data to replace". I am novice at VBA, so if you could help me
modify my code, I would appreciate. Thanks. Mike

Sub CopyRange_Actuals()
' Retrieve Data from Actuals
Dim wbName As String
wbName = ThisWorkbook.Name
x = EssVRetrieve("[" & wbName & "]Ess-Act", range("A1:K51"), 1)
' Replace missing values with zeros
Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
range("A4").Select
' Copy Essbase data to product tabs
Sheets("Ess-Act").range("D8:J15").Copy Destination:=Worksheets
("Comm").range("D11:J18")
End Sub

 

[jasontferrell]

I don't have access to Essbase, and so I'm not sure exactly how the
#Missing value comes up. However, I think the essense of your problem
is that the #Missing is a value that results from a formula rather
than being the text value of a cell. The Cells.Replace method doesn't
allow you to search for values, only formulas.

You might want to look at two solutions. One would be to check cells
for the error value (the worksheet function is "iserror"). Another
would be to try to convert the #Missing into a text value first, such
as by copying the entire results, then pasting values. I'm not
familiar with the Essbase functions, so I'm not sure if it would work,
but you could try inserting this code after the Essbase retrieve
function:

Range("A1:K51").Copy
Range("A1:K51").PasteSpecial xlPasteValues