textbox value in one column next to the first item of a listbox in another column

[Doflamingo]

Hi all,

Does anyone know the lines of code that would allow me to put the value of a textbox1 from a userform in the column A just next to the 1st item of the listbox1 that is displayed in the column B ? It drives me crazy..

Here is the code of the listbox 1

For b = 0 To ListBox1.ListCount - 1
With Sheets("Data")
     nextrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
     .Range("B" & nextrow) = ListBox1.List(b)
End With
Next

Here is the code of the textbox value

With Sheets("Data")
     arow = .Range("a" & Rows.Count).End(xlUp).Row + 1
     .Range("a" & arow) = TextBox1.Value
End With

But the textbox value is written in column A, line after line AND NOT next to the 1st item of listbox1 that is displayed in the column B

 

[Fluff]

How about

With Sheets("Data")
   NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
   .Range("A" & NextRow).Value = TextBox1.Value
   For b = 0 To ListBox1.ListCount - 1
      .Range("B" & NextRow) = ListBox1.list(b)
      NextRow = NextRow + 1
   Next b
End With

 

[Doflamingo]

So many thanks @Fluff that works perfectly !!

Just a note, when I activate the commandbutton of the userform, indeed it displays the items in the listbox1 in the column B and the textbox value in the column A. Do you have any idea how to add 2 empty rows at each time I activate the commandbutton, because I would like for each statement of the listbox 1 be separated by 2 empty rows...

I've changed the items in red, but it adds one empty row in the column B between each items of the listbox 2 and I rather would like 2 empty rows at each time I activate the command button. Any ideas ?

For b = 0 To ListBox1.ListCount - 1
With Sheets("Data")
nextrow = .Range("B" & Rows.Count).End(xlUp).Row + 2
.Range("B" & nextrow) = ListBox1.List(b)
End With
Next

 

[Fluff]

Change it to +3

 

[Doflamingo]

Thanks so much @Fluff. That works perfectly!!

Just a last question, I know many of VBA user are against the use of merged cells but do you have any idea how to merge the cells of column A where it is written the textbox value depending of the number of items of listbox 1 written in column B ?

 

[Fluff]

Simple answer is DON'T!
Merged cells are an abomination & will only cause you major problems in the future.

 

[Doflamingo]

I understand, Ok, thanks again a lot for your previous answer, your help and your time

Kind regards

 

[Fluff]

You're welcome & thanks for the feedback