CODEWITHSUNDEEP
vbaexcel
user1805430
user1805430

Reputation: 107

Combine and Pass value from cells to another in Excel VBA

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?

Upvotes: 0

Views: 2453

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15571

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

  1. It is convenient to fully qualify ranges, e.g., use Worksheets("Sheet1").Range instead of Range, see this explanation.

  2. To choose a Range you can use direct addressing (as in the comment by chris nielsen) or other options, as Offset, possibly convenient here.

  3. 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.

  4. 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.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

Try

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

Upvotes: 0

Related Questions