Program Crashes After Loop

[ggilzow]

I am trying to read data that is columns A5 -> A1000, B5->B1000, and C5->C1000. Then print the values to its respective row in column D. When I run the program through the loop, the program crashes. What am I doing wrong? I am new to VBA so I am on a steep learning curve.

Option Explicit
Sub DR()
Dim FR As Double
Dim Density As Double
Dim Id As Double
Dim m As Double
Dim length As Double
Dim PDC As Double
Dim PDM As Double
Dim Cff As Double
Dim ReN As Double
Dim pi As Double
Dim V As Double
Dim DR As Double

pi = 3.14159265


FR = Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5")
Do
Density = Workbooks("Drag Reduction Testing").ActiveSheet.Range("b5")
PDM = Workbooks("Drag Reduction Testing").ActiveSheet.Range("c5")
Id = Workbooks("Drag Reduction Testing").ActiveSheet.Range("b1")
m = Workbooks("Drag Reduction Testing").ActiveSheet.Range("b3")
length = Workbooks("Drag Reduction Testing").ActiveSheet.Range("d1")
V = 0.00222800926 * FR / (pi * (Id / 12) ^ 2) * 4
ReN = 928 * V * Id * Density / m
Cff = 0.3164 / ReN ^ 0.25
PDC = Cff * V ^ 2 * Density * length / (25.8 * Id)
DR = (PDM - PDC) / PDC * 100
Range("d5").Value = DR
Loop Until IsEmpty(FR)



End Sub

 

[Jonmo1]

Welcome to the board..

A couple things...

1. IsEmpty(FR) will never be true because it is dimmed as a Double.
A variable dimmed as Double will = 0 (not empty) untill it is assigned a value.
And then when it is assigned a value, it is still not empty.

Try

Dim FR As Range
...
SET FR = Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5")

Now IsEmpty(FR) will work as you intended.


HOWEVER,
2. If Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5") is a FORMULA
It will NEVER be Empty...it contains a formula, therefor not empty.
Even if that formula is returning "", that is NOT blank/empty, it's a null text string.

Try

Loop Until Len(FR) = 0



Hope that helps.

 

[ggilzow]

Welcome to the board..

A couple things...

1. IsEmpty(FR) will never be true because it is dimmed as a Double.
A variable dimmed as Double will = 0 (not empty) untill it is assigned a value.
And then when it is assigned a value, it is still not empty.

Try

Dim FR As Range
...
SET FR = Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5")

Now IsEmpty(FR) will work as you intended.


HOWEVER,
2. If Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5") is a FORMULA
It will NEVER be Empty...it contains a formula, therefor not empty.
Even if that formula is returning "", that is NOT blank/empty, it's a null text string.

Try

Loop Until Len(FR) = 0



Hope that helps.

I tried the method, but Excel still gives me the "Non Responsive" Message. I see the value, but not able to use Excel after it calculates the value.

 

[Jonmo1]

OK..

Post the code as you have it now, with my suggestions.

AND
What is in each of the referenced cells..
Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5") - and B5 C5 B1 B3 D1


Also, I notice you have one last range reference at the end, but without a sheet qualifier on it..

Range("d5").Value = DR

That will be referring to whatever sheet is currently selected at the time you run the macro. NOT necessarily the sheet you intend it to refer to.


And finally, drop all the ActiveSheet references...

FR = Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5")
should just be
FR = Workbooks("Drag Reduction Testing").Range("A5")

 

[ggilzow]

OK..

Post the code as you have it now, with my suggestions.

AND
What is in each of the referenced cells..
Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5") - and B5 C5 B1 B3 D1


Also, I notice you have one last range reference at the end, but without a sheet qualifier on it..

Range("d5").Value = DR

That will be referring to whatever sheet is currently selected at the time you run the macro. NOT necessarily the sheet you intend it to refer to.


And finally, drop all the ActiveSheet references...

