auto sum

[Trevor3007]

Hi,

I dont even know if my request is possible? But here goes. The screenshot below , a customer has this spreadsheet which he gets exported from his companies payroll. As it differs from month to month he cannot just place the apllicable =sum() in the same row as last ime as the value in cols d & e will change.

So, rather than doa nested =if, is there it possible to have VB do it?

Col 'I' would display the total using the rate displayed in col 'd' x the hours in col 'E' x £8.30.

many thanks & good luck.

 

[Shesil]

Hi,

What differs from month to month?

Thanks

 

[Peter_SSs]

is there it possible to have VB do it?

Is this what you mean?
Test with a copy of your workbook.

Sub EnterCalculation()
  With Range("I2:I" & Range("D" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RIGHT(SUBSTITUTE(RC[-5],"" "",REPT("" "",20)),20)*RC[-4]"
    .Offset(.Rows.Count).Resize(1).Formula = "=SUM(" & .Address & ")"
  End With
End Sub

 

[Trevor3007]

Is this what you mean?
Test with a copy of your workbook.

Sub EnterCalculation()
  With Range("I2:I" & Range("D" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RIGHT(SUBSTITUTE(RC[-5],"" "",REPT("" "",20)),20)*RC[-4]"
    .Offset(.Rows.Count).Resize(1).Formula = "=SUM(" & .Address & ")"
  End With
End Sub

works great, but ( my fault) where it states TES Sun Core 1, this would mean e x 1.5

hope you can sort ..thank you.

 

[Peter_SSs]

where it states TES Sun Core 1, this would mean e x 1.5

Is that the only possible anomaly?

Try

Sub EnterCalculation_v2()
  With Range("I2:I" & Range("D" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[-5]=""TES Sun Core 1"",1.5,RIGHT(SUBSTITUTE(RC[-5],"" "",REPT("" "",20)),20))*RC[-4]"
    .Offset(.Rows.Count).Resize(1).Formula = "=SUM(" & .Address & ")"
  End With
End Sub

 

[Trevor3007]

Is this what you mean?
Test with a copy of your workbook.

Sub EnterCalculation()
  With Range("I2:I" & Range("D" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=RIGHT(SUBSTITUTE(RC[-5],"" "",REPT("" "",20)),20)*RC[-4]"
    .Offset(.Rows.Count).Resize(1).Formula = "=SUM(" & .Address & ")"
  End With
End Sub

Hi Peter_SSs
thank you so much for your help. The only abdominally I can see is the total hours in Col I, which needs to be x £8.90 into col j and a total of all ( see screenshot below).

Once I again thank you. I could not of even gotten close to this with out your skills.

 

[Peter_SSs]

Sorry, I missed the extra multiplication. Can you confirm that the extra multiplication is 8.90 as you said in post 6 or 8.30 as you said in post 1?

Also, it would help greatly if in future you could provide any sample data and expected results with XL2BB so that we can easily copy to test with rather than manually typing out the data (with likely mistakes for my typing )

 

[Peter_SSs]

Can you confirm that the extra multiplication is 8.90 as you said in post 6 or 8.30 as you said in post 1?

Here I have used 8.90 but you can easily see where to change it if required.

Sub EnterCalculation_v3()
  With Range("I2:J" & Range("D" & Rows.Count).End(xlUp).Row)
    .Formula = Array("=IF(D2=""TES Sun Core 1"",1.5,RIGHT(SUBSTITUTE(D2,"" "",REPT("" "",20)),20))*E2", "=I2*8.9")
    .Offset(.Rows.Count).Resize(1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
  End With
End Sub

 

[Trevor3007]

Here I have used 8.90 but you can easily see where to change it if required.

Sub EnterCalculation_v3()
  With Range("I2:J" & Range("D" & Rows.Count).End(xlUp).Row)
    .Formula = Array("=IF(D2=""TES Sun Core 1"",1.5,RIGHT(SUBSTITUTE(D2,"" "",REPT("" "",20)),20))*E2", "=I2*8.9")
    .Offset(.Rows.Count).Resize(1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
  End With
End Sub

that will do nicley Sir!!

yes orginally , but i can change that and or show the gut how to. Could 8.90, be calculated using a cell ref rather then within the code, as it will be easier for the guy ( and I am sure others will use) . perhaps promted a message box asking to rate to be inserted or not but still looking to that particual cell IE m2?

thanks again...fantastic

 

[Peter_SSs]

You're welcome. Glad to help.