Macro

[Steve]

Hi the following macro excerpt returns data in the following format


1-6-2-1-5 with five being the most recent number that meets the macro
criteri and five being lasty number that meets the criteria.

macro starts-------------------------------------------------------

Set c = .Find(What:=Cells(i, "q"), After:=Cells(lr - 1, "q"),
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then
firstaddress = c.Address
Do
If Cells(c.Row, "h") = Cells(i, "h") And i <> c.Row Then
'ms = ms & "-" & Cells(c.Row, "AZ")
ms = Cells(c.Row, "AZ") & "-" & ms
'Cells(i, "X") = Right(ms, Len(ms) - 1)
Cells(i, "x") = Left(ms, Len(ms) - 1)
End If

macro ends---------------------------------------------------------

Could anyone please tell me how to reverse the output so that it reads
as follows:

5-1-2-6-1

Thanks in advance

Steve

 

[Claus Busch]

Hi Steve,

Am Tue, 29 May 2012 13:34:07 +0100 schrieb Steve:

Cells(i, "x") = Left(ms, Len(ms) - 1)
End If

macro ends---------------------------------------------------------

Could anyone please tell me how to reverse the output so that it reads
as follows:

5-1-2-6-1

try:
Cells(i, "x") = StrReverse(Left(ms, Len(ms) - 1))


Regards
Claus Busch

 

[Steve]

Thank you very much Claus, that worked perfectly

Much appreciated

steve

 

[Steve]

Sorry Claus, it is nearly perfect. I tested on a small sample and it
worked fine but when I applied it to a larger sample i got some
anomolies. I have pasted a few examples for you.

Just to confirm I amended the macro as follows:

Original

If Not c Is Nothing Then
firstaddress = c.Address
Do
If Cells(c.Row, "h") = Cells(i, "h") And i <> c.Row Then
'ms = ms & "-" & Cells(c.Row, "AZ")
ms = Cells(c.Row, "AZ") & "-" & ms
'Cells(i, "X") = Right(ms, Len(ms) - 1)
Cells(i, "x") = Left(ms, Len(ms) - 1)
End If

Amended

If Not c Is Nothing Then
firstaddress = c.Address
Do
If Cells(c.Row, "h") = Cells(i, "h") And i <> c.Row Then
'ms = ms & "-" & Cells(c.Row, "AZ")
ms = Cells(c.Row, "AZ") & "-" & ms
'Cells(i, "X") = Right(ms, Len(ms) - 1)
Cells(i, "x") = StrReverse(Left(ms, Len(ms) - 1))
End If

Original Cell

01

Amended

10

Original Cell

12

Amended Cell

21

It looks like the change has reversed the numbers as well as the
order. which is why it looked perfect originally as the numbers were
all single digits.

Is there a way around this

Thanks for your time and help Claus

Regards

steve

 

[Claus Busch]

Hi Steve,

Am Tue, 29 May 2012 17:28:04 +0100 schrieb Steve:

It looks like the change has reversed the numbers as well as the
order. which is why it looked perfect originally as the numbers were
all single digits.

Is there a way around this

StrReverse will reverse your string digit by digit. So it works ok with
single digit numbers.
In your case you have to seperate your string with left, mid and right
and put it together again.


Regards
Claus Busch

 

[Steve]

Thanks Claus but I am a bit of a novice at excel and this macro was
created for me by someone I am no longer in contact with.

Are you able to advise how I could do this?

Don't worry if it is a time consuming task.

Thanks

Steve

 

[Claus Busch]

Hi Steve,

Am Tue, 29 May 2012 18:34:23 +0100 schrieb Steve:

Are you able to advise how I could do this?

Don't worry if it is a time consuming task.

try:
ms = Left(ms, Len(ms) - 1)
myArr = Split(ms, "-")
For j = 4 To 0 Step -1
strNew = strNew & myArr(j) & "-"
Next
Cells(i, "X") = Left(strNew, Len(strNew) - 1)


Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

better try:

ms = Left(ms, Len(ms) - 1)
myArr = Split(ms, "-")
For j = UBound(myArr) To LBound(myArr) Step -1
strNew = strNew & myArr(j) & "-"
Next
Cells(i, "X") = Left(strNew, Len(strNew) - 1)


Regards
Claus Busch

 

[Steve]

Claus thank you again for your time in trying to help me with my
macro.

Can I just ask where the new code goes should be added and what it
should replace, if anything

Original Macro


If Not c Is Nothing Then
firstaddress = c.Address
Do
If Cells(c.Row, "h") = Cells(i, "h") And i <> c.Row Then
'ms = ms & "-" & Cells(c.Row, "AZ")
ms = Cells(c.Row, "AZ") & "-" & ms
'Cells(i, "X") = Right(ms, Len(ms) - 1)
Cells(i, "x") = Left(ms, Len(ms) - 1)
End If


Your Code

ms = Left(ms, Len(ms) - 1)
myArr = Split(ms, "-")
For j = UBound(myArr) To LBound(myArr) Step -1
strNew = strNew & myArr(j) & "-"
Next
Cells(i, "X") = Left(strNew, Len(strNew) - 1)

Sorry for taking up your time but your help is appreciated

Kind regards

Steve

 

[Claus Busch]

Hi Steve,

Am Tue, 29 May 2012 20:37:39 +0100 schrieb Steve:

Can I just ask where the new code goes should be added and what it
should replace, if anything

If Not c Is Nothing Then
firstaddress = c.Address
Do
If Cells(c.Row, "h") = Cells(i, "h") And i <> c.Row Then
'ms = ms & "-" & Cells(c.Row, "AZ")
ms = Cells(c.Row, "AZ") & "-" & ms
'Cells(i, "X") = Right(ms, Len(ms) - 1)
ms = Left(ms, Len(ms) - 1)
myArr = Split(ms, "-")
For j = UBound(myArr) To LBound(myArr) Step -1
strNew = strNew & myArr(j) & "-"
Next
Cells(i, "X") = Left(strNew, Len(strNew) - 1)
End If

no matter how many digits the numbers have and how many numbers are in
your string - your string will be seperated on "-" and will be set
together reversed. But the numbers will not be reversed.


Regards
Claus Busch