Variables in a cell reference
 

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub

Variables in a cell reference
 

Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Value = Range("B1").Value & " " & Range("C1").Value
Next x
End Sub

You want the same value in every cell?

maybe
cells(x,1).Value = Cells(x,2).Value & " " & cells(x,3).Value

--
Regards,
Tom Ogilvy


"Carpe Diem" wrote:

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub

Variables in a cell reference
 

Try: Selection.Value = "B1" & Chr(32) & "C1"


"Carpe Diem" wrote:

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub

Variables in a cell reference
 

after rereading, I believe you wanted my second choice, but for completeness

Sub Ne()
Dim y As Long
Dim x As Long
y = Application.COUNTA(Columns(2))
For x = 1 To y
cells(x,1).Value = Cells(x,2).Value & " " & cells(x,3).Value
Next x
End Sub

by the way, CountA(A:A," * ")-1 does what you think it does, but not for
the reason you think. CountA(A:A) suffices. CountA(A:A," * ") counts any
cell with a value and then counts the second argument once. You then
subtracted 1 to remove that, but that is running around in circles. Also, I
would guess your column A is empty??

so I adjusted it to count column B. If I am wrong, change it to Columns(1)

In any event you can use it directly without tying up a cell.
--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Value = Range("B1").Value & " " & Range("C1").Value
Next x
End Sub

You want the same value in every cell?

maybe
cells(x,1).Value = Cells(x,2).Value & " " & cells(x,3).Value

--
Regards,
Tom Ogilvy


"Carpe Diem" wrote:

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub

Variables in a cell reference
 

if he wants the string literal you are building, why not

= "B1 C1"

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote:

Try: Selection.Value = "B1" & Chr(32) & "C1"


"Carpe Diem" wrote:

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub

Variables in a cell reference
 

Thank you Tom. The complete answer you provided works perfectly.

"Tom Ogilvy" wrote:

after rereading, I believe you wanted my second choice, but for completeness

Sub Ne()
Dim y As Long
Dim x As Long
y = Application.COUNTA(Columns(2))
For x = 1 To y
cells(x,1).Value = Cells(x,2).Value & " " & cells(x,3).Value
Next x
End Sub

by the way, CountA(A:A," * ")-1 does what you think it does, but not for
the reason you think. CountA(A:A) suffices. CountA(A:A," * ") counts any
cell with a value and then counts the second argument once. You then
subtracted 1 to remove that, but that is running around in circles. Also, I
would guess your column A is empty??

so I adjusted it to count column B. If I am wrong, change it to Columns(1)

In any event you can use it directly without tying up a cell.
--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Value = Range("B1").Value & " " & Range("C1").Value
Next x
End Sub

You want the same value in every cell?

maybe
cells(x,1).Value = Cells(x,2).Value & " " & cells(x,3).Value

--
Regards,
Tom Ogilvy


"Carpe Diem" wrote:

I would like to know how I could combine cells with variables if possible.
Something similar to the Selection.Value Selection.Value = €œB1& €œ€� €œ€�C1€�
line in the code below but which operates like (x,2)&"" ""&(x,3) so I can
get the appropriate cells to add together with a space in between.

I am likely mixing syntax as I am unfamiliar with VB.
Sub Ne()
Dim y As Integer
Dim x As Integer
Cells(4, 10) = "=COUNTA(A:A,"" * "")-1"
y = Cells(4, 10)
For x = 1 To y
Cells(x, 1).Select
Selection.Value = €œB1& €œ€� €œ€�C1€�
Next x
End Sub