Solved

Need help with offset formaul using VBA assigned Ranges?

Posted on 2012-03-12
9
225 Views
Last Modified: 2012-03-25
Public Paid_Day As Range, Paid_Day_Calc As Range    

With Freight
        FreightR1 = .Cells(Rows.Count, 1).End(xlUp).Row

        .Columns("G").Insert
        .Cells(1, 7).Value = "Day"

      Set Paid_Day = .Range(.Cells(1, 7), .Cells(FreightR1, 7))
      Set Paid_Day_Calc = .Range(.Cells(2, 7), .Cells(FreightR1, 7))

         With Paid_Day_Calc
               .Value = Application.Day(.Offset(, -1))
         End With
    End With

My Day calculation is bringing up an object error, What am i doing wrong?
0
Comment
Question by:bearblack
  • 5
  • 3
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37712172
I do not think there is a .day worksheetfunction
0
 
LVL 2

Author Comment

by:bearblack
ID: 37712565
There is in excel
0
 
LVL 2

Author Comment

by:bearblack
ID: 37712761
Here is another one that I would like to use Value instead of formulaR1C1

Vendor, Paid_Day, and Freight_Cost are all defined ranges

I had to do this to make it work which defeats the purpose:

            Vendor.Name = "Vendor"
            Paid_Day.Name = "Paid_Day"
            Freight_Cost.Name = "Freight_Cost"

.Range(.Cells(SummaryR3 + 1, 2), .Cells(SummaryR4, SummaryC3))
                .FormulaR1C1 = "=sumproduct((Vendor=RC1)*(Paid_Day=R" & SummaryR3 & "C),Freight_Cost)"
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37713155
This isn't quite like VB/C#/+, you can't call a variable Public or Private inside of a sub routine.  Those are module-level calls.  Just 'Dim' your variable.  Or, outside of your sub, make it public there.

Also, I wouldn't use 'Application.Day' functionality either.  You're getting an error from that as well.  You would need to use 'VBA.Day()' instead.  There is a worksheetfunction DAY().  All projects will, by default, reference the Office OM, so you could leave off the 'VBA.' portion, but I always leave it on, just in case, as I've had it do funky things if left off.

For your other question, what are 'SummaryR3', 'SummaryR4', and 'SummaryC3' values?

HTH

Regards,
Zack Barresse
0
 
LVL 2

Author Comment

by:bearblack
ID: 37714567
The Public is outside the sub routine. The 'SummaryR3', 'SummaryR4', and 'SummaryC3' values are Row and column counts.

        SummaryR4 = Summary.Cells(Rows.Count, 1).End(xlUp).Row
        SummaryC3 = Summary.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
0
 
LVL 2

Author Comment

by:bearblack
ID: 37714587
With Freight
        Vendor.Copy Summary.Cells(6, 1)
        .Columns("G").Insert
        .Cells(1, 7).Value = "Day"
       
      Set Paid_Day = .Range(.Cells(1, 7), .Cells(FreightR1, 7))
      Set Paid_Day_Calc = .Range(.Cells(2, 7), .Cells(FreightR1, 7))
     
        With Paid_Day_Calc
            .Value = VBA.Day(.Offset(, -1))
            .NumberFormat = "General"
        End With
    End With

The error I get now is Type mismatch once changed
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37715278
Can you tell us where the error is debugging at?  And can you post all of your code instead of snippets?

Zack
0
 
LVL 2

Author Comment

by:bearblack
ID: 37720163
This is where it is buggin out:

 
          .Value = VBA.Day(.Offset(, -1))

Public Summary As Worksheet, Freight As Worksheet
Public SummaryR1 As Long, SummaryR2 As Long, SummaryR3 As Long, SummaryR4 As Long
Public SummaryC1 As Long, SummaryC2 As Long, SummaryC3 As Long
Public FreightR1 As Long, FreightR2 As Long, FreightR3 As Long
Public FreightC1 As Long, FreightC2 As Long, FreightC3 As Long
Public Freight_Ttl As Long
Public MinPost As Date, MaxPost As Date
Public Raw_Data As Range, Raw_Data_Filter As Range
Public Freight_Cost As Range, Freight_Credit As Range, Vendor As Range, Source_Doc As Range
Public Sum_Vendor As Range, Sum_Vendor_Data As Range, Sum_Freight_Calc As Range, Sum_Sum_Credit_Calc As Range
Public Paid_Day As Range, Paid_Day_Calc As Range, Sum_Paid_Day As Range, Sum_Paid_Day_Copy As Range



Sub Set_Tabs()
    
    Set Summary = Sheets("Summary")
    Set Freight = Sheets("Freight Raw Data")
    
  Call Set_Ranges

