Combine and Pass value from cells to another in Excel VBA

Question

Basically, I 'd like to combine values from two cell and then display it in another new cell after I click a button. The tricky part is every time when I enter a new value, it should display in the next row. For example, I shall combine value from A1 and B1 and pass it to C1. the next time I re-enter A1 and B1, the new value should pass to C2. Here is the code I wrote:

Private Sub CommandButton2_Click()

Dim count As Integer
Dim rowNo As String
Dim val As String
Dim val2 As String
Dim sum As String    

count = 1
rowNo = "C" + CStr(count)   

If (Range("A1") <> "" And Range("B1") <> "") Then    
val = Range("A1")
val2 = Range("B1")    
sum = val + "/" + val2    
Worksheets("Sheet1").Range(rowNo).Value = sum
count = count + 1
End If

End Sub

I am new to excel VBA, the above code only write value in A1 and it didnt go to next row when I re-enter the values, can anyone help me to solve this?

When you combine the values with `sum = val + "/" + val2`, the result may be automatically regarded as a date and the target cell formatted accordingly. You may have to explicitly act to prevent/revert this. — sancho.s ReinstateMonicaCellio, Dec 21 '13 at 09:47

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Besides answering your specific question, I will add a few (hopefully useful) comments on your code.

It is convenient to fully qualify ranges, e.g., use Worksheets("Sheet1").Range instead of Range, see this explanation.
To choose a Range you can use direct addressing (as in the comment by chris nielsen) or other options, as Offset, possibly convenient here.
It appears that your code will always go to the same target cell rowNo C1, since count is reset to 1. You will have to let your Sub know where to place the result, and this is key. I guess the safest option is to have one cell in your worksheet set to contain that information, and have your Sub reading it. If you know that column C will only contain the results you want, and that data will be contiguous there, then you could use the code below, which accounts for all items here.
It is often convenient to define a variable for using as reference, e.g., Dim rng as Range and Set rng = Worksheets("Sheet1").Range...

Code below should work.

Private Sub CommandButton2_Click()
    Dim val As String
    Dim val2 As String
    Dim sum As String
    Dim rng As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    With Worksheets("Sheet2")
      If (IsEmpty(.Range("C1"))) Then
        Set rng = .Range("C1")
      Else
        ' There are other options to find the last cell
        Set rng = .Cells(.Rows.count, "C").End(xlUp).Offset(1, 0)
      End If
      If (ws.Range("A1") <> "" And ws.Range("B1") <> "") Then
        val = ws.Range("A1")
        val2 = ws.Range("B1")
        ' Added ' to prevent sum being converted into a date
        'sum = "'" + val + "/" + val2
        sum = "'" & val & "/" & val2
        rng.Value = sum
      End If
    End With
End Sub

PS: there are some variations in the way to select the target range, depending on the contents of your worksheet.

That's awesome! I've learnt sth new, thanks a lot! BTW, what am I supposed to do if I want to retrieve A1 and B1 in sheet1 and display it in C1 WHICH is from sheet2? thought I need to sepcify all each sheet in stead of defining it after with statement? — user1805430, Dec 21 '13 at 10:23
@user1805430 - Please check updated code. You could have based `With...` on Sheet1 and specify explicitly Sheet2 (e.g., with `ws`). — sancho.s ReinstateMonicaCellio, Dec 21 '13 at 10:36
@user1805430 - And, especially since you will be working with different sheets, do not forget [considering this](http://stackoverflow.com/a/20605401/2707864). — sancho.s ReinstateMonicaCellio, Dec 21 '13 at 11:06

score 0 · Answer 2 · answered Dec 21 '13 at 08:35

0

Try

Worksheets("Sheet1").Range(rowNo).Value = sum

answered Dec 21 '13 at 08:35

chris neilsen

52,446
10
84
123

Thx, it works now~however, it does not go to next row when I re-enter the value in cells..Do u know why? I just realize that every time when I click button it will go through "count = 1"..is there any alternatives to avoid this? – user1805430 Dec 21 '13 at 09:06
1

_it does not go to next row when I re-enter the value in cells._ It writes a value to a cell, end of story. If you want to change the active selection, write some more code. Hint: `Range("your range").Select` – chris neilsen Dec 21 '13 at 09:09

Combine and Pass value from cells to another in Excel VBA

2 Answers2