VBA intersect function for rows and column

**jay6939** · 07-16-2020, 01:32 PM

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.

Code:

[CODE=vba]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

**DocAElstein** · 07-16-2020, 02:43 PM

**jay6939** · 07-16-2020, 03:01 PM

**DocAElstein** · 07-16-2020, 03:14 PM

**jay6939** · 07-16-2020, 03:26 PM

please find below link,
there are 2 forms. 2nd form gets value from userform3 when you select and click cost updates buttons. i am looking for intersect values from userform3. txtchangenumber and userform1.txtselectpart number in cost spreadsheet updates
https://www.dropbox.com/s/095zbzry2l...est2.xlsm?dl=0

**DocAElstein** · 07-16-2020, 03:43 PM

I can't find your
Private Sub txtdelta_AfterUpdate()
where is it?
( I dont have much experience with UserForms, so maybe that macro is somewhere that I am not familiar at loooking in )

**jay6939** · 07-16-2020, 04:02 PM

it is on Form1 under cost lable
cost.JPG cost.JPG

**DocAElstein** · 07-16-2020, 04:02 PM

I cant find those named ranges either, and in fact there is no sheet with the name "cost" , so I expect you gave me the wrong file.... or my ignorance in UserForms is showing up, because I still cant see how to get at that macro...
The file has a worksheet with the name Form, but there are two UserForms, UserForm2 and UserForm3

I appologise if it is down to my ignorance and lack of experience with such files.
I know VBA quite well
I do have some Userforms of my own and I know how to get at all the coding behind them, but I still can't find your
Private Sub txtdelta_AfterUpdate()

I cant see anything like your last screenshots anywhere in either the worksheets or in the VB Editor of the file ylou have given.... ( The file I have from you has the name Part tracker test2.xlsm )

According to my ( limited ) knowledge of VBA if I have a code line like this
ThisWorkbook.Sheets("Cost")
then I must have a worksheet with a tab name of "Cost" or else that code line will error

I think also, that I must see in the Names manager the named ranges of "vrech and "col" or else
Range("vrech")
and
Range("col")
will error.

But once again, maybe in my ignorance, I am missing something obvious....

**jay6939** · 07-16-2020, 04:25 PM

please find below updated link,
https://www.dropbox.com/s/58c45t68hh...est1.xlsm?dl=0
sorry for incorrect file.

**DocAElstein** · 07-16-2020, 04:42 PM

I think I am out of my depth on this one due to my lack of experience with user forms….
If I try to step through the macro
Private Sub txtdelta_AfterUpdate()
then a UserForm springs up and nothing else happens.

I have never experienced anything ever like that before.
In my experience I can step through a macro to try to find out what and where any problem is.
If I try to do that with your macro , it does not let me step through it. Instead a UserForm pops up.

So I am at a loss on how to go further . Sorry
I don’t know how to find a problem in a macro that I can’t seem to run.
Hope someone else can help.

Alan