मैं पिछले एक हफ्ते से इस कोड पर अपने दम पर काम कर रहा हूं और ऐसा लगता है कि हर रोज एक नया मुद्दा चल रहा है। आज का मुद्दा तब हो रहा है जब मैं किसी कार्यपत्रक के लिए पंक्तियों की संख्या प्राप्त करने का प्रयास करता हूं ताकि यह किसी अन्य कार्यपत्रक पर सूत्रों को नीचे खींच सके।

मैंने इस्तेमाल किया है

lrSell = Cells(Rows.Count, TradeDateSell).End(xlUp).Row
'Run time error 13
'and
Set lrSell = Cells(Rows.Count, TradeDateSell).End(xlUp).Row
'Compile error: Object required

कोड इस प्रकार है

Dim Transactions As Workbook
Dim Macro As Workbook
'Sales
Dim SellData As Worksheet
Dim MonthlySales As Worksheet
Dim TradeDateSell As Range
Dim NoSales As Range
'Buys
Dim BuyData As Worksheet
Dim MonthlyBuys As Worksheet
Dim TradeDateBuy As Range
Dim NoBuys As Range
'Other info
Dim Others As Worksheet
Dim Summary As Worksheet
Dim PrvDay As Date
Dim Workdates As Worksheet
Dim BuysPaste As Worksheet
Dim MyCell As Range

'Definitions
Set Transactions = ActiveWorkbook
Set Macro = ThisWorkbook
Set BuysPaste = Macro.Sheets("Buys")
Set Workdates = Macro.Sheets("Dates")
PrvDay = (Workdates.Range("B2").Value)
Set Others = Transactions.Sheets("others")
Set Summary = Transactions.Sheets("Summary")

'Sell Def
Set SellData = Transactions.Sheets("SellData")
Set MonthlySales = Transactions.Sheets("Monthly Sales")
Set NoSales = Others.Range("A37:CD37")
'Buy Def
Set BuyData = Transactions.Sheets("BuyData")
Set MonthlyBuys = Transactions.Sheets("Monthly Buys")
Set NoBuys = Others.Range("A36:CD36")

