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?

On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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

On Jan 17, 5:26*pm, Per Jessen <perjesse...@hotmail.com> wrote:
> On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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...

On Jan 17, 6:14*pm, Zilla <zill...@bellsouth.net> wrote:
> On Jan 17, 5:26*pm, Per Jessen <perjesse...@hotmail.com> wrote:
>
>
>
>
>
> > On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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"

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.

On Jan 17, 5:26*pm, Per Jessen <perjesse...@hotmail.com> wrote:
> On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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?

On Jan 17, 7:08*pm, JLGWhiz <JLGW...@discussions.microsoft.com> 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

On Jan 17, 7:10*pm, Zilla <zill...@bellsouth.net> wrote:
> On Jan 17, 5:26*pm, Per Jessen <perjesse...@hotmail.com> wrote:
>
>
>
>
>
> > On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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.

On 18 Jan., 01:39, Zilla <zill...@bellsouth.net> wrote:
> On Jan 17, 7:10*pm, Zilla <zill...@bellsouth.net> wrote:
>
>
>
>
>
> > On Jan 17, 5:26*pm, Per Jessen <perjesse...@hotmail.com> wrote:
>
> > > On 17 Jan., 23:10, Zilla <zill...@bellsouth.net> 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

On Jan 17, 7:08*pm, JLGWhiz <JLGW...@discussions.microsoft.com> 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!