Unable to reference a dynamic range in VBA

[Stephenosn]

Hello all,

I'm working on a userform and am setting up some insurance the users enter the required data. I have a three text boxes: tbMaterial, tbQty and tbCost. There is a index match function for tbMaterials that fills in the cost if its a material we already have the price for. If we do not have the price then the user needs to fill out the cost. My code doesn't appear to make a difference if tbMaterials value matches a value in the range or not. For Ref_Appliance I'm using the following.

=OFFSET(Inventory!$B$4,,,COUNTA(Inventory!$B:$B),1)

The code I'm trying to use for validation is as follows.

    Private Sub btAdd_Click()    
    Dim k As Integer, i As Integer
    Dim Ref_Appliance As Range
    Dim bMatch As Boolean
    
    For Each Ref_Appliance In Range("B2:B50")    
    If Ref_Appliance.Value = tbMaterial.Value And tbCost <> "" Then
        MsgBox "HEY! No need for price, I got your back pal."
        tbCost = ""
    End If
    If Appliance.Value = tbMaterial.Value And tbQty = "" Then
        MsgBox "Hey lazy hows about givin' me a quantity."
        tbQty.SetFocus
        Exit Sub
    End If
    If Appliance.Value <> tbMaterial.Value And tbCost <> "" And tbQty = "" Then
            MsgBox "Ben you dummy, enter a quantity for your materials."
        tbQty.SetFocus
        Exit Sub
    End If
    If Appliance.Value <> tbMaterial.Value And tbCost = "" And tbQty <> "" Then
            MsgBox "Seriously Ben? A quantity without a cost? As if."
        tbCost.SetFocus
        Exit Sub
    End If
  
    Next

Please let me know if you know of a way to tackle my problem.

Thank you for having a look,

John

 

[MARK858]

Surely everywhere you have

Appliance.Value

it should be

[COLOR="#FF0000"][B]Ref_[/B][/COLOR]Appliance.Value

 

[Stephenosn]

MARK858,

Thank you for catching that. I've made the change there, but am still running into the same problem. My code doesn't actual do any sort of lookup. So I need to be referencing Ref_Appliance differently?

Thanks,

John.

 

[MARK858]

Have you set breakpoints in your code and hovered your mouse over the variables or used debug.print to test exactly what your variables are returning?

Have you tried changing

tbMaterial.Value

to

tbMaterial.Text

and possibly test for an empty box with

If Len(tbCost.Text) = 0

rather than ""

 

[mikerickson]

To refer to the named range Ref_Appliance, you might use syntax like

ThisWorkbook.Names("Ref_Appliance").RefersToRange