PDA

View Full Version : [SOLVED] UF To Spreadsheet Help



Emoncada
12-03-2013, 08:34 AM
I have the following code.


Sub Send()
Dim ColNext1 As Integer
Dim RowNext1 As Integer
Dim RowNext2 As Integer
Dim RowNext3 As Integer
Dim RowNext4 As Integer

'last row of data puls one row
ColNext1 = Worksheets("Wk Picks").Range("B1").End(xlToLeft).Column + 1

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


'Cells(Row Number,Column Number)
With Worksheets("Wk Picks")

If Teams.TxtAway.Value <> "" Then

.Cells(RowNext1, ColNext1) = "Sunday"
.Cells(RowNext2, ColNext1) = Teams.CmbTime.Value
.Cells(RowNext3, ColNext1) = Teams.TxtAway.Value
.Cells(RowNext4, ColNext1) = Teams.TxtHome.Value




End If


End With


End Sub

What i need is for this code to look at the next available column with no data a row 1 then transfer the data to the sheet.

This code is putting the data in Column B which is good since A1 has data, but it's putting the data in Row 20. It appears to be looking at Column A which has data down to row 19
I need for it to start from row 1. Then continue looking at next available column based on row1

What am i doing wrong?

Zack Barresse
12-03-2013, 09:02 AM
Hi there,

You're using this as your column number...

ColNext1 = WS.Range("B1").End(xlToLeft).Column + 1
Logically this is saying start from B1, go to the left until you find data (or the end of data) and use that column plus 1. This will ALWAYS return 2. What you want to do (if I understand you correctly) is start from the right-most column of the worksheet in row 1 and go to the left until you find data, then add 1.

Something I would do, since it's referenced multiple times and you can shorten the code a bit with it, is use a variable for the worksheet, something like...

Dim WS As Worksheet
Set WS = Worksheets("Wk Picks")

If this is the case, you'd change the above line of code to this...

ColNext1 = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column + 1

Also, you really don't want to hardcode your row/column numbers, e.g. 65536, because it doesn't work in all versions. Instead reference the sheets rows/columns accordingly. And using a worksheet variable would preclude you from needing the With statement (I generally disdain them). And you really only need a single row variable. This would change your overall code (including the above suggestions) to...

Sub Send()

Dim WS As Worksheet
Dim ColNext1 As Long
Dim RowNext As Long

Set WS = Worksheets("Wk Picks")

'last row of data puls one row
ColNext1 = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column + 1
RowNext = WS.Cells(WS.Rows.Count, 2).End(xlUp).Row

'Cells(Row Number,Column Number)
If Teams.TxtAway.Value <> "" Then

WS.Cells(RowNext + 1, ColNext1) = "Sunday"
WS.Cells(RowNext + 2, ColNext1) = Teams.CmbTime.Value
WS.Cells(RowNext + 3, ColNext1) = Teams.TxtAway.Value
WS.Cells(RowNext + 4, ColNext1) = Teams.TxtHome.Value

End If

End Sub

Emoncada
12-03-2013, 09:57 AM
Hey Zach, thanks for the reply. I was able to use your code and kind of get it to work, but there is an issue.

Sub Send()
Dim WS As Worksheet
Dim ColNext1 As Long
Dim RowNext As Long

Set WS = Worksheets("Wk Picks")

'last row of data puls one row
ColNext1 = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column + 1
RowNext = WS.Cells(WS.Rows.Count, 2).End(xlUp).Row

'Cells(Row Number,Column Number)
If Teams.TxtAway.Value <> "" Then

WS.Cells(RowNext, ColNext1) = "Sunday"
WS.Cells(RowNext + 1, ColNext1) = Teams.CmbTime.Value
WS.Cells(RowNext + 2, ColNext1) = Teams.TxtAway.Value
WS.Cells(RowNext + 3, ColNext1) = Teams.TxtHome.Value

End If

End Sub

I had to modify the RowNext + 1 to just RowNext, because I wanted the data to start from Row1.
The problem is after that one is entered, if run again it should enter the data in Column C Row 1.
Instead it put it in Column C Row 4

Zack Barresse
12-03-2013, 10:17 AM
Hmm, ok, not sure why you're using a variable to find the last row then. Am I missing something? If it will always start in row 1 of the first blank column (based on row 1 data), then use this inside your If statement...

WS.Cells(1, ColNext1) = "Sunday"
WS.Cells(2, ColNext1) = Teams.CmbTime.Value
WS.Cells(3, ColNext1) = Teams.TxtAway.Value
WS.Cells(4, ColNext1) = Teams.TxtHome.Value

Emoncada
12-03-2013, 12:41 PM
Thanks, Zack That worked perfectly.