Set Find Value from a Worksheet cell

**max57** · 01-30-2010, 05:58 PM

Hello,

I’m having trouble trying to get a form, once activated;
- to use a specified cell value (offset from the target cell),
- to search another sheet for that value,
- then return values, (offset from the find value),
- to a series of textboxes.

Later, if the user if edits any of the textboxes,
to perform the same procedure, pasting those values in the appropriate cells.

Private Sub EditPricing_Activate()

Dim FindCell As Range
Dim LastCell As Range
Dim FoundCell As Range
Dim TargetSheet As Worksheet
Set TargetSheet = ThisWorkbook.ActiveSheet


    With Worksheets("IngredientLists")
    Set LastCell = .Range("B1").End(xlDown)
    Set FindCell = .Range("B1", LastCell).find(TargetSheet.Cells(0, -6).Value, _
    .Range("B1"), xlValues, xlWhole)
        End With
     
        If Not FindCell Is Nothing Then
        Set FoundCell = FindCell
       
     Me.txt_PurUnitMz.SetFocus
     Me.txt_PurUnitMz.Value = FoundCell.Offset(0, 1)
     Me.txt_PurUnitWT.Value = FoundCell.Offset(0, 8)
     Me.txt_PurUnitCost.Value = FoundCell.Offset(0, 9)
     Me.txt_ConvOrg.Value = FoundCell.Offset(0, 11)
     Me.lbl_LastUpdate = FoundCell.Offset(0, 10)

 End If

End Sub

A workbook is attached for illustration.

Thanks -
Mark

**StephenR** · 01-30-2010, 06:49 PM

Try replacing your current code with this:

Private Sub UserForm_Initialize()

Dim FindCell As Range

With Worksheets("IngredientLists")
    Set FindCell = .Range("B1", .Range("B1").End(xlDown)). _
            Find(what:=ActiveCell.Offset(0, -6).Value, LookIn:=xlValues, Lookat:=xlWhole)
End With
 
If Not FindCell Is Nothing Then
    Me.txt_PurUnitMz.SetFocus
    Me.txt_PurUnitMz.Value = FindCell.Offset(0, 1)
    Me.txt_PurUnitWT.Value = FindCell.Offset(0, 8)
    Me.txt_PurUnitCost.Value = FindCell.Offset(0, 9)
    Me.txt_ConvOrg.Value = FindCell.Offset(0, 11)
    Me.lbl_LastUpdate = FindCell.Offset(0, 10)
End If

End Sub