I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

You just need to concatenate,

:==================================
Sub NewString()

strA = Cells(1, 1).Value & ", " & Cells(1, 2).Value & ", " & _
Cells(1, 3).Value & ", " & Cells(1, 4).Value & ", " & Cells(1,
5).Value

MsgBox strA

End Sub
:==================================

Is this all you require?

http://www.excel-ant.co.uk

On 12 Aug 2006 01:32:50 -0700, "Maxi" wrote:

I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

Perhaps this will give you some ideas as to how to implement your algorithm
into code:

================================================== =
Option Explicit
Sub setup()
Const strA As String = "1, 2, 3, 4, 5"
Const strB As String = "3, 5, 6, 8, 10"

Debug.Print Rpl(strA)
Debug.Print Rpl(strB)

End Sub

Function Rpl(str)

Dim c As Range
Dim i As Long
Dim temp
Dim res()

Dim rg As Range

Set rg = [A1:J1]

temp = Split(str, ",")
For i = 0 To UBound(temp)
temp(i) = Val(temp(i))
Next i

ReDim res(UBound(temp))
For i = 0 To UBound(temp)
res(i) = rg(1, Val(temp(i)))
Next i

Rpl = Join(res, ", ")

End Function
===================================


--ron

Assuning each value in the original string is an index into the first row, try:

Sub Macro1()

Dim s, st, v As String
Dim n As Integer

s = "3,5,6,8,10"
st = Replace(s, ",", "")
i = Len(s) - Len(st)

s2 = Split(s, ",")
n = s2(0)
v = Cells(1, n).Value

For j = 1 To i
n = s2(j)
v = v & ", " & Cells(1, n).Value
Next

s = v
MsgBox (s)
End Sub

--
Gary's Student


"Maxi" wrote:

I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

Thanks Ron, I don't think the function can be reduced more to improve
on process time. Right?

somethinglikeant, it looks like you did not understand my question but
thanks for trying.



Ron Rosenfeld wrote:
On 12 Aug 2006 01:32:50 -0700, "Maxi" wrote:

I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

Perhaps this will give you some ideas as to how to implement your algorithm
into code:

================================================== =
Option Explicit
Sub setup()
Const strA As String = "1, 2, 3, 4, 5"
Const strB As String = "3, 5, 6, 8, 10"

Debug.Print Rpl(strA)
Debug.Print Rpl(strB)

End Sub

Function Rpl(str)

Dim c As Range
Dim i As Long
Dim temp
Dim res()

Dim rg As Range

Set rg = [A1:J1]

temp = Split(str, ",")
For i = 0 To UBound(temp)
temp(i) = Val(temp(i))
Next i

ReDim res(UBound(temp))
For i = 0 To UBound(temp)
res(i) = rg(1, Val(temp(i)))
Next i

Rpl = Join(res, ", ")

End Function
===================================


--ron

On Sat, 12 Aug 2006 06:39:30 -0400, Ron Rosenfeld
wrote:

On 12 Aug 2006 01:32:50 -0700, "Maxi" wrote:

I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

Perhaps this will give you some ideas as to how to implement your algorithm
into code:

================================================= ==
Option Explicit
Sub setup()
Const strA As String = "1, 2, 3, 4, 5"
Const strB As String = "3, 5, 6, 8, 10"

Debug.Print Rpl(strA)
Debug.Print Rpl(strB)

End Sub

Function Rpl(str)

Dim c As Range
Dim i As Long
Dim temp
Dim res()

Dim rg As Range

Set rg = [A1:J1]

temp = Split(str, ",")
For i = 0 To UBound(temp)
temp(i) = Val(temp(i))
Next i

ReDim res(UBound(temp))
For i = 0 To UBound(temp)
res(i) = rg(1, Val(temp(i)))
Next i

Rpl = Join(res, ", ")

End Function
===================================


--ron


Some redundancy in the above:

===============================
Option Explicit
Sub setup()
Const strA As String = "1, 2, 3, 4, 5"
Const strB As String = "3, 5, 6, 8, 10"

Debug.Print Rpl(strA)
Debug.Print Rpl(strB)

End Sub
Function Rpl(str)

Dim c As Range
Dim i As Long
Dim temp
Dim res()

Dim rg As Range

Set rg = [A1:J1]

temp = Split(str, ",")

ReDim res(UBound(temp))
For i = 0 To UBound(temp)
res(i) = rg(1, Val(temp(i)))
Next i

Rpl = Join(res, ", ")

End Function
===============================
--ron

Sorry I did not see Gary's student's reply. Even this works out per my
requirement.

I have one more code from Denis from mrexcel. He claims that Replacing
50,000 strings and writing them to disk took 12.47 seconds. I don't
know how he did this. I have 3 codes now. One from you, one from Ron
and the below code from Denis.

I want to use the one which produces the result quickly as I am going
to replace thousands of strings like these hence I need the most
efficient and shortest code to improve on time.

Thanks.

Sub Switch_2()
Dim strA As String
Dim strB As String
Dim i As Integer

strA = "1,2,4,6,8"
strB = ""
strA = strA & ","

For i = 1 To 5
strB = strB & Cells(1, CInt(Left(strA, InStr(1, strA, ",") -
1))) & ","
strA = Mid(strA, InStr(1, strA, ",") + 1)
Next i
strA = Left(strB, Len(strB) - 1)
MsgBox strA
End Sub

Gary''s Student wrote:
Assuning each value in the original string is an index into the first row, try:

Sub Macro1()

Dim s, st, v As String
Dim n As Integer

s = "3,5,6,8,10"
st = Replace(s, ",", "")
i = Len(s) - Len(st)

s2 = Split(s, ",")
n = s2(0)
v = Cells(1, n).Value

For j = 1 To i
n = s2(j)
v = v & ", " & Cells(1, n).Value
Next

s = v
MsgBox (s)
End Sub

--
Gary's Student


"Maxi" wrote:

I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi

here's another way

Sub test2()
Dim stra
Dim i As Long
For i = 1 To Application.CountA([a1:j1])
stra = stra & Cells(1, i).Value & ","
Next
stra = Left(stra, Len(stra) - 1)
Debug.Print stra
End Sub


--


Gary


"Maxi" wrote in message
oups.com...
I have the following numbers in range A1:J1
5 6 15 23 42 43 46 66 71 73

In my VBA code I have a sring variable strA which has a value "1, 2, 3,
4, 5"

I want to replace the value of strA with numbers in range A1:J10.

that means strA which was "1, 2, 3, 4, 5" earlier should become "5, 6,
15, 23, 42" that is cells(0,1).value, cells(0,2).value,
cells(0,3).value, cells(0,4).value, cells(0,5).value

similarly "3, 5, 6, 8, 10" should become "15, 42, 43, 66, 73" that is
cells(0,3).value, cells(0,5).value, cells(0,6).value, cells(0,8).value,
cells(0,10).value

I want a shortest code available, preferably a one liner although I am
not sure whether it is possible or not.

Thanks
Maxi