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

Danimal81

New Member
Joined
Nov 29, 2019
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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;
VBA Code:
'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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top