Consulting

Results 1 to 3 of 3

Thread: Problem between sheets

  1. #1
    VBAX Newbie
    Joined
    May 2006
    Posts
    4
    Location

    Problem between sheets

    The two Subs ( ExtractSumCode & Range1Update) each work as separate routines. However, when I call one from the other, I get "error 1004, Application-defined Object-defined error"

    Each time I get the error, if I go back to the file and click off the selected range/cells, Then the Sub will run successfully again............as a separate routine.

    Can someone help me with the code to get the program to switch between sheets.
    ******************************************************
    [VBA]
    Sub ExtractSumCode()
    'Purpose: ExtractCustCode() procedure extracts the related Customer code from
    'the Reference worksheet tables using IF conditions and VLOOKUP functions.

    Dim i As Integer
    Dim RCount As Long
    Dim SumCode As Range, TestTable As Range
    Dim cell As Range

    Call Worksheets("Reference").Range1Update

    With Worksheets("AR_Aging").Range("a2")

    RCount = Range(.Offset(0, 0), .End(xlDown)).Count
    ActiveSheet.Range("A1").Select
    End With
    With Worksheets("AR_Aging").Range("M2")
    .Formula = "=VlookUp(A2,Range1,3)"
    End With

    With Worksheets("AR_Aging").Range("M2")
    .Copy

    Range(.Offset(1, 0), .Offset(RCount - 1, 0)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End With
    With Worksheets("AR_Aging").Range("M1")
    .Value = "SumCode"
    .Font.Bold = True
    Range("P1").Range(.Offset(1, 0), .End(xlDown)).Name = "SumCode"
    End With

    End Sub
    ********************************************************

    Sub Range1Update()
    'This routine updates the range "Range1" to provide an effective table for customer SumCode

    ActiveWorkbook.Names("Range1").Delete
    With Worksheets("Reference").Range("D1")
    Range(.Offset(0, 0), .Offset(0, 2).End(xlDown)).Name = "Range1"
    ActiveSheet.Range("A1").Select
    End With
    With Worksheets("Reference").Range("D1")
    ActiveSheet.Range(.End(xlToRight), .End(xlDown)).Select
    Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False _
    , Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
    DataOption2:=xlSortNormal
    End With
    With Worksheets("AR_Aging").Range("A65300")
    End With
    [/VBA]

  2. #2
    HAHHAH problem between sheets is the best title EVER!

  3. #3
    Nice title ! ?? **

    Try this..

    Its all in the reference sheet code module


    [VBA]Option Explicit
    Sub ExtractSumCode()
    'Purpose: ExtractCustCode() procedure extracts the related Customer code from
    'the Reference worksheet tables using IF conditions and VLOOKUP functions.

    Dim i As Integer
    Dim RCount As Long
    Dim SumCode As Range, TestTable As Range
    Dim cell As Range

    Call Worksheets("Reference").Range1Update
    With Worksheets("AR_Aging")
    RCount = .Range("A65536").End(xlUp).Row
    With .Range("M2:M" & Trim(Str(RCount)))
    .Formula = "=VlookUp(A2,Range1,3,false)" 'false ensure no xl approx match
    .Name = "SumCode"
    End With
    With .Range("M1") ' Presumed P1 was supposed to be M1 ?
    .Value = "SumCode"
    .Font.Bold = True
    End With
    End With
    End Sub
    Sub Range1Update()
    'This routine updates the range "Range1" to provide an effective table for customer SumCode
    ActiveWorkbook.Names("Range1").Delete
    With Worksheets("Reference").Range("D1")
    Range(.Offset(0, 0), .Offset(0, 2).End(xlDown)).Name = "Range1"
    End With
    With Worksheets("Reference").Range("D1")
    .Sort Key1:=Range("Range1"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False _
    , Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
    DataOption2:=xlSortNormal
    End With
    End Sub
    [/VBA]

Posting Permissions

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