0

I am trying to fill a range of cells from a single ComboBox and I am not sure why my modified code of declaring range from cell down to last used row doesn't work when I flip it horizontally and declare range from cell to last used column on the right of the specified cell which is "C1"

    Dim LastColumn As Long
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("Machine Format").Range("C2" & LastColumn).Value = UserForm_Home.Language_ComboBox.Text

I am judging if it's the last used column by it's header row which won't be blank.

Could someone, please, help me with this code?

Eduards
  • 68
  • 2
  • 20
  • Hmm, it fills range "C2:C5" for some reason – Eduards Apr 22 '21 at 17:46
  • Do you want filling of all the range `"C2:C" & lastRow`? Or to place the combo value in the last cell of C:C column. For the first case, please follow BigBen suggestion, for the second case, please use `Range("C" & LastRow).Value`. If you want to place it on the first empty cell, you should use `Range("C" & LastRow + 1).Value`. For the last empty column `.Range("C2", .Cells(2, LastColumn + 1))`... – FaneDuru Apr 22 '21 at 17:47
  • Sorry, `.Range("C2", .Cells(2, LastColumn))`. – BigBen Apr 22 '21 at 17:48
  • @BigBen Thank you, mate! Your code has a mistake, but it still helped me! Code should be .Range("C2", Cells(2, LastColumn)) – Eduards Apr 22 '21 at 17:50

1 Answers1

1

Since you're working with the column index, use Cells instead of Range.

With Worksheets("Machine Format")
    .Range("C2", .Cells(2, LastColumn)).Value = UserForm_Home.Language_ComboBox.Text
End WIth

Use With Worksheets("Machine Format") and a period . before Cells to properly qualify the worksheet for the Cells call.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Thank you, mate! Your code has a mistake, but it still helped me! Code should be .Range("C2", Cells(2, LastColumn)) – Eduards Apr 22 '21 at 17:52
  • @Eduards - use `With`, and yes include the `.` before `Cells`. – BigBen Apr 22 '21 at 17:53
  • No need for with. It works like intended :) – Eduards Apr 22 '21 at 17:54
  • 1
    No it doesn't.... it'll fail when the `MachineFormat` sheet is not active. `Cells` *needs* to be qualified with the worksheet, otherwise it's implicitly the `ActiveSheet`, or `Me` if this code is in a sheet code module. – BigBen Apr 22 '21 at 17:55
  • 1
    [More reading](https://stackoverflow.com/a/28439984/9245853). – BigBen Apr 22 '21 at 17:57