How come VBA if clause fails?
 

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?

How come VBA if clause fails?
 

On 17 Jan., 23:10, Zilla wrote:
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

How come VBA if clause fails?
 

On Jan 17, 5:26*pm, Per Jessen wrote:
On 17 Jan., 23:10, Zilla wrote:





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- Hide quoted text -

- Show quoted text -

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

How come VBA if clause fails?
 

On Jan 17, 6:14*pm, Zilla wrote:
On Jan 17, 5:26*pm, Per Jessen wrote:





On 17 Jan., 23:10, Zilla wrote:

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- Hide quoted text -

- Show quoted text -

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" :)

How come VBA if clause fails?
 

But why didn't my
version work? Just curious

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


"Zilla" wrote:

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.

How come VBA if clause fails?
 

On Jan 17, 5:26*pm, Per Jessen wrote:
On 17 Jan., 23:10, Zilla wrote:





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

How come VBA if clause fails?
 

On Jan 17, 7:08*pm, JLGWhiz wrote:
But why didn't my

version work? Just curious

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
"Zilla" wrote:

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

How come VBA if clause fails?
 

On Jan 17, 7:10*pm, Zilla wrote:
On Jan 17, 5:26*pm, Per Jessen wrote:





On 17 Jan., 23:10, Zilla wrote:

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

How come VBA if clause fails?
 

On 18 Jan., 01:39, Zilla wrote:
On Jan 17, 7:10*pm, Zilla wrote:





On Jan 17, 5:26*pm, Per Jessen wrote:

On 17 Jan., 23:10, Zilla wrote:

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- 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?- 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.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Zilla

Try

If Trim(c.Value) <...

Regards,

Per

How come VBA if clause fails?
 

On Jan 17, 7:08*pm, JLGWhiz wrote:
But why didn't my

version work? Just curious

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
"Zilla" wrote:

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!

How come VBA if clause fails?
 

On Jan 17, 7:59*pm, Per Jessen wrote:
On 18 Jan., 01:39, Zilla wrote:





On Jan 17, 7:10*pm, Zilla wrote:

On Jan 17, 5:26*pm, Per Jessen wrote:

On 17 Jan., 23:10, Zilla wrote:

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- 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?- 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.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Zilla

Try

If Trim(c.Value) <...

Regards,

Per- Hide quoted text -

- Show quoted text -

Thanks, that was perfect!