CODEWITHSUNDEEP
vbaexcelif-statementforeach
Will Parker
Will Parker

Reputation: 3

For each loops and Offsets

I am developing a code in VBA and I am having trouble with a task that searches a range in one worksheet for a string; then records the string in the same row but left one column (so .offset(0, -1)) for all occurrences; then it searches for each recorded string in another worksheet and sums the integers corresponding to the recorded strings to the right of the column.

Option Explicit
Option Base 1

Public Sub StoredProcTimes()
    Worksheets("Proc Time").Select
    Dim Table() As Variant, nItem As Integer
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).Name = "ProcTime"
    nItem = Range("ProcTime").Rows.Count
    ReDim Table(nItem, 2)
End Sub

Public Sub DayLoad()
    Range("G2", Range("G1").End(xlDown)).Name = "Dates"
    Call StoredProcTimes
    Dim reply As Date, cell As Range, sum As Integer
    reply = InputBox("Specify Date", "Day Load", "9/1/2017")
    For Each cell In Range("Dates")
        If cell.Value = reply Then
            cell.Offset(0, -1).Value

        End If
    Next
    MsgBox "The load for " & reply & " is " & sum & " minutes"
End Sub

Upvotes: 0

Views: 75

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

For one, your line cell.Offset(0, -1).Value isn't doing anything. You didn't set it equal to a value. You should be receiving an error here.

It's my personal preference not to use Offset(), but it's not necessarily a big deal to use it. In my example, I am showing you an alternate method that I find is favorable.

And for your specific cell that you are trying to find, don't loop through the range, this takes processor time. Use Range.Find().

Public Sub DayLoad()

    Dim ws As Worksheet, rngG As Range

    ' You may need to change the index, or use "SheetName"
    Set ws = ThisWorkbook.Worksheets(1)
    Set rngG = ws.UsedRange.Columns("G")

    'Call keyword is unnecessary 
    Call StoredProcTimes

    Dim sReply As String, myCell As Range
    sReply = InputBox("Specify Date", "Day Load", "9/1/2017")
    If Not IsDate(sReply) Then Exit Sub

    ' Search for your date
    Set myCell = rngG.Find(sReply)

    ' If date was found, then copy the date to the left 1 col
    If Not myCell Is Nothing Then
        With myCell
            ws.Cells(.Row, .Column - 1) = .Value
        End With
    End If

End Sub

Upvotes: 1

Related Questions