Macro fails in different WB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's a macro that works perfectly in one of my workbooks:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("E6", Range("E65536").End(xlUp))
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
Next myCell
End Sub

Here's the same macro slightly hacked as added to a different WB:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("A2", Range("A65536").End(xlUp))
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End Sub

...and it returns "=RC[-26] & "", "" & RC[-25]" in pretty blue text in every
cell.

Where did I go wrong, Mother darling?
 
Your cells are formatted as Text, so the formulae aren't parsed. Try:

Public Sub UnsplitNames()
Dim myCell As Range
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Offset(0, 26).NumberFormat = "General"
For Each myCell In .Cells
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End With
End Sub
 
Thanks! That solved the problem just fine.

One question, not a "deal breaker", when the new column was populated, the
text was colored blue, whereas there is nothing colored blue anywhere else on
the WS. To fix it, of course, I just selected the whole column and set the
color to Auto, and presto changeo, everything went black <g>!

I gave the macro a hot key and ran it on some other Sheets, without the
color thing happening -- could it be merely that when the text was originally
displyed as code in blue, it set the cell color to blue?
--
Dave
Temping with Staffmark
in Rock Hill, SC


JE McGimpsey said:
Your cells are formatted as Text, so the formulae aren't parsed. Try:

Public Sub UnsplitNames()
Dim myCell As Range
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Offset(0, 26).NumberFormat = "General"
For Each myCell In .Cells
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End With
End Sub


Dave Birley said:
Here's a macro that works perfectly in one of my workbooks:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("E6", Range("E65536").End(xlUp))
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
Next myCell
End Sub

Here's the same macro slightly hacked as added to a different WB:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("A2", Range("A65536").End(xlUp))
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End Sub

..and it returns "=RC[-26] & "", "" & RC[-25]" in pretty blue text in every
cell.

Where did I go wrong, Mother darling?
Click to expand...
Click to expand...
 
Just realized why the macro worked in the first WB and not in the second -- I
had set all cells in the first WB to General.

See, I'm learning <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC


JE McGimpsey said:
Your cells are formatted as Text, so the formulae aren't parsed. Try:

Public Sub UnsplitNames()
Dim myCell As Range
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Offset(0, 26).NumberFormat = "General"
For Each myCell In .Cells
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End With
End Sub


Dave Birley said:
Here's a macro that works perfectly in one of my workbooks:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("E6", Range("E65536").End(xlUp))
myCell(1, 13).FormulaR1C1 = "=RC[-14] & "", "" & RC[-13]"
Next myCell
End Sub

Here's the same macro slightly hacked as added to a different WB:

Sub UnsplitNames()
Dim myCell As Range
For Each myCell In Range("A2", Range("A65536").End(xlUp))
myCell(1, 27).FormulaR1C1 = "=RC[-26] & "", "" & RC[-25]"
Next myCell
End Sub

..and it returns "=RC[-26] & "", "" & RC[-25]" in pretty blue text in every
cell.

Where did I go wrong, Mother darling?
Click to expand...
Click to expand...
 
Back
Top