Macro formula

[sloan0462]

Hello im in need of a macro which will enter the following formula.
-(T-S)/1.8
this needs to look at the relevant row so if row 8 the formula needs to be.
-(T8-S8)/1.8
if row 9
-(T9-S9)/1.8

i need the macro to look in a certain range for whether or not a cell contains the following (Todays- shipment)
the cell that contains this will have other writing within it but it will always say (Todays- shipment) how it is shown here.

So if (Todays- shipment) is found in a cell between Q8 and Q31 i need it to enter the formula in column R for the relevant row.

to make matters more complicated if the beginning of the cell that contains (Todays- shipment) starts with the word (Time) i need the formula to be

-(T-S)/1.2

Can this be done?

Any help would be great thank you

 

[MB161]

The below will do what you need. Just run it while you have the first cell in column R in the table selected.

Sub AddFormula()


i = 0
Do While ActiveCell.Offset(i, -1) <> ""
ActiveCell.Offset(i, 0).Formula = "=-(T" & ActiveCell.Offset(i, 0).Row & "-S" & ActiveCell.Offset(i, 0).Row & ")/1.8"
i = i + 1
Loop


End Sub

 

[sloan0462]

Thats great however what I'm after is just being able to run the macro without have to have the specific cell selected, any ideas?
Also how does this macro no to look in the column for the cell containing (Daily Shipment) and then put the formula in the row which contains this?

 

[MB161]

The formula above looks to the cell to the left of the current cell [offset(i-1)] to see if it's blank. If it's not then it adds the formula.

If the start cell is always going to be Q8 then add Activesheet.Range("Q8").Activate at the start of the macro. From your message I thought that the range might move.

 

[sloan0462]

Hello sorry, this is my mistake the range is O8 to O31

 

[MB161]

If the today's shipment column is in O and you want the formula in column P then use:

Sub AddFormula()

Activesheet.Range("O3").Activate
i = 0
Do While ActiveCell.Offset(i, 0) <> ""
ActiveCell.Offset(i, 1).Formula = "=-(T" & ActiveCell.Offset(i, 0).Row & "-S" & ActiveCell.Offset(i, 0).Row & ")/1.8"
i = i + 1
Loop

End Sub

If the today's shipment column is in O and you want the still formula in column R then use:

Sub AddFormula()

Activesheet.Range("O3").Activate
i = 0
Do While ActiveCell.Offset(i, 0) <> ""
ActiveCell.Offset(i, 3).Formula = "=-(T" & ActiveCell.Offset(i, 0).Row & "-S" & ActiveCell.Offset(i, 0).Row & ")/1.8"
i = i + 1
Loop

End Sub

 

[sloan0462]

Ok, so i don't what to have to be in a particular cell just to run this, also what happens if the cell contains time at the from of the writing, i.e Time......Daily Shipments....loop if this is the case i need the formula to change from -(T-S)/1.8 to -(T-S)/1.2.

Many Thanks

 

[MB161]

Either code can be run from anywhere as the first thing that the code does is select the start cell. You would only need one of the above.

Please could you elaborate a little more on your second question as I don't understand what you are asking.

 

[sloan0462]

I need the macro to look the range O8 to 031 and if it finds a cell which contains the words Daily ShipmentsI the macro to then enter the formula in that row of column R.

However the cell that contains Daily Shipments will also contain other writing, but Daily Shipments will always be wrote in this order.

Then if it looks and Finds the word tim in this box, (it may or may not) i need the formula to be -(T-S)/1.2 for that given row, where as if it does not find time the original formula will hold i.e.-(T-S)/1.8 for the given row.

The cell that contains the words daily shipments will change from time to time hence the need for the macro to look for it.

Does this make sense?

 

[MB161]

I think this might be what you are after.

Sub AddFormula()


ActiveSheet.Range("O8").Activate
For i = 0 To 24
    If InStr(1, ActiveCell.Offset(i, 0).Text, "Time", vbTextCompare) Then
        ActiveCell.Offset(i, 1).Formula = "=-(T" & ActiveCell.Offset(i, 0).Row & "-S" & ActiveCell.Offset(i, 0).Row & ")/1.2"
    ElseIf InStr(1, ActiveCell.Offset(i, 0).Text, "Daily Shipment", vbTextCompare) Then
        ActiveCell.Offset(i, 1).Formula = "=-(T" & ActiveCell.Offset(i, 0).Row & "-S" & ActiveCell.Offset(i, 0).Row & ")/1.8"
    End If
Next i


End Sub

This code does not enter a formula if it doesn't find either time or daily shipments. If you wanted it to add a formula then you would just need to add another else inside the if.