Can't get RIGHT to work

[TheWennerWoman]

Hello,

I have the following (probably very inefficient bit of code) which is failing with "Object Required". Can anyone tell me what I'm doing wrong and perhaps suggest a better way rather than using a FOR-NEXT loop on 32,000 records?

lrow = wb1.Sheets("Go Card").Cells(wb1.Sheets("Go Card").Rows.Count, "B").End(xlUp).Row
For a = 2 To lrow
b = Right(wb1.Sheets("Go Card").Range("AE" & a), 13).Value
Range("A" & a).Value = b
Next

b is dimmed as a String.

If I take the right out and just use

b = wb1.Sheets("Go Card").Range("AE" & a).Value

it runs fine (albeit slowly)

Many thanks as always.

 

[RoryA]

You could simply use a formula then replace it:

lrow = wb1.Sheets("Go Card").Cells(wb1.Sheets("Go Card").Rows.Count, "B").End(xlUp).Row

with Range("A2:A" & lrow)
.Formula = "=RIGHT(AE2,13)"
.Value = .value
end with

 

[RoryA]

You've got the Value in the wrong place:

b = Right(wb1.Sheets("Go Card").Range("AE" & a).Value, 13)

 

[nemmi69]

Are you trying to reverse the order?

 

[TheWennerWoman]

You could simply use a formula then replace it:

lrow = wb1.Sheets("Go Card").Cells(wb1.Sheets("Go Card").Rows.Count, "B").End(xlUp).Row

with Range("A2:A" & lrow)
.Formula = "=RIGHT(AE2,13)"
.Value = .value
end with

Perfect, works a treat, many thanks!