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]

"Wiggler" <[email protected]> wrote in message
news:[email protected]...
> 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