End Sub

Sub Set_Ranges()

    With Freight
        FreightR1 = .Cells(Rows.Count, 1).End(xlUp).Row
        FreightC1 = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

        Set Raw_Data = .Range(.Cells(1, 1), .Cells(FreightR1, FreightC1))
        Set Raw_Data_Filter = .Range(.Cells(2, 1), .Cells(FreightR1, FreightC1))
        Set Freight_Cost = .Range(.Cells(1, 7), .Cells(FreightR1, 7))
        Set Freight_Credit = .Range(.Cells(1, 8), .Cells(FreightR1, 8))
        Set Source_Doc = .Range(.Cells(1, 9), .Cells(FreightR1, 9))
        Set Vendor = .Range(.Cells(1, 11), .Cells(FreightR1, 11))

    End With
End Sub

Sub Run_Freight_Report2()

    Application.ScreenUpdating = False

    Call Set_Tabs
    

' Delete Named Ranges
    On Error Resume Next
        For Each NameX In ActiveWorkbook.Names
            NameX.Delete
        Next
    On Error GoTo 0
    
    Summary.Cells.Clear
    
    With Freight
        Vendor.Copy Summary.Cells(6, 1)
        .Columns("G").Insert
        .Cells(1, 7).Value = "Day"
        
      Set Paid_Day = .Range(.Cells(1, 7), .Cells(FreightR1, 7))
      Set Paid_Day_Calc = .Range(.Cells(2, 7), .Cells(FreightR1, 7))
      
        With Paid_Day_Calc
            .Value = Day(.Offset(, -1))
            .NumberFormat = "General"
        End With
    End With
    
    Vendor.Copy Summary.Cells(6, 1)
    
    With Summary
        SummaryR1 = .Cells(Rows.Count, 1).End(xlUp).Row
        
      Set Sum_Vendor = .Range(.Cells(6, 1), .Cells(SummaryR1, 1))
        
            With Sum_Vendor
                .Activate
                .RemoveDuplicates Columns:=1, Header:=xlYes
                .Sort Key1:=.Cells(6, 1), Order1:=xlAscending, Header:=xlYes
            End With
        If .Cells(7, 1) = "" Then: .Rows(7).Delete
        
        SummaryR1 = .Cells(Rows.Count, 1).End(xlUp).Row
        
        .Cells(6, 1).Value = "Vendor"
        .Cells(6, 2).Value = "Freight"
        .Cells(6, 3).Value = "Credit"
        
        SummaryC1 = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
        
        Set Sum_Vendor_Data = .Range(.Cells(6, 1), .Cells(SummaryR1, SummaryC1))
        Set Sum_Vendor = .Range(.Cells(6, 1), .Cells(SummaryR1, 1))
        Set Sum_Freight_Calc = .Range(.Cells(7, 2), .Cells(SummaryR1, 2))
        Set Sum_Credit_Calc = .Range(.Cells(7, 3), .Cells(SummaryR1, 3))
        
        With Sum_Freight_Calc
            .Value = Application.SumIf(Vendor, .Offset(, -1), Freight_Cost)
            .ColumnWidth = 11
            .NumberFormat = "$#,##0_);[Red]($#,##0)"
        End With
        With Sum_Credit_Calc
            .Value = Application.SumIf(Vendor, .Offset(, -2), Freight_Credit)
            .ColumnWidth = 11
            .NumberFormat = "$#,##0_);[Red]($#,##0)"
        End With
        With Sum_Vendor_Data
            .Borders.Weight = xlMedium
            .Borders(xlInsideHorizontal).Weight = xlThin
        End With
        With .Range(.Cells(6, 1), .Cells(6, SummaryC1))
            .Borders(xlEdgeBottom).Weight = xlMedium
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.ColorIndex = 34
        End With
        Sum_Vendor_Data.Sort Key1:=.Cells(6, 2), Order1:=xlDescending, Header:=xlYes
        .Cells(4, 2).Value = Application.Sum(Sum_Freight_Calc)
        .Cells(4, 3).Value = Application.Sum(Sum_Credit_Calc)
        With .Range(.Cells(4, 2), .Cells(4, SummaryC1))
            .Borders.Weight = xlMedium
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.ColorIndex = 34
            .NumberFormat = "$#,##0_);[Red]($#,##0)"
        End With
        .Range(.Cells(6, 2), .Cells(6, SummaryC1)).Copy (.Cells(6, 7))
        .Cells(7, 6).Value = "Accrue"
        .Cells(8, 6).Value = "UseTax"
        With .Range(.Cells(7, 6), .Cells(8, 6))
            .Borders.Weight = xlMedium
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .Interior.ColorIndex = 34
            .ColumnWidth = 13
        End With
        With .Range(.Cells(7, 7), .Cells(8, 7))
            .Value = Application.SumIf(Source_Doc, .Offset(, -1), Freight_Cost)
        End With
        With .Range(.Cells(7, 8), .Cells(8, 8))
            .Value = Application.SumIf(Source_Doc, .Offset(, -2), Freight_Credit)
        End With
        With .Range(.Cells(7, 7), .Cells(8, 8))
            .Borders.Weight = xlMedium
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .NumberFormat = "$#,##0_);[Red]($#,##0)"
            .ColumnWidth = 13
        End With
        Paid_Day.Copy (.Cells(15, 6))
        
        SummaryR2 = .Cells(Rows.Count, 6).End(xlUp).Row
        .Range(.Cells(15, 6), .Cells(SummaryR2, 6)) _
                .RemoveDuplicates Columns:=1, Header:=xlYes
                
        SummaryR2 = .Cells(Rows.Count, 6).End(xlUp).Row
        
      Set Sum_Paid_Day = .Range(.Cells(15, 6), .Cells(SummaryR2, 6))
      Set Sum_Paid_Day_Copy = .Range(.Cells(16, 6), .Cells(SummaryR2, 6))
      
        Sum_Paid_Day.Sort Key1:=.Cells(15, 6), Order1:=xlAscending, Header:=xlYes
        .Range(.Cells(6, 1), .Cells(6, SummaryC1)).Copy
        .Cells(15, 6).PasteSpecial (xlPasteFormats)
        
        .Cells(15, 7).Value = "Freight"
        .Cells(15, 8).Value = "Credit"
        
        SummaryC2 = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
          
        With .Range(.Cells(16, 7), .Cells(SummaryR2, 7))
            .Value = Application.SumIf(Paid_Day, .Offset(, -1), Freight_Cost)
        End With
        With .Range(.Cells(16, 8), .Cells(SummaryR2, 8))
            .Value = Application.SumIf(Paid_Day, .Offset(, -2), Freight_Credit)
        End With
        With .Range(.Cells(16, 6), .Cells(SummaryR2, 8))
            .Borders.Weight = xlMedium
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .NumberFormat = "$#,##0_);[Red]($#,##0)"
            .ColumnWidth = 13
        End With
        .Range(.Cells(16, 6), .Cells(SummaryR2, 6)).NumberFormat = "General"
        .Range(.Cells(4, 2), .Cells(4, 3)).Copy
            .Cells(13, 7).PasteSpecial (xlPasteFormats)
        
        .Cells(13, 7).Value = Application.Sum(.Range(.Cells(16, 7), .Cells(SummaryR2, 7)))
        .Cells(13, 8).Value = Application.Sum(.Range(.Cells(16, 8), .Cells(SummaryR2, 8)))

        Sum_Vendor.Copy (.Cells(SummaryR1 + 6, 1))
        Sum_Paid_Day_Copy.Copy
        
        SummaryR3 = SummaryR1 + 6
        .Cells(SummaryR3, 2) _
            .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Transpose:=True
        
        SummaryR4 = .Cells(Rows.Count, 1).End(xlUp).Row
        SummaryC3 = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
               
            With .Range(.Cells(SummaryR3, 2), .Cells(SummaryR3, SummaryC3))
                .Interior.ColorIndex = 34
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.Weight = xlMedium
                .Borders(xlInsideVertical).Weight = xlThin
            End With
            Vendor.Name = "Vendor"
            Paid_Day.Name = "Paid_Day"
            Freight_Cost.Name = "Freight_Cost"
            With .Range(.Cells(SummaryR3 + 1, 2), .Cells(SummaryR4, SummaryC3))
                .FormulaR1C1 = "=sumproduct((Vendor=RC1)*(Paid_Day=R" & SummaryR3 & "C),Freight_Cost)"
                .Copy
                .PasteSpecial (xlPasteValues)
                .NumberFormat = "$#,##0_);[Red]($#,##0)"
                .Borders.Weight = xlMedium
                .Borders(xlInsideVertical).Weight = xlThin
                .Borders(xlInsideHorizontal).Weight = xlThin
            End With

        Paid_Day.Delete
            
    End With
    ActiveWorkbook.Save
    Summary.Copy
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 37722486
I see, you're trying to use a formula on an entire range.  It won't work like that.  Instead you could use something like this, which uses a formula then sets the value to static...


            .FormulaR1C1 = "=DAY(RC[-1])"
            .Value = .Value

HTH

Regards,
Zack Barresse
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
And OR formula 5 22
Excel Formula 16 45
Excel Charts: How is this Chart made? 8 28
Error 1004: Application-defined or object-defined error 10 17
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question