FR = Workbooks("Drag Reduction Testing").ActiveSheet.Range("A5")
should just be
FR = Workbooks("Drag Reduction Testing").Range("A5")

Option Explicit
Sub DR()
Dim FR As Range
Dim Density As Double
Dim Id As Double
Dim m As Double
Dim length As Double
Dim PDC As Double
Dim PDM As Double
Dim Cff As Double
Dim ReN As Double
Dim pi As Double
Dim V As Double
Dim DR As Double

pi = 3.14159265

Set FR = Workbooks("Drag Reduction Testing").Range("A5")
Do
Density = Workbooks("Drag Reduction Testing").Range("b5")
PDM = Workbooks("Drag Reduction Testing").Range("c5")
Id = Workbooks("Drag Reduction Testing").Range("b1")
m = Workbooks("Drag Reduction Testing").Range("b3")
length = Workbooks("Drag Reduction Testing").Range("d1")
V = 0.00222800926 * FR / (pi * (Id / 12) ^ 2) * 4
ReN = 928 * V * Id * Density / m
Cff = (0.3164 / ReN ^ 0.25) / 4
PDC = Cff * V ^ 2 * Density * length / (25.8 * Id)
DR = (PDM - PDC) / PDC * 100
Range("d5").Value = DR
Loop Until Len(FR) = 0


End Sub

A5= 10.02
B1= 0.4
B3 = 1
B5= 8.688
C5= 24.81
D1= 10
Print to D5

 

[jreedich]

I believe that from your description you need to change your code because your program never changes reference.
I am not sure I interpretted your original post properly. If this is not what you want to do then I sorry for the misunderstanding.

Try This
Sub DR()
Dim FR As Double
Dim Density As Double
Dim Id As Double
Dim m As Double
Dim length As Double
Dim PDC As Double
Dim PDM As Double
Dim Cff As Double
Dim ReN As Double
Dim pi As Double
Dim V As Double
Dim DR As Double
Dim pvOffset As Integer

pi = 3.14159265

'Set FR = Range("A5")
pvOffset = 5
FR = Cells(pvOffset, 1)
Do Until FR = 0
Density = Cells(pvOffset, 2)
PDM = Cells(pvOffset, 3)
Id = Range("b1")
m = Range("b3")
length = Range("d1")
V = 0.00222800926 * FR / (pi * (Id / 12) ^ 2) * 4
ReN = 928 * V * Id * Density / m
Cff = (0.3164 / ReN ^ 0.25) / 4
PDC = Cff * V ^ 2 * Density * length / (25.8 * Id)
DR = (PDM - PDC) / PDC * 100
Cells(pvOffset, 4).Value = DR
pvOffset = pvOffset + 1
FR = Cells(pvOffset, 1)
Loop

End Sub

 

[ggilzow]

i believe that from your description you need to change your code because your program never changes reference.
I am not sure i interpretted your original post properly. If this is not what you want to do then i sorry for the misunderstanding.

Try this
sub dr()
dim fr as double
dim density as double
dim id as double
dim m as double
dim length as double
dim pdc as double
dim pdm as double
dim cff as double
dim ren as double
dim pi as double
dim v as double
dim dr as double
dim pvoffset as integer

pi = 3.14159265

'set fr = range("a5")
pvoffset = 5
fr = cells(pvoffset, 1)
do until fr = 0
density = cells(pvoffset, 2)
pdm = cells(pvoffset, 3)
id = range("b1")
m = range("b3")
length = range("d1")
v = 0.00222800926 * fr / (pi * (id / 12) ^ 2) * 4
ren = 928 * v * id * density / m
cff = (0.3164 / ren ^ 0.25) / 4
pdc = cff * v ^ 2 * density * length / (25.8 * id)
dr = (pdm - pdc) / pdc * 100
cells(pvoffset, 4).value = dr
pvoffset = pvoffset + 1
fr = cells(pvoffset, 1)
loop

end sub

it works!!