Writing to Columns from VBA

[Wiggler]

Hello, I have a sub set up to find the first blank line then write a row of
data to a worksheet after a button click here is part of the code.
(Begin code)

Private Sub Chart()
Dim irow As Long
Dim Chart As Worksheet
Set Chart = Worksheets("Chart data")
'find first empty Row in database
irow = Chart.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Chart.Cells(irow, 1).value = Me.cbbrand.Text
Chart.Cells(irow, 2).value = Me.tb04vol.Text

I want convert this to a column format and I am having trouble with the
".End(xlup)...
Is it ".end(xlleft)"
Aside from changing "Row(s)" to Column(s) what must I do to write the data
vertically vs. horizontally.

TIA

 

[Wiggler]

Hello, I have a sub set up to find the first blank line then write a row
of data to a worksheet after a button click here is part of the code.
(Begin code)

Private Sub Chart()
Dim irow As Long
Dim Chart As Worksheet
Set Chart = Worksheets("Chart data")
'find first empty Row in database
irow = Chart.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Chart.Cells(irow, 1).value = Me.cbbrand.Text
Chart.Cells(irow, 2).value = Me.tb04vol.Text

I want convert this to a column format and I am having trouble with the
".End(xlup)...
Is it ".end(xlleft)"
Aside from changing "Row(s)" to Column(s) what must I do to write the data
vertically vs. horizontally.

TIA

It must be me... I never get a response... What have I done to offend the
brilliant minds of this NG (He wonders)
Sigh...

 

[GaryDK]

Hi Wiggler,

Try this and see if it works for you. It assumes your data starts in
row 1:

Private Sub Chart()
Dim icol As Integer
Dim Chart As Worksheet
Set Chart = Worksheets("Chart data")
'find first empty Row in database
icol = Chart.Cells(1, 256) _
.End(xlToLeft).Offset(0, 1).Column


Chart.Cells(1, icol).Value = Me.cbbrand.Text
Chart.Cells(2, icol).Value = Me.tb04vol.Text

Regards,

Gary

 

[GaryDK]

You should insert the following (or something similar) after the icol
column is calculated, in order to handle the end of the sheet:

If icol = 255 And Not IsEmpty(Cells(1, 256)) Then
MsgBox "Sheet is full.", vbExclamation
Exit Sub
End If

Gary