Solved

Need help with offset formaul using VBA assigned Ranges?

Posted on 2012-03-12
9
224 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ADD New Entries 7 16
Sum iF  based on a null cell 11 29
Need quick fix on defining a variable 5 18
Excel - Duplicate values in columns 4 16
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

863 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

25 Experts available now in Live!

Get 1:1 Help Now