CODEWITHSUNDEEP
excelvbatimer
Zachary Johnson
Zachary Johnson

Reputation: 1

VBA Row and Named Column Selection

I very New to VBA and am trying to find out how to take 2 values form the same row and different columns subtract them and give an output for that row. Then repeat that process for set range/ number of rows. I'm trying to make a timer that updates if any values are changed/ manually overwritten.

Im New to this so there's probably a simple fix, I can get it to give the selected row I want but cant get it to give the column if I do .cells for example. Also all the named variables correspond correctly to a excel sheet.

Sub TimeCalcs()
Dim x As Workbook
Set x = Workbooks("DOWNTIME PROGRAM - ZJ.xlsm")

'===================================================================================================================
Set SSButton = x.Sheets("Downtime").Range("timer.button.label") 'This is the cell the button lable is in
Set StartTime = x.Sheets("Downtime").Range("time.stamp.start") 'This is the row for the start time cell (B15)
Set EndTime = x.Sheets("Downtime").Range("time.stamp.end") 'This is the row for the end time cell (H15)
Set TimeDiffrence = x.Sheets("Downtime").Range("time.diffrence") 'This is the row for the duration cell (C15)
Set RowOffSet = x.Sheets("Downtime").Range("count.of.timestamps") 'This is the offset for the rows. It is a value that is stored too keep trak of the number of rows that have been input.
Set OnOffButton = x.Sheets("Downtime").Range("timer.button.label") 'This is the cell the button lable is in

'===================================================================================================================

Dim SDE As Range
Dim cel As Range
Set StartTimeRange = x.Sheets("Downtime").Range("StartTimeRange") 'Turns defind column cells for start time into ranges for the code (B16:B50)
Set EndTimeRange = x.Sheets("Downtime").Range("EndTimeRange") 'Turns defind column cells for end time into ranges for the code (H16:H50)
Set DurationRange = x.Sheets("Downtime").Range("DurationRange")
Set SDE = x.Sheets("Downtime").Range("Durations")

'===================================================================================================================


For Each Row In SDE.Rows

    If IsEmpty(StartTimeRange) = False And IsEmpty(EndTimeRange) = False Then
    DurationRange.Value = EndTimeRange.Value - StartTimeRange.Value
    
    Else
        
    End If
    
Next Row
    
End Sub

Upvotes: 0

Views: 47

Answers (1)

taller
taller

Reputation: 18778

  • Assuming the quantity of cells in DurationRange is same as StartTimeRange.

  • StartTimeRange is a range which has more than one cell, so using IsEmpty(StartTimeRange(i)) to check the i-th cell in the named range.

    Dim i As Long
    Set StartTimeRange = x.Sheets("Downtime").Range("StartTimeRange") 'Turns defind column cells for start time into ranges for the code (B16:B50)
    Set EndTimeRange = x.Sheets("Downtime").Range("EndTimeRange") 'Turns defind column cells for end time into ranges for the code (H16:H50)
    Set DurationRange = x.Sheets("Downtime").Range("DurationRange")
    
    '===================================================================================================================
    
    For i = 1 To DurationRange.Cells.Count
        If IsEmpty(StartTimeRange(i)) = False And IsEmpty(EndTimeRange(i)) = False Then
            DurationRange(i).Value = EndTimeRange(i).Value - StartTimeRange(i).Value
        End If
    Next
  • The If clause could be simplified as
    If Not (IsEmpty(StartTimeRange(i)) Or IsEmpty(EndTimeRange(i))) Then

Upvotes: 0

Related Questions