ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   #Value! error help (https://www.excelbanter.com/excel-discussion-misc-queries/95206-value-error-help.html)

[email protected]
#Value! error help
 
I have a workbook with 2 sheets inside, one named Time in Store Summary
and the other Time in Store SummaryMTD.
B1:B5 contains date and hours information. This is where is gets odd.
If I view the Time in Store Summary while the code is running, I get
the correct information returned. But not for the Time in Store
SummaryMTD and vice versa...Viewing MTD, it is correct but the other
sheet gives me the '#value!'.
Below is the code that I was running (the only difference between the
the 2 is the "MTD")...Does anyone have a clue why this has happened?

Thanks in Advance!

Hans

Windows("R2 Time Report.xls").Activate
With Worksheets("Time In Store Summary")
With .Range("B1")
.Formula = "='Call Summary.xls'!$E$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B1")
Cells.Replace What:="From: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B2")
.Formula = "='Call Summary.xls'!$h$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B2")
Cells.Replace What:="Through: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With



With Worksheets("Time In Store Summary")
With .Range("B3")
.Formula = "=NETWORKDAYS(B1,today(),'Time In Store
SummaryMTD'!M7:M15)"
.Value = .Value
With Worksheets("Time In Store Summary")
With .Range("B4")
.Formula = "=(B3*8)"
.Value = .Value
End With
End With
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B5")
.Formula = "=NETWORKDAYS(TODAY()+1,B2,'Time In Store
SummaryMTD'!M7:M15)*8"
.Value = .Value
End With
End With

Dave Peterson
#Value! error help
 
I'm not sure if this is your problem, but these sure looks funny to me:

..Formula = "='Call Summary.xls'!$E$5"
..Formula = "='Call Summary.xls'!$h$5"

Shouldn't you have a worksheet name in there someplace????



" wrote:

I have a workbook with 2 sheets inside, one named Time in Store Summary
and the other Time in Store SummaryMTD.
B1:B5 contains date and hours information. This is where is gets odd.
If I view the Time in Store Summary while the code is running, I get
the correct information returned. But not for the Time in Store
SummaryMTD and vice versa...Viewing MTD, it is correct but the other
sheet gives me the '#value!'.
Below is the code that I was running (the only difference between the
the 2 is the "MTD")...Does anyone have a clue why this has happened?

Thanks in Advance!

Hans

Windows("R2 Time Report.xls").Activate
With Worksheets("Time In Store Summary")
With .Range("B1")
.Formula = "='Call Summary.xls'!$E$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B1")
Cells.Replace What:="From: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B2")
.Formula = "='Call Summary.xls'!$h$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B2")
Cells.Replace What:="Through: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B3")
.Formula = "=NETWORKDAYS(B1,today(),'Time In Store
SummaryMTD'!M7:M15)"
.Value = .Value
With Worksheets("Time In Store Summary")
With .Range("B4")
.Formula = "=(B3*8)"
.Value = .Value
End With
End With
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B5")
.Formula = "=NETWORKDAYS(TODAY()+1,B2,'Time In Store
SummaryMTD'!M7:M15)*8"
.Value = .Value
End With
End With

--

Dave Peterson

[email protected]
#Value! error help
 
Dave,

Thanks for the "catch" I inserted the workbook/sheet name, but still
have the issue. I decided to break it down into 2 different subs and it
works fine...If you have any additional ideas, it would be appreciated
as usual!

Thanks,


Hans

Dave Peterson wrote:
I'm not sure if this is your problem, but these sure looks funny to me:

.Formula = "='Call Summary.xls'!$E$5"
.Formula = "='Call Summary.xls'!$h$5"

Shouldn't you have a worksheet name in there someplace????



" wrote:

I have a workbook with 2 sheets inside, one named Time in Store Summary
and the other Time in Store SummaryMTD.
B1:B5 contains date and hours information. This is where is gets odd.
If I view the Time in Store Summary while the code is running, I get
the correct information returned. But not for the Time in Store
SummaryMTD and vice versa...Viewing MTD, it is correct but the other
sheet gives me the '#value!'.
Below is the code that I was running (the only difference between the
the 2 is the "MTD")...Does anyone have a clue why this has happened?

Thanks in Advance!

Hans

Windows("R2 Time Report.xls").Activate
With Worksheets("Time In Store Summary")
With .Range("B1")
.Formula = "='Call Summary.xls'!$E$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B1")
Cells.Replace What:="From: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B2")
.Formula = "='Call Summary.xls'!$h$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B2")
Cells.Replace What:="Through: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B3")
.Formula = "=NETWORKDAYS(B1,today(),'Time In Store
SummaryMTD'!M7:M15)"
.Value = .Value
With Worksheets("Time In Store Summary")
With .Range("B4")
.Formula = "=(B3*8)"
.Value = .Value
End With
End With
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B5")
.Formula = "=NETWORKDAYS(TODAY()+1,B2,'Time In Store
SummaryMTD'!M7:M15)*8"
.Value = .Value
End With
End With

--

Dave Peterson


All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com