How come VBA if clause fails?

[Zilla]

I have the following subroutine. Why can't I see the passed-in Range's
Cells(x,y).Value?

Sub getDataInfo2(sRange As Range)
Const maxr = 15
Const defSize = 100
Dim row As Integer
Dim col As Integer
Dim i As Integer
Dim size As Integer
Dim buffer(defSize)

' Get data from source range
row = sRange.row
col = sRange.Column
size = 0
For i = 0 To maxr
If sRange.Cells(row, col).Value <> "" Then
' This if() NEVER passes - ???????????????????????
buffer(size) = sRange.Cells(row, col).Value
size = size + 1
End If
col = col + 1
Next i
End Sub


Sub test()
Dim baseBook As Workbook
Dim currSheet As Worksheet
Dim sRange as Range

set baseBook = ThisWorkbook
set currSheet = baseBook.Sheets(2)
currSheet.Activate
set sRange = currSheet.Range("A1:J1")
' I CAN SEE CELL VALUES IN THE RANGE HERE
Call getDataInfo2(sRange)
End Sub

Any clues?

 

[Per Jessen]

I have the following subroutine. Why can't I see the passed-in Range's
Cells(x,y).Value?

Sub getDataInfo2(sRange As Range)
    Const maxr = 15
    Const defSize = 100
    Dim row As Integer
    Dim col As Integer
    Dim i As Integer
    Dim size As Integer
    Dim buffer(defSize)

    ' Get data from source range
    row = sRange.row
    col = sRange.Column
    size = 0
    For i = 0 To maxr
        If sRange.Cells(row, col).Value <> "" Then
        ' This if() NEVER passes - ???????????????????????
            buffer(size) = sRange.Cells(row, col).Value
            size = size + 1
        End If
        col = col + 1
    Next i
End Sub

Sub test()
    Dim baseBook As Workbook
    Dim currSheet As Worksheet
    Dim sRange as Range

   set baseBook = ThisWorkbook
   set currSheet = baseBook.Sheets(2)
   currSheet.Activate
   set sRange = currSheet.Range("A1:J1")
   ' I CAN SEE CELL VALUES IN THE RANGE HERE
   Call getDataInfo2(sRange)
End Sub

Any clues?

Hi

Try this

Sub getDataInfo2(sRange As Range)
Const defSize = 100
Dim size As Integer
Dim buffer(defSize)


' Get data from source range

size = 0
For Each c In sRange
If c.Value <> "" Then
buffer(size) = c.Value
size = size + 1
End If
Next
End Sub

//Per

 

[Zilla]

Hi

Try this

Sub getDataInfo2(sRange As Range)
    Const defSize = 100
    Dim size As Integer
    Dim buffer(defSize)

    ' Get data from source range

    size = 0
    For Each c In sRange
        If c.Value <> "" Then
            buffer(size) = c.Value
            size = size + 1
        End If
    Next
End Sub

//Per- Hide quoted text -

- Show quoted text -

Thanks again Jason. I'll try your code tomorrow. But why didn't my
version work? Just curious...

 

[Zilla]

Thanks again Jason. I'll try your code tomorrow. But why didn't my
version work? Just curious...- Hide quoted text -

- Show quoted text -

Sorry, I meant Pe "Jessen"

 

[JLGWhiz]

But why didn't my
For i = 0 To maxr

If sRange.Cells(row, col).Value <> "" Then
' This if() NEVER passes - ???????????????????????
buffer(size) = sRange.Cells(row, col).Value
size = size + 1
End If
col = col + 1
Next i

:

The For ... Next loop is useless because you do not use the variable i in
the intervening code to do anything. You would need to use it like
Cells(row, i) or
Cells(i, col) depending on which way you want to move.

When you used sRange.Cells(row, col), you essentially create a circular
reference. Cells(row, col) by definition are part of sRange. Remember row =
sRange.Row?
You should not have used sRange as part of the cell designation but just use
the
If Cells(i, col) = etc. Top down Parent/Child would be
Workbook.Sheet.Range or Cell. The cell is a range, so if you use Cells(row,
col) don't use a Range variable and vice versa.

It takes a while to pull all this stuff together, but you're getting there.

 

[Zilla]

Hi

Try this

Sub getDataInfo2(sRange As Range)
    Const defSize = 100
    Dim size As Integer
    Dim buffer(defSize)

    ' Get data from source range

    size = 0
    For Each c In sRange
        If c.Value <> "" Then
            buffer(size) = c.Value
            size = size + 1
        End If
    Next
End Sub

//Per- Hide quoted text -

- Show quoted text -

Ok I just tried it, and I get the opposite effect that is, the if()
clause ALWAYS passes; I know c.Value is sometimes "" (I do a
MsgBox(c.Value) to prove it). Is this the only way to check for an
empty cell?

 

[Zilla]

But why didn't my


For i = 0 To maxr

:

The For ... Next loop is useless because you do not use the variable i in
the intervening code to do anything.  You would need to use it like
Cells(row, i) or
Cells(i, col) depending on which way you want to move.

When you used sRange.Cells(row, col), you essentially create a circular
reference.  Cells(row, col) by definition are part of sRange.  Remember row =
sRange.Row?
You should not have used sRange as part of the cell designation but just use
the
If Cells(i, col) =  etc.   Top down Parent/Child would be
Workbook.Sheet.Range or Cell.  The cell is a range, so if you use Cells(row,
col) don't use a Range variable and vice versa.

It takes a while to pull all this stuff together, but you're getting there..

Oh, I see the coding error now in my orig code - a typical cut and
paste error! The line should be

NOT
col = col + 1

BUT THIS
col = col + i

 

[Zilla]

Ok I just tried it, and I get the opposite effect that is, the if()
clause ALWAYS passes; I know c.Value is sometimes "" (I do a
MsgBox(c.Value) to prove it). Is this the only way to check for an
empty cell?- Hide quoted text -

- Show quoted text -

Ok, I know why - the passing cells have white space, so c.Value <> ""
indeed since c.Value = " " (3 spaces) for example.

 

[Per Jessen]

Ok, I know why - the passing cells have white space, so c.Value <> ""
indeed since c.Value = "   " (3 spaces) for example.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Zilla

Try

If Trim(c.Value) <>...

Regards,

Per

 

[Zilla]

But why didn't my


For i = 0 To maxr

:

The For ... Next loop is useless because you do not use the variable i in
the intervening code to do anything.  You would need to use it like
Cells(row, i) or
Cells(i, col) depending on which way you want to move.

When you used sRange.Cells(row, col), you essentially create a circular
reference.  Cells(row, col) by definition are part of sRange.  Remember row =
sRange.Row?
You should not have used sRange as part of the cell designation but just use
the
If Cells(i, col) =  etc.   Top down Parent/Child would be
Workbook.Sheet.Range or Cell.  The cell is a range, so if you use Cells(row,
col) don't use a Range variable and vice versa.

It takes a while to pull all this stuff together, but you're getting there..

I read your reply again, this time carefully I'm studying more
about Cells and Range objects. Thanks!

 

[Zilla]

Hi Zilla

Try

If Trim(c.Value) <>...

Regards,

Per- Hide quoted text -

- Show quoted text -

Thanks, that was perfect!