Cell value not showing up in VBA

4
votes

I am trying to read a cell value in an excel with vba. When i try to read it I always get null.

Here is the code snippet

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
i = 3
j = 4

Do While (Len(Trim(Sheet1.Cells(i, 16))) <> 0)

    Sheet2.Cells(4, j) = Sheet1.Cells(i, 16)

    j = j + 1
    i = i + 1

Loop

End Sub

But the dowhile always gets NULL and comes out of the loop. The cell in sheet1 has value in it.

Please help me out here.

3
excelvba
try to change Null into vbNull... - Kazimierz Jawor
This code has lots of problems. You cannot even execute it; first thing to do is writing it properly. The most typical approach for Null in VBA is Nothing (or vbNull) to be used with "Is". Thus your code should better be: Sheet1.Cells(i,16) Is Not Nothing. Nothing/null is for non-instantiated objects, a cell is always instantiated and thus is never null, can be blank/empty (string value = "") and thus your code does not make any sense (use KekuSemau suggestion better). And this question got a +1?! Well... I am not a down-voter myself unless in extreme-cases, but this surely deserves -1s. - varocarbas
Agree with @varocarbas on the use of Nothing Another way Which is my fav If Len(Trim(Sheet1.Cells(i,16).Value)) <> 0 Then This will ignore spaces pressed by mistake in a cell - Siddharth Rout
@SiddharthRout I also like quite a lot the Len(Trim(string)), actually this is what I use always (the adapted version, logically) in .NET. In VBA I tend to use IsEmpty, which has always worked fine for me BUT I have got some not-so-good-results when using IsEmpty with .NET and Excel (via Interop); and thus I am starting to not feel 100% comfortable with IsEmpty, even in VBA. - varocarbas
And one more thing for IsEmpty: When I paste values to turn formulas into fixed values, some cells can be empty but isEmpty is false (they are treated as non-empty with ATL-Cursor-keys). Then possibly ="" can be better sometimes than IsEmpty. - KekuSemau

3 Answers

0
votes

Try 

Do While(Not IsEmpty(Sheet1.Cells(i, 16))

or maybe then rather 

Do Until IsEmpty(Sheet1.Cells(i, 16))
0
votes

I am sure you are aware, but keep in mind the following difference between IsNull and IsEmpty Excel Help reminds us that:

IsNull --> Returns a Boolean value that indicates whether an expression contains no valid data (Null). IsEmpty --> Returns a Boolean value indicating whether a variable has been initialized.

Okay, this being said, I've fixed your problem of not being able to read the values. I placed the range you are looking at into an array variant. Now the msgbox in the loop will return a value for you.

    Sub x()

Dim Arr As Variant
    Arr = Worksheets("Sheet1").Range(Cells(1, 16), Cells(1000, 16)).Value     'You can modify the 1000 by finding the last line, eg - Range.End(xldown).Row

Dim i
    i = 1

Dim CurrentPos
    CurrentPos = Arr(i, 1)

Do Until IsEmpty(Arr(i, 1)) Or IsNull(Arr(i, 1))

    MsgBox CurrentPos

Loop

End Sub

What do you guys think?

0
votes

I think this is what you're trying to do:

Private Sub CommandButton1_Click()

    Sheet1.Range("P3", Sheet1.Cells(Rows.Count, "P").End(xlUp)).Copy
    Sheet2.Range("D4").PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False

End Sub