Solved

Need help with offset formaul using VBA assigned Ranges?

Posted on 2012-03-12
9
220 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now