Simple Question, Hopefully

[cjdhx9]

Hey all,

I was hoping to get some help with a project. I'm doing an erosion study on a piece of equipment that I work with. I have a wall thickness and an erosion rate. I was hoping made a module that would simply run the equation "thickness - rate * x = 0", where x is time (in minutes) and would increase by increments of 1 until the statement was met. Then return the value x into another cell. Pretty much telling me how many minutes more it would take before the thickness of the part became 0. This is what I've got so far, but I get an error stating "object required" on "x =" in the fourth line.



Sub TimeRemaining()
Dim x As Integer, w As Integer
Dim Thickness As Long, Rate As Long
Set x = 1
Set w = 2
Set Thickness = Range(AFw).Value
Set Rate = Range(ANw).Value
Do Until Thickness - Rate * x = 0
Set x = x + 1
Loop
Range(APw).Activate
Set ActiveCell = x
Set w = w + 1
End Sub

In my module x is the Time I mentioned above, w is the row number. Thickness is the wall thickness of the part, rate is the rate of erosion. Then I set x = 1 and it gives me the error "Object Required". Besides that I'm not sure if the "Set Thickness = Range(AFw).Value" will actually work. The column was AF and I was hoping to be able to just move down one row as I increased the variable w later on. But I'm not sure if that would actually work. I appreciate any help.

[cjdhx9]

Ok, so I think I need to get rid of all the "Set" in the code. But then I get that the "Thickness = Range(AFw).Value" has failed. Which I kind of expected. So I need to keep the same column but move the row down one at a time based on the variable.

[Bob Phillips]

This is what I have come up with



Sub TimeRemaining()
Dim x As Integer, w As Integer
Dim Thickness As Long, Rate As Long
x = 1
w = 2
Thickness = Range(AFw).Value
Rate = Range(ANw).Value
Do Until Thickness - Rate * x = 0
x = x + 1
Loop
Range(APw).vaalue = x
Set w = w + 1
End Sub


but,
what are AFw, ANw and APw?
why do increment w after all the work is done?
wouldn't it be better as a UDF callable from the worksheet where you pass all the value ranges and the answer returns to the calling cell?

[cjdhx9]

Ok, several revisions, heres what I have now


Sub TimeRemaining()
Dim x As Integer, w As Integer
Dim Thickness As Long, Rate As Long
x = 1
w = 3
Thickness = Cells(32, w).Value
Rate = Cells(40, w).Value
Do Until Thickness - Rate * x = 0
x = x + 1
Loop
Cells(42, w).Activate
ActiveCell = x
w = w + 1
End Sub

[cjdhx9]

To be honest, I know very little about VBA. The AFw stuff was supposed to (in my mind) identify the cell I wanted to pull the value from. Where AF is the column, and w is the row. But w needs to increase by one every time the process is done, thus slowling moving down the entire document. But if theres a better way, then please help me out. I'm not garenteeing that I'll understand it though, but I'll try. Thanks for the help.

[mdmackillop]

As presented, x = Thickness/Rate
What more are you trying to achieve?

[cjdhx9]

I'm sorry for the confusion, Let me clarify.

The data for Erosion rate has already been derived from "amount eroded"/"Time used" previously in the document. The Thickness is simply how many mm of material is left AFTER the part has already eroded. I'm trying to calculate a estimated time for how long the part could continue to be used before a hole wears in it. To do this I need to use the formula "Thickness - Rate * x = 0" where x is the "estimated time" before the material thickness reaches 0 and a hole forms. Then I need to report that time (x) in a cell next to the other information. Let me know if that helps or just confuses the issue.

[mdmackillop]

Can you post your workbook? Use Manage Attachments in the Go Advanced reply section.

[Bob Phillips]

Perhaps this



Function TimeRemaining(AFw As Variant, ANw As Variant) As Double
Dim x As Integer, w As Integer
Dim Thickness As Long, Rate As Long

x = 1
w = 2
If TypeName(AFw) = "Range" Then

Thickness = AFw.Value
Else

Thickness = AFw
End If
If TypeName(ANw) = "Range" Then

Rate = ANw.Value
Else

Rate = ANw
End If
Do Until Thickness - Rate * x <= 0
x = x + 1
Loop
TimeRemaining = x
End Function


and in a worksheet cell, use say

=TimeRemaining(B2,C2)

where b2 and c2 are thickness and rate values

[cjdhx9]

Never Mind, I'm completely idiotic. there is no need to write a Macro for this. So dumb. Thanks for the help guys.

[mdmackillop]

So the wood has become visible through the trees?

No problem, but maybe you could post your solution for the benefit of others.

Regards
MD