Send Data to SPECIFIC Row FROM User Form

[ChrisOK]

Need to SEND data from userform to a "specific row" within a speadsheet table.....based upon the criteria selected by the user within the user form.

Example: User selects selects "2005" from the year drop down box..... and "OCT" from the month drop down box....then enters all their month-end metrics. User hits SUBMIT (sending it to the database/master holding tank -which is simply a spreadsheet )...ready for chart generation....

Right now, my code sends the metrics to the spreadsheet, but goes to the first available row....

I need it to find the row that has: "2005" (col A) and "OCT" (col B) then, LAY IN THE DATA starting at column C....once it has found the appropriate row to lay the data into.....
(see attached image).....data should go to row 14...then lay in starting at Col C....within all the defined cells to the right.........

I've shared this with a lot of people this month looking for a way...but noone seems to know the answer... :cry:
Does anyone know how to alter the below code to make it do the above?


Private Sub cmdCancel_Click()
'when the user clicks cancel it will close out the form
frmRGUserEntry.Hide
End Sub

Private Sub cmdSave_Click()
frmRGUserEntry.Hide
Dim MetricOut As Range

'get to the end of the list so you can begin population
Set MetricOut = Worksheets("UFDATA").Range("C65536").End(xlUp).Offset(1, 0)

'whatevers in text box 1 put into location 0,0 etc
With MetricOut
'CYCLE TIME
.Offset(0, 0) = TextBox1.Text 'column C
.Offset(0, 3) = TextBox2.Text 'column F
'EFFICIENCY
.Offset(0, 9) = TextBox3.Text 'column L
.Offset(0, 10) = TextBox4.Text 'etc.
'TIMELINESS
.Offset(0, 18) = TextBox5.Text
.Offset(0, 19) = TextBox6.Text
'QUALITY
' no input from user needed in this category
'ACTIVITY
.Offset(0, 37) = TextBox10.Text
.Offset(0, 38) = TextBox9.Text
.Offset(0, 39) = TextBox12.Text
.Offset(0, 40) = TextBox11.Text

.Offset(0, -2) = cboYear.Text
.Offset(0, -1) = cboMonth.Text
End With

'now immediately open the next CNA form
frmRGCNA.Show

End Sub


I GREATLY appreciate any guidance you can provide....

[DKenny]

Insert a column after col B and concatenate the values in Cols A&B.


Then use the MATCH worksheet function to find your rownum. The following code snipped should help.

MyRow = Application.WorksheetFunction.Match(list1.Value & list2.Value, Range("C:C"), 0)

[ChrisOK]

Ok, I've inserted the column and have it concatenating..... but am not sure how to incorporate the Match function into the VBA.....and the row of code you provided.... into my exisiting code...... ( I'm pretty new to VBA )... :eek:

Would I put it between the 2 Private Subs as a new sub -- or somehow put it within the code --near the top of the " Private Sub cmdSave_Click() " code?

I really appreciate your guidance and am excited to see how this will look and how it will work.....! :)

If you can post how the new code should look....I can go back in and edit all the column offset references to accont for the newly inserted column....
(assuming all of that code would stay in tact) ?
(image attached showing how the sample file looks now - inclusive of concat)

[DKenny]

Replace you line of code below...


Set MetricOut = Worksheets("UFDATA").Range("C65536").End(xlUp).Offset(1, 0)



