Looping through a range object modifying each cell

[tcardwell]

Trying to create a subroutine that takes a selected range and loops through each cell trimming off all but the first five characters. Current code is as follows:

Sub TrimAcntNo()

Dim Cell As Range
For Each Cell In Selection
ActiveCell.Formula = "=Left(activecell,5)"
Next Cell
End Sub

I am getting a #Name error message when I run this code.
Just trying to run through the cells and trim off all but the first 5 characters.

TIA,
tcardwell

 

[tcardwell]

Hi Mumps,

Yes, this version worked on all cells beginning at A2, thanks!!

 

[Marcelo Branco]

Hi Vladimir

This not worked for me

Sub Left5()
  Selection.Value = Evaluate("LEFT(" & Selection.Address & ",5)")
End Sub

For example

Original data

[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

12345nju​

[/TD]
[TD]

abcdefghij​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12345nju​

[/TD]
[TD]

abcdefghij​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

12345nju​

[/TD]
[TD]

abcdefghij​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

12345nju​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

12345nju​

[/TD]
[TD]

abcdefghij​

[/TD]
[/TR]
</TBODY>[/TABLE]



After macro

[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

12345​

[/TD]
[TD]

12345​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12345​

[/TD]
[TD]

12345​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

12345​

[/TD]
[TD]

12345​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

12345​

[/TD]
[TD]

12345​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

12345​

[/TD]
[TD]

12345​

[/TD]
[/TR]
</TBODY>[/TABLE]


Am I missing something?

But this this worked perfectly

Sub aTest()
    Dim FirstCell As String, LastCell As String
    
    With Selection
        FirstCell = .Cells(1, 1).Address
        LastCell = .Cells(.Rows.Count, .Columns.Count).Address
    End With
      
    Selection = Evaluate("=IF(" & FirstCell & ":" & LastCell & "<>"""",LEFT(" & Selection.Address & ",5),"""")")
        
End Sub

M.

 

[Marcelo Branco]

I over complicated the code

I think this is ok

Sub aTest()
    Selection = Evaluate("=IF(" & Selection.Address & "<>"""",LEFT(" & Selection.Address & ",5),"""")")
End Sub

M.

 

[Rick Rothstein]

For the collection:

Sub Left5()
   Selection.Value = Evaluate("LEFT(" & Selection.Address & ",5)")
End Sub

Worked perfectly, thanks Vladimir!

Are you sure it worked correctly for you? If the cells in your selected range have different first five characters, I think you will find Vladimir's formula simply returns the five first characters from the first cell to all the other cells rather than return the first five character of each cell to its own cell. The reason is, by themselves, text string functions are not "array aware" and Evaluate is an array processing engine (when given an array to process). The trick to making Evaluate work correctly on an arrayed text string function is to embed the function call within another function that is array aware. The following modification to Vladimir's function will make it work correctly (here, the IF function call is the array aware function that induces the array processing into the LEFT function)...

Sub Left5()
  Selection.Value = Evaluate("IF(LEN(" & Selection.Address & "),LEFT(" & Selection.Address & ",5),"""")")
End Sub

 

[tcardwell]

You are probably right. I got lazy with my test data and just copied the same contents of the first cell to about a dozen subsequent cells and ran the sub. I assumed it worked because I was mainly interested in getting the left() function to work. Thanks for pointing that out!! Eventually would have crashed and burned when tested on real world data, thanks again.

 

[JLGWhiz]

Worked perfectly, thanks JLGwhiz!!

You're welcome,
Regards, JLG

 

[JLGWhiz]

Hi Mumps,

For some reason when I ran your code it only worked on the first cell in the range.

Hi tcardwell, I am sure Mumps is aware of this. The reason the code only returned the value of the first cell was because it is th only active cell. The For,,,Each method does not activate the cells as it walks down the column, it merely addresses them to execute the code, But it will return the value of the cell that is active when it executes. My personal preference is to not use ActiveCell any nore that absolutely necessary, and when I do use it to know which cell it is.

 

[ZVI]

I am apologizing for misleading, the INDEX was missing.
This works:

Sub Left5()
  Selection.Value = Evaluate("INDEX(LEFT(" & Selection.Address & ",5),0)")
End Sub

Marcelo & Rick - thank you both for the catching and correction the issue.

 

[Rick Rothstein]

I am apologizing for misleading, the INDEX was missing.
This works:

Sub Left5()
  Selection.Value = Evaluate("INDEX(LEFT(" & Selection.Address & ",5),0)")
End Sub

Marcelo & Rick - thank you both for the catching and correction the issue.

You are welcome, of course. By the way, I like your use of the INDEX function to induce the array processing of the LEFT function within the Evaluate function... the address argument is only repeated one time using it.

 

[Marcelo Branco]

I am apologizing for misleading, the INDEX was missing.
This works:

Sub Left5()
  Selection.Value = Evaluate("INDEX(LEFT(" & Selection.Address & ",5),0)")
End Sub

Marcelo & Rick - thank you both for the catching and correction the issue.

Vladimir

I also like the use of INDEX - much simpler.
Hadn't occurred to me.

M.