Controlling position of data from a Userform (into columns)

[drewberts]

I'm using the following to set an input range to select my first cell to input from a userform (This selects the 1st blank cell in Column I on worksheet called Targets)

Set inputrange = Worksheets("Targets").Cells(Rows.Count, 9).End(xlUp).Offset(1)

I then use the following (to add the correct value from the userform)

inputrange.Value = Textbox1.Value

Followed by (To add the other values from the userform under each other in a column - this is for all the userform values).

inputrange.Offset(1, 0).Value = Textbox2.Value

When I re-use the userform the next set of data gets added underneath the 1st set in the same column (Column I). What I want it to do is find the first blank cell in the next column to the right and add the data in that column as previous. Each userform submission enters the values in the next column from the last. I know the issue is in the Set Inputrange... line of code but I can't work out the correct syntax.

Its probably obvious but I'm going round in circles.

 

[DanteAmor]

What I want it to do is find the first blank cell in the next column to the right and add the data in that column as previous. Each userform submission enters the values in the next column from the last.

Considering that there are always headers in row 1, you could use row 1 as a basis for determining the next available column.
But it will be necessary to put a header every time you use a column:

Try this:

  Dim lr As Long
  Dim lc As Long
 
  With Worksheets("Targets")
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1  ''Fit the row number where you will always have data
    lr = .Cells(Rows.Count, lc).End(xlUp).Row + 1
  
    .Cells(lr, lc).Value = TextBox1.Value
    .Cells(lr + 1, lc).Value = TextBox2.Value
  
    '...
  
    .Cells(1, lc).Value = "New header"     'Fit the row number where you will always have data
  End With

 

[drewberts]

Thanks for the response DanteAmor. The issue with this is that the row above the first blank cell has a formula in which is being used elsewhere so I couldn't "add a header" each time.

 

[DanteAmor]

The issue with this is that the row above the first blank cell has a formula in which is being used elsewhere so I couldn't "add a header" each time

Just delete this line:

.Cells(1, lc).Value = "New header" 'Fit the row number where you will always have data


And adjust the row number where you have the formula in this line:
lc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1 ''Fit the row number where you will always have data

For example, if you have the formula in row 5, then:
lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1 ''Fit the row number where you will always have data

Then:

 With Worksheets("Targets")
    lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1  ''Fit the row number where you will always have data
    lr = .Cells(Rows.Count, lc).End(xlUp).Row + 1
 
    .Cells(lr, lc).Value = TextBox1.Value
    .Cells(lr + 1, lc).Value = TextBox2.Value
 
    '...
 
  End With

 

[drewberts]

Just delete this line:

.Cells(1, lc).Value = "New header" 'Fit the row number where you will always have data


And adjust the row number where you have the formula in this line:
lc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1 ''Fit the row number where you will always have data

For example, if you have the formula in row 5, then:
lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1 ''Fit the row number where you will always have data

Then:

 With Worksheets("Targets")
    lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1  ''Fit the row number where you will always have data
    lr = .Cells(Rows.Count, lc).End(xlUp).Row + 1
 
    .Cells(lr, lc).Value = TextBox1.Value
    .Cells(lr + 1, lc).Value = TextBox2.Value
 
    '...
 
  End With

Thanks again Dante Amor - nearly there - its adding the info to subsequent columns just in the wrong place.

 

[drewberts]

Thanks DanteAmor - it works in as much as bring the data in I just cant get it into the correct position on the sheet (Its not a simple sheet as it has total columns etc on the right hand side so I'm guessing the problem is that its seeing the "true" next blank column rather that the "blank" one I want.

My workaround (which isn't particularly efficient I know) is to bring the data into a different simple sheet and then reference that data into my main sheet. Thanks again

 

[DanteAmor]

it works in as much as bring the data in I just cant get it into the correct position on the sheet (Its not a simple sheet as it has total columns etc on the right hand side so I'm guessing the problem is that its seeing the "true" next blank column rather that the "blank" one I want.

Obviously I don't know how your data is, my idea is to find the next available column, but for that you should explain how your data is or with an image explain how you have your data and in which row they should start putting the data.

For example, if the data is to start in column I in row 6:

  Dim lr As Long
  Dim lc As Long
  
  lr = 6
  lc = Columns("I").Column
  
  With Worksheets("Targets")
  
    Do While .Cells(lr, lc) <> ""
      lc = lc + 1
    Loop
    
    .Cells(lr, lc).Value = TextBox1.Value
    .Cells(lr + 1, lc).Value = TextBox2.Value
    
    '...
    
  End With

Set row 6 and column I with the values where you want to start writing