Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PERCENTILE with an IF Clause | Excel Worksheet Functions | |||
IF Clause | Excel Worksheet Functions | |||
Can I use a between clause or in clause on an IF statement | Excel Programming | |||
IF Clause | Excel Programming | |||
"Between" in an IF clause | Excel Discussion (Misc queries) |