Cell value not displayed in VBA

Question

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

Here is a snippet of code

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 dowhile always gets NULL and exits the loop. The cell in sheet1 has a value in it.

Please help me here.

Answer 1

Try

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

or maybe sooner

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

Answer 2

I'm sure you know, but keep in mind that the following difference between IsNull and IsEmpty Excel Help reminds us that:

IsNull → Returns a boolean value indicating whether the expression contains invalid data (Null). IsEmpty → Returns a boolean value indicating whether the variable was initialized.

Well, that being said, I fixed your problem of inability to read values. I put the range you are looking at into an array variant. Now msgbox in a loop will return you the value.

  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?

Answer 3

I think this is what you are 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