MrBrian
02-05-2019, 05:59 PM
Hi Guys,
I spent a bunch of time writing a big macro to pull data from one sheet of my workbook onto another in a very fiddly way, but I can't even get it to run due to runtime errors. Here's the first bit, which runs into 'Error 438: Object doesn't support this property or method' on the line that starts "xLines = ...". I've already tried jiggering it around to get it to work a bit. My original code, on the line below, returns "Error 424: Object required'. SaleSheetData is a named data range, found on sheet "Sale01", with Workbook scope. I tried the Google, and a forum search, but didn't find anyone who'd done whatever foolish thing I seem to have done.
Dim numLines As Integer
Dim xLines As Integer
Dim yLines As Integer
Dim blankLines As Integer
Dim i As Integer
Dim wSale As Worksheet, wPO As Worksheet, wTemplate As Worksheet
Dim matchX As String, matchY As String
Dim SaleSheetData As Range
Set wSale = Worksheets("Sale01")
Set wPO = Worksheets("PO")
Set wTemplate = Worksheets("Templates")
Set SaleSheetData = wSale.Range("SaleSheetData")
matchX = "Ducks"
matchY = "Chickens"
Application.ScreenUpdating = False
xLines = Application.WorksheetFunctions.CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)
yLines = WorksheetFunctions.CountIf(WorksheetFunctions.Index("SaleSheetData", 0, 80), matchY)
numLines = xLines + yLines
blankLines = WorksheetFunctions.CountA(wPO.Range("B19:B1000"))
This part of the macro is just supposed to get the number of chickens and ducks from the sale sheet, to be used for a number of things later in the macro, and the chicken/duck/other column is column 80 (don't ask) in the range. Hopefully I can fix whatever is wrong globally, but this is also the only section that calls worksheet functions, so if there's a better way to do it wholly within VBA, I'm open to that, too.
Thanks in advance!
I spent a bunch of time writing a big macro to pull data from one sheet of my workbook onto another in a very fiddly way, but I can't even get it to run due to runtime errors. Here's the first bit, which runs into 'Error 438: Object doesn't support this property or method' on the line that starts "xLines = ...". I've already tried jiggering it around to get it to work a bit. My original code, on the line below, returns "Error 424: Object required'. SaleSheetData is a named data range, found on sheet "Sale01", with Workbook scope. I tried the Google, and a forum search, but didn't find anyone who'd done whatever foolish thing I seem to have done.
Dim numLines As Integer
Dim xLines As Integer
Dim yLines As Integer
Dim blankLines As Integer
Dim i As Integer
Dim wSale As Worksheet, wPO As Worksheet, wTemplate As Worksheet
Dim matchX As String, matchY As String
Dim SaleSheetData As Range
Set wSale = Worksheets("Sale01")
Set wPO = Worksheets("PO")
Set wTemplate = Worksheets("Templates")
Set SaleSheetData = wSale.Range("SaleSheetData")
matchX = "Ducks"
matchY = "Chickens"
Application.ScreenUpdating = False
xLines = Application.WorksheetFunctions.CountIf(Range(SaleSheetData.Offset(0, 80), SaleSheetData.End(xlDown)), matchX)
yLines = WorksheetFunctions.CountIf(WorksheetFunctions.Index("SaleSheetData", 0, 80), matchY)
numLines = xLines + yLines
blankLines = WorksheetFunctions.CountA(wPO.Range("B19:B1000"))
This part of the macro is just supposed to get the number of chickens and ducks from the sale sheet, to be used for a number of things later in the macro, and the chicken/duck/other column is column 80 (don't ask) in the range. Hopefully I can fix whatever is wrong globally, but this is also the only section that calls worksheet functions, so if there's a better way to do it wholly within VBA, I'm open to that, too.
Thanks in advance!