VBA in excel: EASY question for you

[kleen00]

Here's my question, just scroll to the 'While Loop', and look at:

distance = Sheets("QMP-Auto").Range("C[range0]").Value

The C column has values from 0-1500, which i call 'distance'. I want to keep adding .01 to 't60auto' until 'distance' gets above 60, that way i can use the inital value of t60auto when distance > 60.

I think everything is fine until i get to Range, then it screws up... HELP!

Thanks!

-----------

Sub sixtyauto()

Dim dt
Dim rangeadd
Dim t60
Dim distance
Dim range0

dt = 0.01
rangeadd = 1
t60auto = 0
distance = 0
range0 = 3

While distance < 60
t60auto = t60auto + dt
distance = Sheets("QMP-Auto").Range("C[range0]").Value
range0 = range0 + 1
Wend

End Sub

[RobDog888]

Welcome to the Forums.

You have your variable enclosed in the double quotes so its evaluated as a string and not a variable.

distance = Sheets("QMP-Auto").Range("C[" & range0 & "]").Value

also, you should dim your variables as the needed type and not as Variants (no type specified will be dimmed as a Variant). Use Integer for range0 and Single for t60.

[kleen00]

Holy crap that was fast. Thanks Rob! This place may become my new home for the next few months.

[kleen00]

Still not working... Check it out again please. Thanks!!!! I'm slow, sorry about this...

--------------

Sub sixtyauto()

Dim dt As Single
Dim rangeadd As Integer
Dim t60auto As Single
Dim distance As Single
Dim range0 As Integer

dt = 0.01
rangeadd = 1
t60auto = 0
distance = 0
range0 = 3

While distance < 60
t60auto = t60auto + dt
distance = Sheets("QMP-Auto").Range("C['range0']").Value
range0 = range0 + 1
Wend

End Sub

[RobDog888]

Sorry for the delay, posting

You used single quotes instead of double quotes like I posted. The single quotes wrapped around with double quotes still makes vba look at it as a complete string and not a variable.

[kleen00]

While distance < 60
t60auto = t60auto + dt
distance = Sheets("QMP-Auto").Range("C[" & range0 & "]").Value
range0 = range0 + 1
Wend

THis still didn't work... =/ Any suggestions?

[RobDog888]

Did you change your Dim statements to include As Integer for range0?

Dim range0 As Integer

[kleen00]

Yup heres my entire snip:

Sub sixtyauto()

Dim dt As Single
Dim rangeadd As Integer
Dim t60auto As Single
Dim distance As Single
Dim range0 As Integer

dt = 0.01
rangeadd = 1
t60auto = 0
distance = 0
range0 = 3

While distance < 60

distance = Sheets("QMP-Auto").Range("C[" & range0 & "]").Value
range0 = range0 + 1
t60auto = t60auto + dt
Wend

End Sub

[RobDog888]

Ok, looks like you have the range parameter incorrect.

distance = Workbooks(1).Sheets("Sheet1").Range("C" & range0).Value

Sub sixtyauto()

    Dim dt As Single
    Dim rangeadd As Integer
    Dim t60auto As Single
    Dim distance As Single
    Dim range0 As Integer
    
    dt = 0.01
    rangeadd = 1
    t60auto = 0
    distance = 0
    range0 = 3
    
    While distance < 60
        distance = Sheets("QMP-Auto").Range("C" & range0).Value
        range0 = range0 + 1
        t60auto = t60auto + dt
    Wend

End Sub

[kleen00]

Mismatch Error on the Range line...

=/

[RobDog888]

Really? It ran fine for me in Excel 2003. What version are you running?

[RobDog888]

You could use a CStr conversion on the Integer variable to see f thats it.

distance = Sheets("QMP-Auto").Range("C" & CStr(range0)).Value

[kleen00]

Excel 2002.... Kinda strange though, I didn't realize there was a 2002 till just now..

With the CStr version, i get a 'subscript out of range' error. Maybe it's my version??

[RobDog888]

You have it exactly aas I had posted in #9?

Where is the code located? Behind the sheet or in the ThisWorkbook class?

[kleen00]

In a module...

[RobDog888]

Then you should reference the workbooks collection also in this line for better programming practice. Change the workbook to your workbooks name.

distance = Workbooks("Book1.xls").Sheets("QMP-Auto").Range("C" & range0).Value

Now to figure out why 2002 is acting different then 2003 lets try placing the entire range identifier into a variable instead.

Sub sixtyauto()

    Dim dt As Single
    Dim rangeadd As Integer
    Dim t60auto As Single
    Dim distance As Single
    Dim range0 As Integer
    Dim rangeTemp As String
    dt = 0.01
    rangeadd = 1
    t60auto = 0
    distance = 0
    range0 = 3
    rangeTemp = "C3"

    Do While distance < 60
        distance = Workbooks("Book1.xls").Sheets("QMP-Auto").Range(rangeTemp).Value
        If distance = 0 Then Exit Do
        range0 = range0 + 1
        rangeTemp = "C" & CStr(range0)
        t60auto = t60auto + dt
    Loop

End Sub

[mikeyc1204]

I just looked at it quickly, but might it have something to do with the source data? You have values being read into "distance" type single. Maybe your loop is hitting a string or reaching the end of the column and reading a null value? Both would kick out a mismatch I think.

[RobDog888]

Yes, thats true. Good spot mikey. I assumed that there would be integer type data in the cells.

[billhuard]

here is how you should write it and should works fine

distance = Sheets("QMP-Auto").Range("C" & range0 & "").Value