| 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 - 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? |
|
#4
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. |
|
#5
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 |
|
#6
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! |
|
#7
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... |
| 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) | |||
![Zilla[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