with (note: I don't know what the names of your Dropdown boxes are, so you will need to replace my placeholders (YearddBOX and MonthddBOX) with the correct names)


LookupVal = YearddBOX.Value + MonthddBOX.value
RowNum = Application.WorksheetFunction.Match(LookupVal,Worksheets("UFDATA").Range("C:C"),0)
Set MetricOut = Worksheets("UFDATA").Cells(RowNum,4)



You will also need to add the following to your variable definition section at the start of the cmdSave_Click sub


Dim LookupVal as String
Dim RowNum as Long

[ChrisOK]

:confused: -- I edited the code as you directed and then adjusted the offset references, then ran the user form with sample data for 2005 + OCT.....

:D Indeed, it went to row 14 ( the October row ) -- but it's laying in the "2005" and "Oct" -- but shouldnt be -- because I've already set up the spreadsheet to hold all the presets in consecutive order.....

How do I make it lay in only the user input starting in (what's now Col D) after it has located the appropriate row.......Col A and B are already preset...
The user form now needs to scan down your concatenated column for a match and place the user data in there starting at Col D.....

Oh, maybe it's the offsets at the very bottom that need to be adjusted?

Revised Code:

Private Sub cmdCancel_Click()
'when the user clicks cancel it will close out the form
frmRGUserEntry.Hide
End Sub


Private Sub cmdSave_Click()
Dim LookupVal As String
Dim RowNum As Long
frmRGUserEntry.Hide
Dim MetricOut As Range

'get to the end of the list so you can begin populatio
LookupVal = cboYear.Value + cboMonth.Value
RowNum = Application.WorksheetFunction.Match(LookupVal, Worksheets("UFDATA").Range("C:C"), 0)
Set MetricOut = Worksheets("UFDATA").Cells(RowNum, 4)


'whatevers in text box 1 put into location 0,0 etc
With MetricOut
'CYCLE TIME
.Offset(0, 1) = TextBox1.Text 'column C
.Offset(0, 4) = TextBox2.Text 'column F
'EFFICIENCY
.Offset(0, 10) = TextBox3.Text 'column L
.Offset(0, 11) = TextBox4.Text 'etc.
'TIMELINESS
.Offset(0, 19) = TextBox5.Text
.Offset(0, 20) = TextBox6.Text
'QUALITY
' no input from user needed in this category
'ACTIVITY
.Offset(0, 38) = TextBox10.Text
.Offset(0, 39) = TextBox9.Text
.Offset(0, 40) = TextBox12.Text
.Offset(0, 41) = TextBox11.Text

.Offset(0, -2) = cboYear.Text
.Offset(0, -1) = cboMonth.Text
End With

'now immediately open the next CNA form
frmRGCNA.Show

End Sub

[DKenny]

Remove the following two lines from your code. They are just before the End With statement.



.Offset(0, -2) = cboYear.Text
.Offset(0, -1) = cboMonth.Text

[ChrisOK]

Oh! - You beat me to the post ---
I didnt remove the lines you mentioned, however, I did play with all the offsets and believe I've got it working....

Should I leave it the way I have it (in my latest example) -- or remove the lines as you have just noted...? If I remove those lines, is that ALL that needs to happen?
?? Benefit or better functionality by doing it one way vs. another?

I'm SOOOO EXCITED!! -- You are brilliant! :bigyello:
You have no idea how long I've been trying to get someone who (KNOWS HOW) to make this work!
THANK YOU!!!!
I appreciate all your fantastic feed-back!

Here's the latest code ( what I did, not knowing your last post suggestion ):

Private Sub cmdCancel_Click()
'when the user clicks cancel it will close out the form
frmRGUserEntry.Hide
End Sub


Private Sub cmdSave_Click()
Dim LookupVal As String
Dim RowNum As Long
frmRGUserEntry.Hide
Dim MetricOut As Range

'get to the end of the list so you can begin populatio
LookupVal = cboYear.Value + cboMonth.Value
RowNum = Application.WorksheetFunction.Match(LookupVal, Worksheets("UFDATA").Range("C:C"), 0)
Set MetricOut = Worksheets("UFDATA").Cells(RowNum, 4)


'whatevers in text box 1 put into location 0,0 etc
With MetricOut
'CYCLE TIME
.Offset(0, 0) = TextBox1.Text 'column D
.Offset(0, 3) = TextBox2.Text 'column G
'EFFICIENCY
.Offset(0, 9) = TextBox3.Text 'column M
.Offset(0, 10) = TextBox4.Text 'etc.
'TIMELINESS
.Offset(0, 18) = TextBox5.Text
.Offset(0, 19) = TextBox6.Text
'QUALITY
' no input from user needed in this category
'ACTIVITY
.Offset(0, 37) = TextBox10.Text
.Offset(0, 38) = TextBox9.Text
.Offset(0, 39) = TextBox12.Text
.Offset(0, 40) = TextBox11.Text

.Offset(0, -3) = cboYear.Text
.Offset(0, -2) = cboMonth.Text
End With

'now immediately open the next CNA form
frmRGCNA.Show

End Sub