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

[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.