VBA function value problem

[hc002]

The following formula is resulting in "0" in cell H2. The actual result should be 13 which can be determined when place the =LEN(D2) function in the H2 cell. Ultimately I want to run a macro which will count the length of the cell in all hows of the D column and past the result in the H column which didn't work so I created this short test. Can someone tell me the problem with this? Thanks

Sub CountSpaces()
Dim nn As Integer
nn = Len(D2)
Range("H2").Value = nn
End Sub

 

[NeonRedSharpie]

Sub countSpaces()
    For x = 1 To Cells(Rows.Count, "D").End(xlUp).Row
        Cells(x, 8).Value = Len(Cells(x, 4))
    Next x
End Sub

 

[Comfy]

Sub CountSpaces()
Range("H2").Value = Len(Range("D2").Value)
End Sub

The reason yours didn't work is because D2 is seen as an object and you haven't set the value.

Sub CountSpaces2()
Dim nn As Integer
Dim D2 As String
D2 = "123"
nn = Len(D2)
Range("H2").Value = nn
End Sub

 

[adam087]

Here's it is checking the length of the variable D2, which presumably doesn't exist and is therefore zero. You can avoid this btw, by putting OPTION EXPLICIT at the top of your code.

Notwithstanding, you need to reference the range explicitly

nn = Len(Sheets("Sheet1").Range("D2").Value)

Hope that helps.


/AJ

 

[hc002]

Those all worked. Thanks