PDA

View Full Version : modify nextrow code



Emoncada
10-05-2009, 05:31 AM
I have the following.


RowNext1 = Worksheets("Outgoing").Cells(65536, 1).End(xlUp).Row + 1
RowNext2 = Worksheets("Outgoing").Cells(65536, 1).End(xlUp).Row + 2
RowNext3 = Worksheets("Outgoing").Cells(65536, 1).End(xlUp).Row + 3
RowNext4 = Worksheets("Outgoing").Cells(65536, 1).End(xlUp).Row + 4


With Worksheets("Outgoing")
.Cells(RowNext1, 1) = "HP Laptop"
.Cells(RowNext1, 2) = UserFormOutgoing.TxtSN.Value
.Cells(RowNext1, 3) = UserFormOutgoing.Txtname.Value
.Cells(RowNext1, 4) = UserFormOutgoing.TxtTrk.Value


This works great, it transfers the forms data into the spreadsheet by rows. I would like the samething but for columns so all data goes down in the first available column.

Any help would be great.

Thanks

Bob Phillips
10-05-2009, 06:00 AM
ColNext = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Column + 1

Emoncada
10-05-2009, 06:27 AM
So would it be like this

ColNext1 = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Col + 1
ColNext2 = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Col + 2
ColNext3 = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Col + 3
ColNext4 = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Col + 4
ColNext5 = Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft).Col + 5

Emoncada
10-05-2009, 06:28 AM
With ColNext ending in a number pending on how many possible txtboxes userform has?
So if i have TxtBox1-30 do I next to Dim ColNext1-30 as integer?

Bob Phillips
10-05-2009, 07:25 AM
Simplify it


With Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft)

ColNext1 =.Col + 1
ColNext2 = .Col + 2
ColNext3 = .Col + 3
ColNext4 = .Col + 4
ColNext5 = .Col + 5
End With

Bob Phillips
10-05-2009, 07:27 AM
With ColNext ending in a number pending on how many possible txtboxes userform has?
So if i have TxtBox1-30 do I next to Dim ColNext1-30 as integer?

No, use an array



Dim ColNext(1 to 20) As Long
Dim i As Long

With Worksheets("Outgoing").Cells(1, Columns.Count).End(xlToLeft)

For i = 1 to 30

ColNext(i) = .Column + i
Next i
End With

Emoncada
10-05-2009, 07:57 AM
Ok So now to transfer the data to the spreadsheet does this look right?

With Worksheets("BAR_OutDesktops")
.Cells(ColNext1, 1) = UserFormDesktops.TextBox1.Value
.Cells(ColNext2, 2) = UserFormDesktops.TextBox2.Value
.Cells(ColNext3, 3) = UserFormDesktops.TextBox3.Value
.Cells(ColNext4, 4) = UserFormDesktops.TextBox4.Value
.Cells(ColNext5, 5) = UserFormDesktops.TextBox5.Value
.Cells(ColNext6, 6) = UserFormDesktops.TextBox6.Value
.Cells(ColNext7, 7) = UserFormDesktops.TextBox7.Value
.Cells(ColNext8, 8) = UserFormDesktops.TextBox8.Value
.Cells(ColNext9, 9) = UserFormDesktops.TextBox9.Value
.Cells(ColNext10, 10) = UserFormDesktops.TextBox10.Value

Sorry I got a little confused.

Bob Phillips
10-05-2009, 08:00 AM
Yeah, that looks about right.

Emoncada
10-05-2009, 10:13 AM
Its giving me an error

Application-defined or object-defined error

It highlights .Cells(ColNext1, 1) = UserFormDesktops.TextBox1.Value

Bob Phillips
10-05-2009, 11:03 AM
Is ColNext1 properly setup?

Does UserFormDesktops exist?

Does TextBox1 exist?

Emoncada
10-05-2009, 11:16 AM
This is the way I have it

Dim ColNext(1 To 30) As Long
Dim i As Long

With Worksheets("BAR_OutDesktops").Cells(1, Columns.Count).End(xlToLeft)

For i = 1 To 30

ColNext(i) = .Column + i
Next i
End With

With Worksheets("BAR_OutDesktops")
.Cells(ColNext1, 1) = UserFormDesktops.TextBox1.Value
.Cells(ColNext2, 2) = UserFormDesktops.TextBox2.Value
.Cells(ColNext3, 3) = UserFormDesktops.TextBox3.Value
.Cells(ColNext4, 4) = UserFormDesktops.TextBox4.Value
.Cells(ColNext5, 5) = UserFormDesktops.TextBox5.Value
.Cells(ColNext6, 6) = UserFormDesktops.TextBox6.Value
.Cells(ColNext7, 7) = UserFormDesktops.TextBox7.Value
.Cells(ColNext8, 8) = UserFormDesktops.TextBox8.Value
.Cells(ColNext9, 9) = UserFormDesktops.TextBox9.Value
.Cells(ColNext10, 10) = UserFormDesktops.TextBox10.Value
etc..

Both Userform and Worksheet exists.

Bob Phillips
10-05-2009, 03:23 PM
It should be



Dim ColNext(1 To 30) As Long
Dim i As Long

With Worksheets("BAR_OutDesktops").Cells(1, Columns.Count).End(xlToLeft)

For i = 1 To 30

ColNext(i) = .Column + i
Next i
End With

With Worksheets("BAR_OutDesktops")
.Cells(ColNext(1), 1) = UserFormDesktops.TextBox1.Value
.Cells(ColNext(2), 2) = UserFormDesktops.TextBox2.Value
.Cells(ColNext(3), 3) = UserFormDesktops.TextBox3.Value
.Cells(ColNext(4), 4) = UserFormDesktops.TextBox4.Value
.Cells(ColNext(5), 5) = UserFormDesktops.TextBox5.Value
.Cells(ColNext(6), 6) = UserFormDesktops.TextBox6.Value
.Cells(ColNext(7), 7) = UserFormDesktops.TextBox7.Value
.Cells(ColNext(8), 8) = UserFormDesktops.TextBox8.Value
.Cells(ColNext(9), 9) = UserFormDesktops.TextBox9.Value
.Cells(ColNext(10), 10) = UserFormDesktops.TextBox10.Value
End With

geekgirlau
10-05-2009, 04:10 PM
What about this:


Dim i As Long

With Worksheets("BAR_OutDesktops").Cells(1, Columns.Count).End(xlToLeft)
For i = 1 To 30
.Cells(.Column + i, i) = UserFormDesktops.Controls("TextBox" & i).Value
Next i
End With

Emoncada
10-06-2009, 08:21 AM
XLD It put the data on the spreadsheet now, but it's not going in the same column its going in first A2, then B3, then C4 etc..

Now I changed this

.Cells(ColNext(1), 1) = UserFormDesktops.TextBox1.Value
.Cells(ColNext(2), 2) = UserFormDesktops.TextBox2.Value
.Cells(ColNext(3), 3) = UserFormDesktops.TextBox3.Value
To

.Cells(ColNext(1), 1) = UserFormDesktops.TextBox1.Value
.Cells(ColNext(2), 1) = UserFormDesktops.TextBox2.Value
.Cells(ColNext(3), 1) = UserFormDesktops.TextBox3.Value
etc...

That put it in the same column, but when i did it again it deleted the first column with the new data instead of putting the new data in the next column.

Emoncada
10-08-2009, 10:03 AM
BUMP!

Emoncada
10-13-2009, 06:27 AM
Anyone know how I can makes this work?