looping issue

[Pegasus0990]

i cannot figure out why my loop is not working properly. all help is greatly appreciated. when a integer replaces the variable "b" in the code, it works fine.

Public Sub CommandButton1_Click()
HideListBoxes

Dim oDoc As Word.Document

Set oDoc = Application.Documents("C:\word.doc")

Dim oExcel As Excel.Application, myWB As Excel.Workbook

Set oExcel = New Excel.Application
Set myWB = oExcel.Workbooks.Open("C:\excel.xls")

Dim b As Integer

b = (myWB.Sheets("Questions").Range("B20").Value)

For i = 20 To b

oDoc.Bookmarks("bookmark1").Range.Text = myWB.Sheets("Questions").Cells(i, 1) + vbNewLine

Next i

Set myWB = Nothing
End Sub

 

[rjoubert]

Try this...

b = CInt(myWB.Sheets("Questions").Range("B20").Value)

 

[Pegasus0990]

no luck with your suggestion.

 

[jebenson]

Is 'b' actually getting set to a valid value?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Pegasus0990]

in the Excel document that "b" is suppose to pull the data from, the value of that cell is 22. now if i change the code from

For i = 20 To b

to

For i = 20 To 22

then it works fine. so therefore i have no idea what im doing wrong. "b" will be a dynamic value, that is why i have to use it.

 

[jebenson]

Yes, I understand that 'b' needs to be variable, but in the code you posted, if you break and setp through does 'b' actually get set to a valid amount?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Pegasus0990]

excuse my inexperience, but how can i test what the value of "b" is.

now i realize u are talking about the set procedures of the "Debug" option in VB, but other then that, im confused how to get an output shown.

 

[jebenson]

No problem, that's why we're here... to help.

to break and check a value, you can do a number of things.

One way is to throw in a MessageBox to display the value:

b = (myWB.Sheets("Questions").Range("B20").Value)

MsgBox("b = " & b.ToString)


Another way it to set a break on the line of code in question. to do this, put the cursor on the b = (myWB.Sheets("Questions").Range("B20").Value) line, and hit the F9 key. This should highlight the line in a dark red/maroon color. Now when the code is run, execution will pause on this line and wait for you to continue its execution. You can do this by hitting F8 to step through the code one line at a time, of hit F5 to continue execution normally. While in break mode, you can hover your mouse over the 'b' and a popup should appear telling you what the current value of 'b' is. Note that to see the value after the call to the spreadsheet, you will need to step past the b = (myWB.Sheets("Questions").Range("B20").Value) line.

Post back if you need any further help with this.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Pegasus0990]

my issue is resolved, thanx for all help.