Duration Calculation

[ksilvan0418]

I am looking for VBA code to perform the following task..

I have a column for baseline duration, a column for duration and a column for production coding.

I need code to auto figure the following… duration = baseline duration * production coding.

My issue is that both the duration and baseline duration have text and numbers in it. (i.e. 1.5 hrs, 1 day, etc). These columns cannot be split. Production coding only has a number (1, 2, 3, 4 or 5).


Please help!

 

[Gilliam]

Care to provide say, 8-10 rows of data (that represent a good sample of what you currently have)?

 

[ksilvan0418]

Duration	Baseline Duration	Production Coding
	1.5 hrs	2
	1.5 hrs	1
	2 days	1
	2 days	2
	3 hrs	3
	4 hrs	2

<tbody>
</tbody>

 

[ksilvan0418]

Sub DurationCalculation()
'Calculate duration based off of Production Coding
If Summary = No Then
Duration = Duration1 * Number1
End If
End Sub

What I wrote so far.

 

[Gilliam]

This ought to do the trick.
=LEFT(B2,FIND(" ",B2))*C2


Update:
*** Oh, sorry, did you still want VBA Code?

 

[ksilvan0418]

Thanks... but I want it in code. Not a formula, I could have done that myself.

 

[ksilvan0418]

Yes! Definitely... I usually can do it, but this one really has me stumped!

 

[Gilliam]

Yes! Definitely... I usually can do it, but this one really has me stumped!

Something like this?

Sub Duration()    lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For x = 2 To lastRow
        Range("A" & x).Value = Left(Range("B" & x).Value, InStr(1, Range("B" & x).Value, " ", vbTextCompare)) * Range("C" & x).Value
    Next x
End Sub

 

[ksilvan0418]

It doesn't work... but thanks for trying! I appreciate it!

 

[Jonmo1]

It works fine for me
In what way does it not work for you?
Do you get an error? What Error?
Do you get the wrong result? What result DO you get, what result did you expect?

Note that something happened to Gilliam's post, that the first 2 lines of the code got put on the same line..
I don't know why that happens sometimes on this (and some other) forums.

So the first line
Sub Duration() lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

Should be seperated like
Sub Duration()
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row