'Find the column that will be searched
SellData.Activate
Set TradeDateSell = Rows("1:1").Find(What:="Trade Date", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

'Find the column that will be searched
BuyData.Activate
Set TradeDateBuy = Rows("1:1").Find(What:="Trade Date", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

'Check if Sales were made
SellData.Activate
Dim lrSell As Long
lrSell = Cells(Rows.Count, TradeDateSell.Column).End(xlUp).Row

For Each Cell In Range(TradeDateSell.Column & lrSell)
    If Cell.Value = PrvDay Then
    Else
        NoSales.Copy Destination:=SellData.Rows(TradeDateSell.Offset(1, -7))
        Exit For
    End If
Next

'Offset allows it to paste in the A column
lrSell = Cells(Rows.Count, TradeDateSell.Column).End(xlUp).Row
'Counting Rows again after adjustments were made

'Check if Buys were made
BuyData.Activate
Dim lrBuy As Long
lrBuy = Cells(Rows.Count, TradeDateBuy.Column).End(xlUp).Row

For Each Cell In Range(TradeDateBuy.Column & lrBuy).Cells
    If Cell.Value = PrvDay Then
    Else
        NoBuys.Copy Destination:=BuyData.Rows(TradeDateBuy.Offset(1, -10))
        Exit For
    End If
Next
'Offset allows it to paste in the A column
lrBuy = Cells(Rows.Count, TradeDateBuy.Column).End(xlUp).Row
'Counting Rows again after adjustments were made

'Drag down all columns in Monthly tabs to the same number rows in the data tabs
MonthlyBuys.Range("A2:CN2").AutoFill Destination:=Range("A2:CN" & lrBuy)
MonthlySells.Range("A2:CG2").AutoFill Destination:=Range("A2:CG" & lrSell)

'Refresh All PivotTables
Summary.Activate
ActiveWorkbook.RefreshAll

'Filter for PRVDay in the monthly buys tab and copy all and paste into BuysPaste worksheet in the Macro workbook
'Clear filter once complete

MsgBox "Please confirm if all totals are correct before running the Equitable Macro", vbOKOnly, "Please use EquitableMacro for the next steps"

यदि आप देखते हैं कि कुछ और बंद है या जानकारी की आवश्यकता है, तो कृपया मुझे बताएं। शुक्रिया

0
signup4stuffmatt 10 सितंबर 2019, 16:57

1 उत्तर

सबसे बढ़िया उत्तर

मुझे यह भी नहीं पता कि यह कोड काम करेगा या नहीं क्योंकि यह बहुत गड़बड़ है, लेकिन कम से कम आपको कुछ सुझाव दिए हैं:

Option Explicit
Sub Test()

    'you are trying to use lrSell it before you even declare the variable, same goes with TradeDateSell but also you need its .Column property
    'lrSell = Cells(Rows.Count, TradeDateSell).End(xlUp).Row
    'Run time error 13
    'and
    'You can't use Set for a Long variable, is only for Object variables such as Workbooks, Worksheets, Dictionaries, objects...
    'Set lrSell = Cells(Rows.Count, TradeDateSell).End(xlUp).Row
    'Compile error: Object required

    Dim TradeDateSell As Range
    Dim TradeDateBuy As Range

    'Is highly recomendable to qualify the whole range you are talking about
    'If you are working on the same sheet for all this code you can do by using a dot before any Range or Cell like
    ' .Range() or .Cells() just by using the With Block below
    With ThisWorkbook.Sheets("MySheet")
        Set TradeDateSell = .Rows("1:1").Find(What:="Trade Date", After:=.Range("A1"), LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False) 'see how I've added the dots on .RAnge("A1") and .Rows("1:1")
        Set TradeDateBuy = .Rows("1:1").Find(What:="Trade Date", After:=.Range("A1"), LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        Dim lrSell As Long

        'like we stated before a long variable doesn't need Set to be set, also you need the column from TradeDateSell
        lrSell = Cells(Rows.Count, TradeDateSell.Column).End(xlUp).Row

        'to build a range you need the first cell and the last one, being the first in this case TradeDateSell
        'and the last Cells(lrSell, TradeDateSell.Column)
        Dim PrvDay As String 'you didn't declare PrvDay neither give it value to check
        Dim NoSales As Range 'you didn't declare NoSales nor set it
        Dim SellData As Range 'you didn't declare SellData nor set it

        Set NoSales = .Range("??") 'set your variable
        Set SellData = .Range("??") 'set your variable
        PrvDay = "?" 'give PrvDay a value

        For Each MyCell In .Range(TradeDateSell, .Cells(lrSell, TradeDateSell.Column))
            If MyCell.Value = PrvDay Then
            Else
                NoSales.Copy Destination:=SellData.Rows(TradeDateSell.Offset(1, -7).Row) 'you need the .Row property for the range
                Exit For
            End If
        Next
        'Offset allows it to paste in the A column

        lrSell = .Cells(Rows.Count, TradeDateSell).End(xlUp).Row '
        'Counting Rows again after adjustments were made

        Dim lrBuy As Long
        lrBuy = .Cells(.Rows.Count, TradeDateBuy.Column).End(xlUp).Row

        Dim NoBuys As Range
        Dim BuyData As Range

        Set NoBuys = .Range("??")
        Set BuyData = .Range("??")

        For Each MyCell In .Range(TradeDateSell, .Cells(lrSell, lrBuy.Column))
            If MyCell.Value = PrvDay Then
            Else
                NoBuys.Copy Destination:=BuyData.Rows(TradeDateBuy.Offset(1, -10).Row)
                Exit For
            End If
        Next
        'Offset allows it to paste in the A column
        lrBuy = .Cells(.Rows.Count, TradeDateBuy).End(xlUp).Row
        'Counting Rows again after adjustments were made

        Dim MonthlyBuys As Range
        Dim MonthlySells As Range

        Set MonthlyBuys = .Range("??")
        Set MonthlySells = .Range("??")

        'Drag down all columns in Monthly tabs to the same number rows in the data tabs
        MonthlyBuys.Range("A2:CN2").AutoFill Destination:=.Range("A2:CN" & lrBuy)
        MonthlySells.Range("A2:CG2").AutoFill Destination:=.Range("A2:CG" & lrSell)
    End With

End Sub
0
Damian 10 सितंबर 2019, 17:17