Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Moving directly left of a field using VBA

    I'm trying to search through a few fields which will have names of employees. Once it finds an employee name I would like it to then look to the left of the employee name cell. Because directly to the left will be how many hours they have worked.

    I've tried using something using i.address but vba doesn't seem to like it at all. Would I be better off just bruteforcing it somehow and examing the value of i.address and having a case statement for if it's c,e,g,i,k or m? Or is there some handy dandy way to actually move around cells?

    For Each c In [c14:c16, e14:e16, g14:g16, i14:i16, k14:k16, m14:m16]
       If IsEmpty(c.Value) = False Then
            strSearchValue = c
            FoundName = True
       End If
       If FoundName = True Then
            For Each i In [c14:c16, e14:e16, g14:g16, i14:i16, k14:k16, m14:m16]
                If UCase(i.Value) = UCase(strSearchValue) Then
                    i.Address(RowAbsolute:=False, _
                    ColumnAbsolute:=False, ReferenceStyle:=xlA1)
                End If
            FoundName = False
        End If
    Also any advice for how I can prevent a name from being looped through twice the when it is say the second time it appears it's read?

    Storing the range in my loops as a variable and then editing the variable so it no longer has the range of a name that has already gone through the process.
    Last edited by Lith; 12-01-03 at 18:41.

  2. #2
    Join Date
    Jan 2003
    Hi, the below codes would solve ur prob. its not exactly for ur case but the concept is it. bascially selection.find and ActiveCell.Offset

    hope this helps

    Private Sub cmbOK_Click()
    Dim Code As String
    Dim n As Integer
    ' Dim tmp1 As String, tmp2 As String
    Code = Trim(Me.cmbCodes.Value)
    n = ((CurDay - 1) * 3) + 1

    If Me.optHTL.Value = True Then
    End If

    Selection.Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, n).Value = Me.txtRmNites.Value
    ActiveCell.Offset(0, n + 2).Value = Me.txtRev.Value

    Me.txtTtlRmNites.Value = Val(Me.txtTtlRmNites.Value) + Val(Me.txtRmNites.Value)
    Me.txtTtlRev.Value = Val(Me.txtTtlRev.Value) + Val(Me.txtRev.Value)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts