Concat Variant Values

[br_turnbull]

I have a For Each....Next loop running along with a counter. Within this I have two variants that refer to different cells which vary depending on the value of the counter (+1 everytime a loop is made) and wanting to concatenate the values in the two. As in:

counter = 0

For Each c In Worksheets("Sheet1").Range("A1:A700")

Dim var1 As Variant
Dim var2 As Variant

var 1 = "A" & counter
var 2 = "B" & counter

'Need to concat values in var1 and var 2 here

counter = counter +1

Next

So if var1 = A1, and var2 = B1, i would then want to concatenate the values in those cells. But how can i concat using variants? (i.e. var1.value & " " & var2.value). Is it possible to convert the variants into strings/numerics to solve the problem?

[June Macleod]

Not 100% sure I understood your requirments but here is one option. Note:
you will need to start your counter at 1 rather than zero.

counter = 1

For Each c In Worksheets("Sheet1").Range("A1:A700")

Dim var1 As Variant
Dim var2 As Variant

var1 = "A" & counter
var2 = "B" & counter

> 'Need to concat values in var1 and var 2 here

result = Range("A" & counter) & " " & Range("B" & counter)

> OR

Range("C" & counter).Value = Range("A" & counter) & " " & Range("B" &
counter)


counter = counter +1
Next

[NickHK]

br_turnbull,
Depending whether you want the resulting value in a cell or variable:

Private Sub ConcatToCell_Click()
Dim Destination As Range

For Each Destination In Worksheets("Sheet1").Range("C1:C700")
With Destination
'If you want the value in a cell in column C
.FormulaR1C1 = "=RC[-2] & RC[-1]"
End With
Next

End Sub

Private Sub ConcatToVariable_Click()
Dim Destination As Range
Dim ConcatValue As String

For Each Destination In Worksheets("Sheet1").Range("A1:A700")
With Destination
'Or if you want the value to process in code
ConcatValue = .Value & .Offset(0, 1).Value
Debug.Print ConcatValue
End With
Next

End Sub

NickHK

"br_turnbull" <[email protected]> wrote
in message news:[email protected]...
>
> I have a For Each....Next loop running along with a counter. Within this
> I have two variants that refer to different cells which vary depending
> on the value of the counter ( everytime a loop is made) and wanting
> to concatenate the values in the two. As in:
>
> counter = 0
>
> For Each c In Worksheets("Sheet1").Range("A1:A700")
>
> Dim var1 As Variant
> Dim var2 As Variant
>
> var 1 = "A" & counter
> var 2 = "B" & counter
>
> 'Need to concat values in var1 and var 2 here
>
> counter = counter
>
> Next
>
> So if var1 = A1, and var2 = B1, i would then want to concatenate the
> values in those cells. But how can i concat using variants? (i.e.
> var1.value & " " & var2.value). Is it possible to convert the variants
> into strings/numerics to solve the problem?
>
>
> --
> br_turnbull
> ------------------------------------------------------------------------
> br_turnbull's Profile:
http://www.excelforum.com/member.php...o&userid=27479
> View this thread: http://www.excelforum.com/showthread...hreadid=469910
>