M
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
"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