excel VBA Range or cells()?

Question

So, I'm trying to write a user friendly macro for easy sales recording.
I've completed all the input and calculation part, however when I tried to write the result in excel spread sheet using the cell() function, I can only write in string.

Range("A2").Select
ActiveCell.End(xlToRight).Select
lastcolumn = ActiveCell.Column
'MsgBox lastcolumn
If lastcolumn = 16384 Then
lastcolumn = 1
End If
Cells(16, lastcolumn + 1).Select
If lbl_scfe = "" Then
Cells(2, lastcolumn + 1).Value = 0
Else
Cells(2, lastcolumn + 1).Value = lbl_scfe
End If

I've also wrote a short code to test if the range function would work...

Private Sub CommandButton1_Click()
val_cfe_1 = Val(TextBox1)
val_cfe_2 = Val(TextBox2)
coffee = val_cfe_1 * 10 + val_cfe_2
Label1 = coffee
Range("a1") = coffee
Range("a2") = Label1
Cells(2, 1) = coffee
Cells(2, 2) = Label1
End Sub

Both codes had a userform to go with it, the range function output both in double data type, cells(2,2) output in strings, and

Cells(2, 1) = coffee

yield no output in the cell.
So should I assume that Cells() function can only input string? Or am I not using the function correctly...
If I can't use cells() function, then how can I write a range function that can automatically go to the last column and goes to individual row? Thanks

Using Cells(2, 1) = coffee should be fine. Lots of queries/questions: 1. How have you Dim'd coffee, val_cfe_1 and val_cfe_2 (please show us code). 2. What are you getting in cell A1? 3. Have you stepped through the debugger to see what values you are getting for coffee, val_cfe_1 and val_cfe_2? — joehanna

davidleejy davidleejy · Accepted Answer · 2015-09-01T05:46:39

You should use

Cells(2, 1).Value = coffee

instead of just

Cells(2, 1) = coffee

Otherwise you compare the cell with coffee, instead of the cells value.

excel VBA Range or cells()?

1 Answers