VBA intersect row and column code

[jaym6939]

i am writing code to update spreadsheet cell value with intersect function. i have below code that search UserForm3.txtchangenumber in range I1 to AZA1 (worksheet "Cost") and search userform1.txtselectedpart in range E4 to E250(worksheet "Cost"). i would like to populate cell in (worksheet "Cost") when both search intersect by using intersect function.
below is code what i have right now. but it gives me run time erroe 1004 method range of object _global failed.
can you pl help me to understand what is error by correcting below code.

Private Sub txtdelta_AfterUpdate()

Dim vrech As Range
Dim lColumn As Range
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Cost")


'Dim colonne As Long
        'colonne = Me.ComboBox1.Value
Set lColumn = sh.Range("i2:AZa2").Find(UserForm3.txtchangenumber, , xlValues, xlWhole) 'LookIn:=xlValues)
Set vrech = sh.Range("E4:E250").Find(UserForm1.txtselectedpart, , xlValues, xlWhole) 'LookIn:=xlValues)


     Intersect(Range("vrech"), Range("col")) = UserForm1.txtdelta.Value
 


End Sub

 

[jasonb75]

Last line should be
Intersect(vrech, col) = UserForm1.txtdelta.Value
You don't use Range(...) when you have already set a range variable.

Noting that there are no error traps, you will still have problems if either of the searches fails to find the correct range.

 

[jaym6939]

Last line should be
Intersect(vrech, col) = UserForm1.txtdelta.Value
You don't use Range(...) when you have already set a range variable.

Noting that there are no error traps, you will still have problems if either of the searches fails to find the correct range.

Thanks for feedback. after updates it give me an error 424 object required,
can you pl help?
thanks

 

[jasonb75]

You just need to change the last line of your code to the one in my post.

Other than that, your code looks fine, if it still doesn't work then we need to establish the cause of the next error.

 

[jaym6939]

You just need to change the last line of your code to the one in my post.

Other than that, your code looks fine, if it still doesn't work then we need to establish the cause of the next error.

i just updated last line code to below. i found initial error that i have define column range as icoloumn and in last line code has col.
Intersect(vrech, lColumn) = UserForm1.txtdelta.Value
now i am getting new error message, Invalid procedure call or argument (Error 5) with same last line.
can you pl help.
thanks

 

[jasonb75]

Try this instead,
Intersect(vrech.EntireRow, col.EntireColumn) = UserForm1.txtdelta.Value
I thought that I had already seen the entire row an column refs in the search lines which meant that they would not be needed here.

 

[jaym6939]

after updaring to below formula,
Intersect(vrech.EntireRow, lColumn.EntireColumn) = UserForm1.txtdelta.Value = UserForm1.txtdelta.Value
i am getting error message 91 object variable not set.
how do check that my search function works?
thanks
you can find file at below link,
Part tracker test2.xlsm

 

[jasonb75]

Taking a different approach, try

Private Sub txtdelta_AfterUpdate()

Dim vrech As Range
Dim lColumn As Range
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Cost")


'Dim colonne As Long
        'colonne = Me.ComboBox1.Value
Set lColumn = sh.Range("i2:AZa2").Find(UserForm3.txtchangenumber, , xlValues, xlWhole) 'LookIn:=xlValues)
Set vrech = sh.Range("E4:E250").Find(UserForm1.txtselectedpart, , xlValues, xlWhole) 'LookIn:=xlValues)
If lColumn Is Nothing Then
    MsgBox "Column not found"
ElseIF vrech Is Nothing Then
    MsgBox "Row not found"
Else
    Intersect(vrech.EntireRow, lColumn.EntireColumn) = UserForm1.txtdelta.Value
End If
End Sub

 

[jaym6939]

thanks. it gives me error of column not found.
looks like i have problem with UserForm3.txtchangenumber.
below is sequence i am following to update cost delta,
enter change number under userform3.txtchangenumber and click save button. post that select parts from multiselect listbox.
click cost update command button, this will transfer selected parts from userform3 multiselect listbox to userform1 listbox.
when you select lisbox item, it will populate textbox at top.
what i am trying to do with intersect function to search for userform3.textchangenumber in spread sheet cost (I1to AZA1) and intersect with value in userform2 under .textselected part in column E in spread sheet.
do you think there might be issue with saving userform3.txtchangenumber just before updating cost delta?
updated file,
Part tracker test1.xlsm
thansk

 

[jasonb75]

Possibly, I had missed that there were 2 different userforms.
It might be as simple as adding .Value to the end of the textbox name, form code is not something that I use often enough to know the important details without testing it.

Passing the value of the textbox forward as a public variable might be another option that would work.

The day job is about to ruin my forum time but I'll have a look at it in more detail later for you.

@Fluff, if you have time, could you have a quick glance at this thread please, just in case I'm missing something blatantly obvious