How to input data from a form text box to a worksheet cell lookup

[Eurekaonide]

Hi I wonder if you can help please as my mind has gone blank!

I have a user form im creating where users can update / add / amend data in the vast number of cells of worksheets, however Im struggling with the VBA code to write for the data to be put into the worksheets from the form that they fill in.

Its a simple form with text boxes currently where they can input data.

The complexity comes from being able to locate the right cell in the worksheet to put the data into.

it has a few conditions to locate the correct cell;

• The ID which is always in Column A but may appear several times
• The Stage which is in Column B and could appear multiple times
• The Instance number which is a sequential number ranging from 1 onwards (these are unique as in 1 only ever appears once against that ID and that Stage)

My formula for looking up the data goes like this;
=LOOKUP(2,1/(('Raw Data'!$A1:$A600=HOME!$B$12)*('Raw Data'!$C1:$C600=HOME!L$20)*('Raw Data'!$H1:$H600=HOME!K$22)),'Raw Data'!$G1:$G600)
But what would I write in VBA to look this up and then place the new data they have put in the text box into the corresponding cell?

Thanks for your assistance

 

[Herakles]

Hi I wonder if you can help please as my mind has gone blank!

I have a user form im creating where users can update / add / amend data in the vast number of cells of worksheets, however Im struggling with the VBA code to write for the data to be put into the worksheets from the form that they fill in.

Its a simple form with text boxes currently where they can input data.

The complexity comes from being able to locate the right cell in the worksheet to put the data into.

it has a few conditions to locate the correct cell;

• The ID which is always in Column A but may appear several times
• The Stage which is in Column B and could appear multiple times
• The Instance number which is a sequential number ranging from 1 onwards (these are unique as in 1 only ever appears once against that ID and that Stage)

My formula for looking up the data goes like this;
=LOOKUP(2,1/(('Raw Data'!$A1:$A600=HOME!$B$12)*('Raw Data'!$C1:$C600=HOME!L$20)*('Raw Data'!$H1:$H600=HOME!K$22)),'Raw Data'!$G1:$G600)
But what would I write in VBA to look this up and then place the new data they have put in the text box into the corresponding cell?

Thanks for your assistance

If you are updating / ammending data then are you populating the userform from the worksheet prior to the updates being made on the userform?

If you are adding rows then you will need a new unique id.

Is the Instance number unique across all rows and not just for a combination of ID and Stage.

A sample of your data using XL2BB would be handy.

A image of your form would also be handy.

 

[Eurekaonide]

Thanks Im not really in a position to upload the data as it is sensitive.
What I have tried to do now is record a macros carrying out the two filters I need but I then need to be able to state I want the selected cell to be for example C35 in the below example - however whilst on this occasion their is data in C35 there may well not be on other occassions so I cant use the VBA code go to the last row. Ive tried asking it to go to the Column then drop down a row whilst filtered but this does not seem to work either.

ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=1, Criteria1:="P-43901"
ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=2, Criteria1:="SG1"
Range("C1" & ActiveCell.Row + 1).Select

So my question is:
1) how do I navigate it to the correct row even if this cell is empty
2) then the data the user has input into the input box needs to be updated into that cell

Thanks

 

[Eurekaonide]

Ive now changed my formula to the below but it states Object required

ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=1, Criteria1:="P-43901"
ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=2, Criteria1:="SG1"
Activeshett.Range("C1").Offset(1).SpecialCells(xlCellTypeVisible).Select

 

[Eurekaonide]

Ive now changed my formula to the below but it states Object required

ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=1, Criteria1:="P-43901"
ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=2, Criteria1:="SG1"
Activeshett.Range("C1").Offset(1).SpecialCells(xlCellTypeVisible).Select

OK I think I have that part sorted by using the below formula

ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=1, Criteria1:="P-43901"
ActiveSheet.Range("$A$1:$BH$645").AutoFilter Field:=2, Criteria1:="SG1"
Range("C2:C645").Select
With Selection
.Offset(1).SpecialCells(xlCellTypeVisible).Activate
End With

Please advise if you think this is incorrect