ExcelBanter - Macro copied from ng returns a #VALUE!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro copied from ng returns a #VALUE! (https://www.excelbanter.com/excel-programming/400285-macro-copied-ng-returns-value.html)

Macro copied from ng returns a #VALUE!

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

Macro copied from ng returns a #VALUE!

Copy the formula from the formulabar and paste it into your response.

And what's the fullname of the workbook--include drive, path and filename.

And one more...If you select the cell with the formula and hit F2, then enter,
what happens. If it works ok with this test, then try adding one line right
before the End Sub line:

...
Application.Calculate
End Sub

and since you're placing the formula in B2 of all sheets, I'd change those A1
references to B2.

BEEJAY wrote:

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

--

Dave Peterson

Macro copied from ng returns a #VALUE!

Dave: Thanks for having a look at this.
1: Work Book Names - Each one different
Current format used, of this application is xxxxx_X
(Meaning 5 numbers, underscore, then the letter "X".)
Drive\Path: C:\44094_X (sample)
2: The F2 and Enter idea did not work.
3: I changed the A1's to B2's.
4: Does the fact that these files are in Excel 95 format make a difference?
The reason for the files being processed in 95 is completed by the time
this particular macro is to be run, so I can save it in 2003 mode, if
that helps.
5: Formula from formula bar:
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)
JFS

"Dave Peterson" wrote:

Copy the formula from the formulabar and paste it into your response.

And what's the fullname of the workbook--include drive, path and filename.

And one more...If you select the cell with the formula and hit F2, then enter,
what happens. If it works ok with this test, then try adding one line right
before the End Sub line:

...
Application.Calculate
End Sub

and since you're placing the formula in B2 of all sheets, I'd change those A1
references to B2.

BEEJAY wrote:

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

--

Dave Peterson

Macro copied from ng returns a #VALUE!

I can't imagine that xl95 works differently. But if you enter that same formula
in any old worksheet in any old workbook that's been saved (open one, test it,
close without saving), does it work?

It worked fine for me.

And I assumed that your file was really named: C:\44094_x.xls
(with that extension)

I don't see any reason why it wouldn't work, but I don't remember xl95 enough to
know for sure.

And just one more nag--You are positive you put the formula in a cell in a
workbook that was saved, right?

BEEJAY wrote:

Dave: Thanks for having a look at this.
1: Work Book Names - Each one different
Current format used, of this application is xxxxx_X
(Meaning 5 numbers, underscore, then the letter "X".)
Drive\Path: C:\44094_X (sample)
2: The F2 and Enter idea did not work.
3: I changed the A1's to B2's.
4: Does the fact that these files are in Excel 95 format make a difference?
The reason for the files being processed in 95 is completed by the time
this particular macro is to be run, so I can save it in 2003 mode, if
that helps.
5: Formula from formula bar:
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)
JFS

"Dave Peterson" wrote:

Copy the formula from the formulabar and paste it into your response.

And what's the fullname of the workbook--include drive, path and filename.

And one more...If you select the cell with the formula and hit F2, then enter,
what happens. If it works ok with this test, then try adding one line right
before the End Sub line:

...
Application.Calculate
End Sub

and since you're placing the formula in B2 of all sheets, I'd change those A1
references to B2.

BEEJAY wrote:

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

--

Dave Peterson

--

Dave Peterson

Macro copied from ng returns a #VALUE!

Dave:
Thanks for all the input. If it wasn't for your double checking me, I would
have given up.
As it is, after extensive testing, I found out that the macro does not work
if the workbook name is the same as the worksheet name.
As soon as I changed the wb name, all the problems were resolved.
Thanks again for your patience with me.
My love affair with Excel and VBA is renewed.

jfs

"Dave Peterson" wrote:

I can't imagine that xl95 works differently. But if you enter that same formula
in any old worksheet in any old workbook that's been saved (open one, test it,
close without saving), does it work?

It worked fine for me.

And I assumed that your file was really named: C:\44094_x.xls
(with that extension)

I don't see any reason why it wouldn't work, but I don't remember xl95 enough to
know for sure.

And just one more nag--You are positive you put the formula in a cell in a
workbook that was saved, right?

BEEJAY wrote:

Dave: Thanks for having a look at this.
1: Work Book Names - Each one different
Current format used, of this application is xxxxx_X
(Meaning 5 numbers, underscore, then the letter "X".)
Drive\Path: C:\44094_X (sample)
2: The F2 and Enter idea did not work.
3: I changed the A1's to B2's.
4: Does the fact that these files are in Excel 95 format make a difference?
The reason for the files being processed in 95 is completed by the time
this particular macro is to be run, so I can save it in 2003 mode, if
that helps.
5: Formula from formula bar:
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)
JFS

"Dave Peterson" wrote:

Copy the formula from the formulabar and paste it into your response.

And what's the fullname of the workbook--include drive, path and filename.

And one more...If you select the cell with the formula and hit F2, then enter,
what happens. If it works ok with this test, then try adding one line right
before the End Sub line:

...
Application.Calculate
End Sub

and since you're placing the formula in B2 of all sheets, I'd change those A1
references to B2.

BEEJAY wrote:

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

--

Dave Peterson

--

Dave Peterson

Macro copied from ng returns a #VALUE!

Glad you found the error.

You've reminded me of an earlier discussion (I didn't remember it before you
posted).

There's another problem if the file name contains [], too.

Watch out for that.

(And I don't remember if there were other problems <sigh.)

BEEJAY wrote:

Dave:
Thanks for all the input. If it wasn't for your double checking me, I would
have given up.
As it is, after extensive testing, I found out that the macro does not work
if the workbook name is the same as the worksheet name.
As soon as I changed the wb name, all the problems were resolved.
Thanks again for your patience with me.
My love affair with Excel and VBA is renewed.

jfs

"Dave Peterson" wrote:

I can't imagine that xl95 works differently. But if you enter that same formula
in any old worksheet in any old workbook that's been saved (open one, test it,
close without saving), does it work?

It worked fine for me.

And I assumed that your file was really named: C:\44094_x.xls
(with that extension)

I don't see any reason why it wouldn't work, but I don't remember xl95 enough to
know for sure.

And just one more nag--You are positive you put the formula in a cell in a
workbook that was saved, right?

BEEJAY wrote:

Dave: Thanks for having a look at this.
1: Work Book Names - Each one different
Current format used, of this application is xxxxx_X
(Meaning 5 numbers, underscore, then the letter "X".)
Drive\Path: C:\44094_X (sample)
2: The F2 and Enter idea did not work.
3: I changed the A1's to B2's.
4: Does the fact that these files are in Excel 95 format make a difference?
The reason for the files being processed in 95 is completed by the time
this particular macro is to be run, so I can save it in 2003 mode, if
that helps.
5: Formula from formula bar:
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)
JFS

"Dave Peterson" wrote:

Copy the formula from the formulabar and paste it into your response.

And what's the fullname of the workbook--include drive, path and filename.

And one more...If you select the cell with the formula and hit F2, then enter,
what happens. If it works ok with this test, then try adding one line right
before the End Sub line:

...
Application.Calculate
End Sub

and since you're placing the formula in B2 of all sheets, I'd change those A1
references to B2.

BEEJAY wrote:

The body of this macro was cut and pasted from this ng.
The only adjustment made was to change the cell to B2

When I try to run it, it puts #VALUE! in cell B2.
The file was saved (a number of times) prior to running the macro.
The cell was checked and it is formatted as "General".
What else can I check/change?

' WSNCOPY Macro
' From Dave Peterson

Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B2").Formula = myFormula
End With
Next wks
End Sub

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson