Consulting

Results 1 to 2 of 2

Thread: Need some help on the Function

  1. 10-31-2011, 05:48 AM #1
    dilipramadas
    dilipramadas is offline
    VBAX Regular
    Joined
    Feb 2010
    Posts
    28
    Location

    Need some help on the Function

    Hi Guys,

    I tried working on a macro for a repetitive task. It was working until i tried simplifying it and ruined it entirely. Now when i run this macro I get an error: "Function call on left hand side of assignment must return variant or object"

    [vba]Public Function lr() As Long
    lr = ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    End Function
    Public Function lc() As Long
    lc = ActiveSheet.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    End Function
    Sub CORI_HC()
    '
    ' Macro1 Macro
    ' Macro by Dilip Ramadas
    'BIEN
    Dim Path1, Path2, path3, path4, path5, path6, path7, path8, path9, path10, path11, path12, path13, path14, path15 As String
    Path1 = Sheet1.Range("d2").Value
    Path2 = Sheet1.Range("d3").Value
    path3 = Sheet1.Range("d4").Value
    path4 = Sheet1.Range("d7").Value
    path5 = Sheet1.Range("d8").Value
    path6 = Sheet1.Range("d9").Value
    path7 = Sheet1.Range("d12").Value
    path8 = Sheet1.Range("d13").Value
    path9 = Sheet1.Range("d14").Value
    path10 = Sheet1.Range("d17").Value
    path11 = Sheet1.Range("d18").Value
    path12 = Sheet1.Range("d19").Value
    path13 = Sheet1.Range("d22").Value
    path14 = Sheet1.Range("d23").Value
    path15 = Sheet1.Range("d24").Value
    Dim file1, file2, file3, file4, file5, file6, file7, file8, file9, file10, file11, file12, file13, file14, file15 As String
    file1 = Sheet1.Range("e2").Value
    file2 = Sheet1.Range("e3").Value
    file3 = Sheet1.Range("e4").Value
    file4 = Sheet1.Range("e7").Value
    file5 = Sheet1.Range("e8").Value
    file6 = Sheet1.Range("e9").Value
    file7 = Sheet1.Range("e12").Value
    file8 = Sheet1.Range("e13").Value
    file9 = Sheet1.Range("e14").Value
    file10 = Sheet1.Range("e17").Value
    file11 = Sheet1.Range("e18").Value
    file12 = Sheet1.Range("e19").Value
    file13 = Sheet1.Range("e22").Value
    file14 = Sheet1.Range("e23").Value
    file15 = Sheet1.Range("e24").Value

    Workbooks.Open Filename:= _
    Path1 & file1
    wb1 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    Path2 & file2
    wb2 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path3 & file3
    wb3 = ActiveWorkbook.Name

    Windows(wb3).Activate

    Sheets("Source").Select
    lr = Range("A5000").End(xlUp).Row

    If lr > 1 Then
    Rows("2:" & lr).Delete
    End If

    Windows(wb1).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy
    Windows(wb3).Activate
    Rows("2:2").Select
    ActiveSheet.Paste

    Windows(wb2).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy

    Windows(wb3).Activate
    lr = Range("A5000").End(xlUp).Row + 1
    Rows(lr).Select
    ActiveSheet.Paste
    Range("A1").Select
    lr = Range("A5000").End(xlUp).Row
    Dim pvtrng As Range
    Set pvtrng = Range("A1:CS" & lr)
    ActiveSheet.Previous.Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    pvtrng, Version:=xlPivotTableVersion12)

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close

    'cba
    Workbooks.Open Filename:= _
    path4 & file4
    wb4 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path5 & file5
    wb5 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path6 & file6
    wb6 = ActiveWorkbook.Name

    Windows(wb6).Activate

    Sheets("Source").Select
    lr = Range("A5000").End(xlUp).Row

    If lr > 1 Then
    Rows("2:" & lr).Delete
    End If

    Windows(wb4).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy
    Windows(wb6).Activate
    Rows("2:2").Select
    ActiveSheet.Paste

    Windows(wb5).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy

    Windows(wb6).Activate
    lr = Range("A5000").End(xlUp).Row + 1
    Rows(lr).Select
    ActiveSheet.Paste
    Range("A1").Select
    lr = Range("A5000").End(xlUp).Row
    Dim pvtrng1 As Range
    Set pvtrng1 = Range("A1:CS" & lr)
    ActiveSheet.Previous.Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    pvtrng1, Version:=xlPivotTableVersion12)

    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close

    'CORI Cov
    Workbooks.Open Filename:= _
    path7 & file7
    wb7 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path8 & file8
    wb8 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path9 & file9
    wb9 = ActiveWorkbook.Name

    Windows(wb9).Activate

    Sheets("Source").Select
    lr = Range("A5000").End(xlUp).Row

    If lr > 1 Then
    Rows("2:" & lr).Delete
    End If

    Windows(wb7).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy
    Windows(wb9).Activate
    Rows("2:2").Select
    ActiveSheet.Paste

    Windows(wb8).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy

    Windows(wb9).Activate
    lr = Range("A5000").End(xlUp).Row + 1
    Rows(lr).Select
    ActiveSheet.Paste
    Range("A1").Select
    lr = Range("A5000").End(xlUp).Row
    Dim pvtrng2 As Range
    Set pvtrng2 = Range("A1:CS" & lr)
    ActiveSheet.Previous.Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    pvtrng2, Version:=xlPivotTableVersion12)

    ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close

    'JV MARK
    Workbooks.Open Filename:= _
    path10 & file10
    wb10 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path11 & file11
    wb11 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path12 & file12
    wb12 = ActiveWorkbook.Name

    Windows(wb12).Activate

    Sheets("Source").Select
    lr = Range("A5000").End(xlUp).Row

    If lr > 1 Then
    Rows("2:" & lr).Delete
    End If

    Windows(wb10).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy
    Windows(wb12).Activate
    Rows("2:2").Select
    ActiveSheet.Paste

    Windows(wb11).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy

    Windows(wb12).Activate
    lr = Range("A5000").End(xlUp).Row + 1
    Rows(lr).Select
    ActiveSheet.Paste
    Range("A1").Select
    lr = Range("A5000").End(xlUp).Row
    Dim pvtrng3 As Range
    Set pvtrng3 = Range("A1:CS" & lr)
    ActiveSheet.Previous.Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    pvtrng3, Version:=xlPivotTableVersion12)

    ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close

    'MNA
    Workbooks.Open Filename:= _
    path13 & file13
    wb13 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path14 & file14
    wb14 = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
    path15 & file15
    wb15 = ActiveWorkbook.Name

    Windows(wb15).Activate

    Sheets("Source").Select
    lr = Range("A5000").End(xlUp).Row

    If lr > 1 Then
    Rows("2:" & lr).Delete
    End If

    Windows(wb13).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy
    Windows(wb15).Activate
    Rows("2:2").Select
    ActiveSheet.Paste

    Windows(wb14).Activate
    lr = Range("A5000").End(xlUp).Row
    Rows("2:" & lr).Copy

    Windows(wb15).Activate
    lr = Range("A5000").End(xlUp).Row + 1
    Rows(lr).Select
    ActiveSheet.Paste
    Range("A1").Select
    lr = Range("A5000").End(xlUp).Row
    Dim pvtrng4 As Range
    Set pvtrng4 = Range("A1:CS" & lr)
    ActiveSheet.Previous.Select
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable6").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    pvtrng4, Version:=xlPivotTableVersion12)

    ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWindow.Close
    ActiveWindow.Close
    End Sub
    [/vba]

    I've been trying to crack my head for this but without any luck so far.
    Any help would be appreciated. Thanks in advance.

    Regards,
    Dilip
    In life, small things bother you more than the big ones. You can sit on a mountain but not on a pin!
    Reply With Quote Reply With Quote
  2. 10-31-2011, 06:57 AM #2
    Kenneth Hobs
    Kenneth Hobs is offline
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You named the Function "lc" the same as a Variable "lc" in the other Sub.

    Tip: Use
    [vba]Option Explicit[/vba] as the first line of code.

    Tip2: When using Dim, note that you must define each type. e.g.
    [vba]Dim lc, lr as long[/vba] This defines lc as a variant and lr as Long.

    Tip3. Column numbers are Integers. Row numbers are Long.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules