Put data from userform into specific place.

[rikapple]

I have a user form which the user is entering data into a large spreadsheet.

The first part of the userform is a combobox to enter the week number. This is the key which dictates where the remaining data needs to go.

On a separate sheet I have a range (G12:AF12). What I am trying to do is to have the input from the Combobox to dictate which row the data should be stored.

For example if the combobox value is 42 then the macro will look in the range G12:af12, when it finds the row with 42 in it, the remaining entered data from the userform will be entered in this row.

I have tried the following but it does not work and seems a lot of coding for something so simple.


Private Sub CommandButton1_Click()
Sheets("kpi data").Activate
If Cells(12, 7).Value = ComboBox1.Value Then Cells(28, 7).Value = TextBox1.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(30, 7).Value = TextBox2.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(27, 7).Value = TextBox3.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(15, 7).Value = TextBox4.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(16, 7).Value = TextBox5.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(20, 7).Value = TextBox6.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(19, 7).Value = TextBox7.Value
If Cells(12, 7).Value = ComboBox1.Value Then Cells(23, 7).Value = TextBox8.Value
MsgBox ("Data Submitted, thank you")
Sheets("Mainscreen").Activate
UserForm1.Hide

End Sub

As you can tell I am new to VBA and any help would be gratefully received.

Thank you in advance:yes

[SamT]

will look in the range G12:af12, when it finds the row with 42

that statement is nonsense in Excel. "G12:AF12" is a single Row so you can't find the "Row" with 42 in it.

Your code puts all the values in a single column ("G")

BTW, here is a Better constructed version or your code

Private Sub CommandButton1_Click()
With Sheets("kpi data")
If .Cells(12, 7).Value = ComboBox1.Value Then
.Cells(28, 7).Value = TextBox1.Value 'Range("G28")
.Cells(30, 7).Value = TextBox2.Value 'Range("G30")
.Cells(27, 7).Value = TextBox3.Value
.Cells(15, 7).Value = TextBox4.Value
.Cells(16, 7).Value = TextBox5.Value
.Cells(20, 7).Value = TextBox6.Value
.Cells(19, 7).Value = TextBox7.Value
.Cells(23, 7).Value = TextBox8.Value
End If
End With

And here is the (IMO) best version

With Sheets("kpi data").Columns(7) 'With Column("G")
If .Cells(12) = ComboBox1.Value Then
.Cells(28) = TextBox1 '("G28")
.Cells(30) = TextBox2 '("G30")
.Cells(27) = TextBox3
.Cells(15) = TextBox4
.Cells(16) = TextBox5
.Cells(20) = TextBox6
.Cells(19) = TextBox7
.Cells(23) = TextBox8
End If
End With

Obviously, neither of those will actually do what you want, because I don't know what you want.

[SamT]

this might be what you are looking for. IT looks for the value selected in ComboBox1 in the range (G12:AF12) and gets that column number.
Then it goes thru the TextBoxes and puts their values in the rows you used in your code.


Private Sub CommandButton1_Click()
Dim ColNum As Long
ColNum = Sheets("kpi data").Range("G12:AF12").Find(Combobox1).Column

With Sheets("kpi data").Columns(ColNum)
.Cells(28).Value = TextBox1.Value
.Cells(30).Value = TextBox2.Value
.Cells(27).Value = TextBox3.Value
.Cells(15).Value = TextBox4.Value
.Cells(16).Value = TextBox5.Value
.Cells(20).Value = TextBox6.Value
.Cells(19).Value = TextBox7.Value
.Cells(23).Value = TextBox8.Value
End With

MsgBox ("Data Submitted, thank you")
Sheets("Mainscreen").Activate
Me.Hide 'Me = Parent Object (Userform1 in this case)

End Sub

[rikapple]

this might be what you are looking for. IT looks for the value selected in ComboBox1 in the range (G12:AF12) and gets that column number.
Then it goes thru the TextBoxes and puts their values in the rows you used in your code.


Private Sub CommandButton1_Click()
Dim ColNum As Long
ColNum = Sheets("kpi data").Range("G12:AF12").Find(Combobox1).Column

With Sheets("kpi data").Columns(ColNum)
.Cells(28).Value = TextBox1.Value
.Cells(30).Value = TextBox2.Value
.Cells(27).Value = TextBox3.Value
.Cells(15).Value = TextBox4.Value
.Cells(16).Value = TextBox5.Value
.Cells(20).Value = TextBox6.Value
.Cells(19).Value = TextBox7.Value
.Cells(23).Value = TextBox8.Value
End With

MsgBox ("Data Submitted, thank you")
Sheets("Mainscreen").Activate
Me.Hide 'Me = Parent Object (Userform1 in this case)

End Sub

Hi

I much appreciate your reply your second answer captures what I am trying to achieve, although I am getting a "runtime error 91" Object variable or with block variable not set error on the 2nd line in (col num = .....)

Your help is gratefully appreciated.

[SamT]

Try this. I'll hang around a bit for a quick answer

Option Explicit

Private Sub CommandButton1_Click()
Dim ColNum As Long

''''Error testing routine. Delete when done
On Error Resume Next
If Not Sheets("kpi data") Is Nothing Then MsgBox "Good Sheet"
If Error <> 0 Then MsgBox "Problem with Sheet"
Error = 0


On Error Resume Next
Dim X
X = CLng(Combobox1.Value)
If Not X > 0 And X < 53 Then MsgBox "Problem with ComboBox1"
Error = 0

On Error Resume Next
Dim Y
For Y = 1 To 52
Set X = Sheets("kpi data").Range("G12:AF12").Find(Y)
If X = Nothing Then MsgBox "Problem with Range"
Next Y
Error = 0
Y = Nothing
Set X = Nothing 'X is a Range Object after the last test
''''End error Testing routine

ColNum = Sheets("kpi data").Range("G12:AF12") _
.Find(CLng(Combobox1.Value), LookIn:=xlValues).Column

With Sheets("kpi data").Columns(ColNum)
.Cells(28).Value = TextBox1.Value
.Cells(30).Value = TextBox2.Value
.Cells(27).Value = TextBox3.Value
.Cells(15).Value = TextBox4.Value
.Cells(16).Value = TextBox5.Value
.Cells(20).Value = TextBox6.Value
.Cells(19).Value = TextBox7.Value
.Cells(23).Value = TextBox8.Value
End With

MsgBox ("Data Submitted, thank you")
Sheets("Mainscreen").Activate
Me.Hide 'Me = Parent Object (Userform1 in this case)

End Sub

[rikapple]

Thank you for your help if anyone else has this problem this is the solution that worked for me


Private Sub CommandButton1_Click()
Dim ColNum As Variant
ColNum = Application.Match(CLng(ComboBox1.Value), Sheets("KPIData").Range("G12:AF12"), 0)
If IsError(ColNum) Then
MsgBox ComboBox1.Value & " not found."
Exit Sub
End If
ColNum = ColNum + 6 ' take into account range starts at G2, not A2
With Sheets("KPIData").Columns(ColNum)
.Cells(28).Value = TextBox1.Value
.Cells(30).Value = TextBox2.Value
.Cells(27).Value = TextBox3.Value
.Cells(15).Value = TextBox4.Value
.Cells(16).Value = TextBox5.Value
.Cells(20).Value = TextBox6.Value
.Cells(19).Value = TextBox7.Value
.Cells(23).Value = TextBox8.Value
End With

MsgBox ("Data Submitted, thank you")
Sheets("Mainscreen").Activate
Me.Hide

End Sub

[SamT]

Nice :) Good commenting, too.