UF To Spreadsheet Help

[Emoncada]

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]

Hi there,

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

[vba]ColNext1 = WS.Range("B1").End(xlToLeft).Column + 1[/vba]
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...

[vba] Dim WS As Worksheet
Set WS = Worksheets("Wk Picks")[/vba]

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

[vba]ColNext1 = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column + 1[/vba]

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...

[vba]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[/vba]

[Emoncada]

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]

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...

[vba] 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[/vba]

[Emoncada]

Thanks, Zack That worked perfectly.