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