Userform data entry into excel

[austin397]

Hello all,

I am attempting to have my userform enter data into certain cells on my spreadsheet. I need it to enter the data from a text box into a cell. The cell that it goes into depends on the data selected from a list box.

So if in the list box I select the option of "8605", and in the text box is the data "52" then I need it to look at Column A and if it find a match in column A that is "8605", then enter "52" in the cell of the same row and in column D.

In this instance it would find "8605" in cell A193. So in cell D193 I need it to enter "52".

Please help!

 

[dssJones]

I need to make a few assumptions

1) if the option (selected from the list box) is not found then do nothing
2) Column A contains zero cells that are BLANK; hence the first BLANK cell encountered means we have reached the end of list
3) The search and entry actions will be initiated by a click on an ENTRY button.

I will test my solution and be back with you within the next 2 hours.
Please let me know if my assumptions are OK with you, and/or you have additional requirements

 

[Roderick_E]

need

application.match(listboxvalue, sheet#.range("A:A"),0)

This would give you the row number of 8605 for example. I've provided no error checking in case 8605 doesn't exist.

 

[dssJones]

Additional assumptions

4) Column A contains unique values. The routine will NOT make entries on every row with a match; just the first row.
5) You are familiar with UserForms, and know how to add buttons, textboxes, and such

 

[austin397]

Hello dssJones and Roderick,

Thank you for your replies.

As far as your assumptions
1) That is correct.
2) Column A does contain blank cells but the list does not end at the first blank cell. The data goes until row 300 but there are blanks withen where the data has been seperated for organization reason.
3) That is correct.
4) That is correct.
5) That is correct.

I need to make a few assumptions

1) if the option (selected from the list box) is not found then do nothing
2) Column A contains zero cells that are BLANK; hence the first BLANK cell encountered means we have reached the end of list
3) The search and entry actions will be initiated by a click on an ENTRY button.

I will test my solution and be back with you within the next 2 hours.
Please let me know if my assumptions are OK with you, and/or you have additional requirements

Additional assumptions

4) Column A contains unique values. The routine will NOT make entries on every row with a match; just the first row.
5) You are familiar with UserForms, and know how to add buttons, textboxes, and such

Thank you for this Roderick, that is useful information.

need

application.match(listboxvalue, sheet#.range("A:A"),0)

This would give you the row number of 8605 for example. I've provided no error checking in case 8605 doesn't exist.

 

[Roderick_E]

Simply:

rng = "D" & [COLOR=#333333]application.match(listboxvalue, sheet#.range("A:A"),0)[/COLOR]
sheet1.range(rng) = Userform1.textbox1

 

[austin397]

Hello Roderick_E,

Thank you for the reply, I tried this and kept getting the error "Type mismatch".

Simply:

rng = "D" & [COLOR=#333333]application.match(listboxvalue, sheet#.range("A:A"),0)[/COLOR]
sheet1.range(rng) = Userform1.textbox1

 

[Roderick_E]

Well hopefully you adjusted my code
Depending which sheets you are using, something like:

rng = "D" & application.match(Userform1.listbox1.value, sheet1.range("A:A"),0)
sheet1.range(rng) = Userform1.textbox1

 

[austin397]

Yes, I adjusted the sheets and other applicable names. Still gives "Type mismatch" error.

rng = "D" & Application.Match(StandardChannel.Channel.Value, Sheet1.Range("A:A"), 0)   
 Sheet1.Range(rng) = StandardChannel.dShallow

 

[Roderick_E]

Yes, I adjusted the sheets and other applicable names. Still gives "Type mismatch" error.

rng = "D" & Application.Match(StandardChannel.Channel.Value, Sheet1.Range("A:A"), 0)   
 Sheet1.Range(rng) = StandardChannel.dShallow

Sometimes you have to Dim out the other parts, something like

Schan = StandardChannel.Channel.Value
rng = "D" & Application.Match(Schan, Sheet1.Range("A:A"), 0)   
Sheet1.Range(rng) = StandardChannel.dShallow

You might want to check what is being returned by StandardChannel.dShallow
msgbox StandardChannel.dShallow