Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference a file by concatenating cell variables | Excel Worksheet Functions | |||
Syntax for using variables in a cell formula to reference paths/fi | Excel Worksheet Functions | |||
using variables to reference sheets | Excel Programming | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) | |||
Using variables to reference columns in VBA | Excel Programming |