How come VBA if clause fails?

Z

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?
 
P

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?
Click to expand...

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
 
Z

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 -
Click to expand...

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

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 -
Click to expand...

Sorry, I meant Pe "Jessen" :)
 
J

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
Click to expand...


:

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.
 
Z

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 -
Click to expand...

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?
 
Z

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..
Click to expand...

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
 
Z

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 -
Click to expand...

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

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 -
Click to expand...

Hi Zilla

Try

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

Regards,

Per
 
Z

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..
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VB to capture the current cells row number 4
Help with VBA runtime error 5
Excel Visual Basic to get current row number 1
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
Loop thru xx No. of sheets until the four strValue combo's are found 6
Excel VB to create and send an outlook appointment with html code 1
Shed some light on this WS_Calculate macro. 3
Help in sorting data 2

Top