CODEWITHSUNDEEP
vbaexcel
RGil
RGil

Reputation: 11

Finding a range from different columns and inserting a value

My current challenge is that I have a list of data that has been pulled in from another workbook in Columns A-K which all finish on the same row. I want to label the data with all the same value in Column L

The code I have is trying to find the last cell in Column A and the last cell in Column L.

I am then trying to use the range between these two rows to insert a value across the range in Column L: 

    Sub FillTestType(TestType As String)

    Dim rng As Range
    Dim wsh As Worksheet
    Dim Row As Long
    Dim EndCell As Long
    Dim TopCell As Long

      Set wsh = Worksheets("Sheet1")
      wsh.Activate

      EndCell = Cells(Rows.count, "A").End(x1Up).Row
      TopCell = Cells(Rows.count, "L").End(x1Up).Row

      rng = Range(Cells(TopCell, 12), Cells(EndCell, 12)).Value

      rng = TestType

    End Sub

I keep getting a runtime error - Any help would be greatly appreciated! Or if I am being stupid and there is a better way to tackle the problem please do let me know.

Upvotes: 1

Views: 26

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Something like this should work. Mainly collating the comments:

  • xlUp rather than x1Up (and Option Explicit to catch that yourself in future!)
  • +1 to TopCell so that you don't overwrite your last value in that row
  • Fully qualified your Range and Cell references using With and .
  • Cleaned up variables which aren't necessary -- e.g. for multiple future use or readability
Option Explicit    

Sub FillTestType(TestType As String)
    Dim EndCell As Long
    Dim TopCell As Long

    With Worksheets("Sheet1")
        EndCell = .Cells(.Rows.Count, "A").End(xlUp).Row
        TopCell = .Cells(.Rows.Count, "L").End(xlUp).Row + 1
        .Range(.Cells(TopCell, 12), .Cells(EndCell, 12)).Value = TestType
    End With
End Sub

Upvotes: 1

Related Questions