VBA code, subtracting a value from a non 0 range value

[Danimal81]

Hello All,
I'm new (as in just the last couple of days) to programming in general let alone VBA, but I'm learning. This site has helped me tremendously on a number of code snippets I've been struggling to get right.
Anyway, I'm making my next years time sheet for work as I do every year to record hours I've worked and overtime etc, Previous years have all been based entirely in excel worksheet. This year however I'm going total overboard (for procrastination) and running lots of VB in the background to do donkey work I normally do manually but I'm lazy and don't want to do it anymore.

I'm stuck on this;
At the end of each 7 day week my total hours worked is summed to one cell, for each week of the year.
From each of these cells I need to subtract my normal hours (37) and put that calculated value in the adjacent cell. (told ya I was lazy)

Going through the range and finding the next non 0 total hours value is fine the debug gives the correct values, its the subtraction of the normal hours from the found value that brings up a syntax error but my inexperience I'm not sure what's wrong?
any help or pointers would be greatly appreciated.

Cheers
Dan

The code I have so far;

'find non 0 values and subtract WorkingWeekHrs value from the non 0 cell (TotalHrs) and place result in the adjacent right cell

Application.Volatile True

Dim findrange As Range
Set findrange = Sheet3.Range("C20:C397")

Dim TotalHrs As Range
Set TotalHrs = findrange(1, 1)

Dim WorkingWeekHrs As Integer
WorkingWeekHrs = 37


For Each TotalHrs In Sheet3.Range("C20:C397")
    If TotalHrs.Value > 0 Then
        TotalHrs (,+1) = TotalHrs .Value - WorkingWeekHrs '<this is where I'm stuck, something fundamental I'm doing wrong? I get a compile syntax error
        Exit For
    End If
Next TotalHrs

' Print cell address to Immediate Window(Ctrl + G)
Debug.Print findrange.Address
Debug.Print TotalHrs.Address
Debug.Print TotalHrs.Value

 

[Peter_SSs]

Try removing this space

 

[DanteAmor]

If you want to put the value in the adjacent column you have to put .Offset(, 1), check the following:

Sub test()
  'declaration of variables
  Dim findrange As Range, TotalHrs As Range
  Dim WorkingWeekHrs As Integer
 
  'set values
  Set findrange = Sheet3.Range("C20:C397")
  WorkingWeekHrs = 37
 
  'start loop
  For Each TotalHrs In findrange
    If TotalHrs.Value > 0 Then
        TotalHrs.Offset(, 1) = TotalHrs.Value - WorkingWeekHrs
    End If
  Next TotalHrs
  MsgBox "Done"
End Sub

Or it can also be like this:

Sub test2()
  'declaration of variables
  Dim findrange As Range
  Dim WorkingWeekHrs As Integer
 
  'set values
  Set findrange = Sheet3.Range("C20:C397")
  WorkingWeekHrs = 37
 
  'without loop
  With findrange.Offset(, 1)
    .Formula = "=IF(RC[-1]>0,RC[-1]-" & WorkingWeekHrs & ","""")"
    .Value = .Value
  End With
  MsgBox "Done"
End Sub

 

[Danimal81]

Thankyou gents, knew it would be something simple.
And like your code layout much better Dante?
Cheers

 

[Peter_SSs]

you have to put .Offset(, 1)

Whilst I would use .Offset too, you don't have to use it as the OP's structure can also put the result in the adjacent cell, it just needed to change the 1 to 2

TotalHrs(, 2) = TotalHrs.Value - WorkingWeekHrs

 

[Danimal81]

Thankyou gents, knew it would be something simple.
And like your code layout much better Dante?
Cheers

 

[Danimal81]

Whilst I would use .Offset too, you don't have to use it as the OP's structure can also put the result in the adjacent cell, it just needed to change the 1 to 2

TotalHrs(, 2) = TotalHrs.Value - WorkingWeekHrs

Is that absolute column number?

 

[Peter_SSs]

Is that absolute column number?

No, it is the 2nd column starting at whatever column TotalHrs is in

 

[DanteAmor]

Whilst I would use .Offset too, you don't have to use it as the OP's structure can also put the result in the adjacent cell, it just needed to change the 1 to 2

TotalHrs(, 2) = TotalHrs.Value - WorkingWeekHrs

But the OP did not do, so his code was not correct for the original OP request:

From each of these cells I need to subtract my normal hours (37) and put that calculated value in the adjacent cell.

 

[DanteAmor]

And like your code layout much better Dante?
Cheers

I'm glad to help you. Thanks for the feedback.