FOR loop in Excel 1

[sweetleaf]

Hi there,

I was wondering if anyone can help me?
I have an excel sheet which reads data from an oracle db using odbc.
The data is retrieved successfully - and I can see all the rows and columns in the recordset as the data is displayed in the Immediate window just fine.

However, I am having a real tough time setting the values of cells to the recordset data.

So far, I have managed to successfully retrieve and display the data in the Immediate Window, but when I try writing the results to the excel sheet, all 200+ rows instead of filling downwards in the sheet, cyle through row 1 (columns A,B andC).

-any help would be greatly appreciated!

The code below shows my attempt to fill the retrieved data downwards in the excel sheet:

Dim x As Range
Set x = ActiveCell.CurrentRegion
col1 = x.Columns(1).Column
lastCol = col1 + x.Columns.Count - 1
row1 = x.Rows(1).Row
lastRow = row1 + x.Rows.Count - 1


Do While Not rst.EOF
For r = row1 To lastRow Step 1

Set rec = rst("APPCD&quot
Set rec2 = rst("ENG&quot
Set rec3 = rst("FR&quot

Cells(r, 1).Value = rec
If rec2 = 0 Then
Cells(r, 2).Value = 0
End If
If rec3 = 0 Then
Cells(r, 3).Value = 0
End If

Debug.Print rec & ", " & rec2 & ", " & rec3

Next r

rst.MoveNext
Loop

Set get_ivr_records = rst

End Function

 

[RobBroekhuis]

Your for loop is nested inside your do while loop - I don't think that's what you mean to do. You probably want either the for or the do while, not both. Your rst.movenext doesn't happen until the for loop is entirely completed (i.e., after the "next&quot.
Rob

 

[sweetleaf]

Thanks for the response!

The FOR loop, in this case, is meant to manage the rows and columns, and without it there are no rows (r) or columns to traverse.

While the data would be retrieved there'd be no construct to increment rows.

Thanks though!

 

[RobBroekhuis]

Inside your do while loop, you would put the statement

r=r+1

to increment your row counter.
Rob

 

[sweetleaf]

Thanks Rob,

It worked! I have 1 more quick question, perhaps you'll know the answer to..

I have the following code which is supposed to set the value of cells to the recordset data.
Because I am unaware of a 'null-to-zero' function in excel i am doing this manually - by converting nulls to 0's in the if-then-else below.
This is fine and works when rec2 or rec3 contain a null - However, if the values of rec2 or rec3 are not null then nothing gets put into the respective cell (cell will be blank), yet in the Immediate Window the values (ie.3 or 4) are dsiplayed just fine.
Do you know whats causing this?

Thanks again!

If rec2 = 0 Then
Cells(r, 2).Value = 0
ElseIf Cells(r, 2).Value <> 0 Then
Cells(r, 2).Value = rec2
End If
If rec3 = 0 Then
Cells(r, 3).Value = 0
ElseIf Cells(r, 3).Value <> 0 Then
Cells(r, 3).Value = rec3
End If

 

[RobBroekhuis]

If rec2 = 0 Then
Cells(r, 2).Value = 0
ElseIf Cells(r, 2).Value <> 0 Then
Cells(r, 2).Value = rec2
end if

The logic of the code above says that the cell value gets assigned when
a) rec2=0 (or null)
b) the cell contains a value other than zero.

If you want the cell to always be filled, regardless of what's in it already, you can use:

cells(r,2)=iif(isnull(rec2),0,rec2)


Rob

 

[sweetleaf]

Hi Rob,

I tried that and get an &quot;application-defined or object-defined&quot; error when the value is 0..

&quot;application-defined or object-defined&quot; error:

Cells(r, 2) = IIf(IsNull(rec2), 0, rec2)
(r =1 AND rec2 =0)

 

[sweetleaf]

Hi Rob,

I tried that and get an &quot;application-defined or object-defined&quot; error when the value is 0..

&quot;application-defined or object-defined&quot; error:

Cells(r, 2) = IIf(IsNull(rec2), 0, rec2)
(r =1 AND rec2 =0)

 

[RobBroekhuis]

r=1
rec2=0
Cells(r, 2) = IIf(IsNull(rec2), 0, rec2)

this sequence (executed from the immediate window) works fine in my case. Something else must be awry on your end. Try this from the immediate window and see what happens.
Rob