Using LEFT function on entire range in vba?

[supdawg]

Been trying to figure out how to do this for a while. Can't seem to get it right.

This is my range:

Set MyRange = wb.Sheets(1).Range("B4:C" & Cells(Rows.Count, "B").End(xlUp).Row)

I need to do this to that range:

Application.WorksheetFunction.Left(Range(MyRange), 5)

Just trying to apply a LEFT(range,5) to the range I set above. Why can'tI figure out how?

I know its something small (as usual)

 

[Tom Urtis]

You are describing a wish to do something that is really wierd. The LEFT function returns or identifies the left characters of a string for as many characters as the integer argument you specify, such as the leftmost 5 characters.

But that is for a string in a cell. You *can* use LEFT in other ways to return a value, example, to sum all the numbers in column B whose adjacent column A text starts with "PM":
=SUMPRODUCT((LEFT(A2:A100,2)="PM")+0,B2:B100)
Or
=SUMIF(A1:A10,"PM*",B1:B10)
Or
=SUMIF(A1:A100,"PM*",B1:B100)

But what you are doing is looking at two columns, and then stopping dead in your tracks as if the leftmost values (potentially millions with 2 columns) are a range (because you set a range type variable to that) and not characters or placeholders.

What are you ultimately after? Whatever it is, you won't get there from here.

 

[supdawg]

Sorry...

Just thought it would be possible to use the LEFT function within a range and paste the results of that formula back into that range?

I know I could just record macro to left and copy paste, but I was wondering if there was a way to do it with vba without actualy doing anything inside the sheet?

 

[Tom Urtis]

Don't understand how you would accomplish the feat of not doing anything inside the sheet, seeing as the cells are inside the sheet you want to change.

Maybe you want to edit the cells in the used range of columns B and C such that they end up showing only the 5 leftmost characters of their original text. If so, just loop through them like this:

Sub Test()
Application.ScreenUpdating = False
Dim cell as range
for each cell in wb.sheets(1).Range("B4:C" & Cells(Rows.Count, "B").End(xlUp).Row)
If len(cell.value) > 5 then cell.value = left(cell.value, 5)
Next cell
Application.ScreenUpdating = True
End Sub

 

[supdawg]

Thanks. Just thought there was an easier way to do this. Basically doing it in memory and pasting the results back into the sheet. I don't hardly ever use worksheet functions in vba.

 

[pgc01]

Hi

Assuming the range has values, these are 2 alternatives:

1 - do it 1 column at a time

Dim r As Range, rCol As Range
 
Set r = Range("A1:B10")
For Each rCol In r.Columns
    rCol.TextToColumns Destination:=rCol(1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 9))
Next rCol

2 - evaluate the result and replace all the values at a time

Set r = Range("A1:B10")
 
r.Value = Evaluate("if(row(" & r.Address & ")+column(" & r.Address & "),left(" & r.Address & ",5))")

 

[Da Spotz]

@pgc01: I realise this is an old post, so I hope I may still get an answer. I understand that the evaluate function allows you to use worksheet functions, but could you explain what purpose the if statement has here? I tested the code for my specific need wothout it and it worked just fine, so I'm curious why you'd even need it at all. Would appreciate a reply. Below is the code as I use it:

   Set rng1 = ws2.Range("B1:B" & LastRow2)
    Set rng2 = ws2.Range("C1:C" & LastRow2)
    
    rng2.Value = Evaluate("left(" & rng1.Address & ",2)")

 

[pgc01]

Hi
Welcome to the board

Sometimes Evaluate() does not understand that it should loop through rows and columns to get the array result.

Using if(row() or If(column() forces Evaluate() to loop.

An example:

Dim v As Variant

Range("A1:E1") = Array("a", "b", "c", "d", "e")

v = Evaluate("upper(a1:e1)")

You'll see that you get the result in v: "A"

It's like Evaluate does not loop, it just gets you the first result.

Now try instead:

v = Evaluate("if(column(a1:e1),upper(a1:e1))")

You'll see that now v is an array with the 5 uppercase letters.

The if(column() is there just to make Evaluate realise that the result is an array and that it should loop.


Now, this does not mean that you always need to use the if(row() or the if(column() to make Evaluate() return an array, but I usually don't bother to check if it's really necessary and just add it.

 

[Da Spotz]

Thank you, that cleared things up for me!

 

[Rick Rothstein]

2 - evaluate the result and replace all the values at a time

Set r = Range("A1:B10")
 
r.Value = Evaluate("if(row(" & r.Address & ")+column(" & r.Address & "),left(" & r.Address & ",5))")

Why not make the variable 'r' a String instead and then do it this way...

r ="A1:B10"
 
Range(r).Value = Evaluate("if(row(" & r & ")+column(" & r & "),left(" & r & ",5))")