Object Required?

J

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
I can't get this to work: Set NewPN = LastPN.Offset(1).Value

I've tried making different changes, like removing 'set' or changing the "LastPN.Offset(1).Value" to "Right(frmPartLoc.cboDataYr.Value, 2) & Right(frmPartLoc.cboBuyer.Value, 1) & "-" & Right(LastPN, 3) + 1" and everything I try to gives gives me a differen error message. It shows the correct data for "LastPN.Offset(1).Value" but shows "NewPN = Nothing". As it is now, it gives me and Object Required error.

Can someone help?

Code: 
Sub CostChangeForm()
'
    Dim LastPN As Range, NewPN As Range, CurrCost As Double, CurrDate As Date, RemVol As Double, AnnDem As Double, OneYr As Double, MinDate As Double, NoOfRows As Long, NoOfRowsA As Long, NoOfRowsB As Long, NoOfRowsC As Long, wsForm As Worksheet, LastRowInc As Range
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
' open data files
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "1.10.2.2.xls"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "5.13.3.xls"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "23.16.xls"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "2011 PSNA Purchasing Cost Change Log.xls"
   
' Activate Cost Change Form
    Windows("Cost Change Form.xls").Activate
    Sheets("Less Than $1k Increase").Select

' New Project Number
    Windows("2011 PSNA Purchasing Cost Change Log.xls").Activate
    Sheets("External Cost Changes").Select
    
    If frmPartLoc.cboType.Value = "Savings" Then
    Set LastPN = Range("D:D").Find("Subtotal Actual").Offset(0, -3)
    Set LastPN = LastPN.End(xlUp)
    End If
    
    If frmPartLoc.cboType.Value = "Increase" Then
    Set LastPN = Range("D:D").Find("Economic Increases Actual").Offset(0, -3)
    Set LastPN = LastPN.End(xlUp)
    End If
    
    LastPN.Offset(1).Value = Right(frmPartLoc.cboDataYr.Value, 2) & Right(frmPartLoc.cboBuyer.Value, 1) & "-" & Right(LastPN, 3) + 1
  
' Activate 1.10.2.2, find most recent date, cost for that date
    Windows("1.10.2.2.xls").Activate
    NoOfRowsA = ActiveSheet.UsedRange.Rows.Count
    myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
    Range("K22").Formula = "=MAX(E4:E" & NoOfRowsA & ")"
    Range("L22").Formula = "=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)"
    CurrCost = Range("L22").Value
    CurrDate = Range("K22").Value
    ActiveWindow.Close
    
' Activate 23.16, format data
    Windows("23.16.xls").Activate
    myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
    NoOfRowsB = ActiveSheet.UsedRange.Rows.Count
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("J1").Select
    Selection.Copy
    Range("A2:E" & NoOfRowsB).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yy;@"
    Range("A1").Select
    Cells.Replace what:="1/0/1900", Replacement:="", lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
' Sum data in col B
    Range("J11").Formula = "=SUM(B2:B" & NoOfRowsB & ")"
    RemVol = Range("J11").Value
    
' Find max date -subtract 1 year- find min date
    Range("J12").Formula = "=MAX(A2:A" & NoOfRowsB & ")"
    Range("J13").Formula = "=J12-364"
    Range("J14").Formula = "=MIN(A2:A" & NoOfRowsB & ")"
    OneYr = Range("J13").Value
    MinDate = Range("J14").Value
    ActiveWindow.Close
    
' Activate 5.13.3,calculate annual demand
    Windows("5.13.3.xls").Activate
    myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
    NoOfRowsC = ActiveSheet.UsedRange.Rows.Count
    Range("M6").Value = OneYr
    Range("M7").Value = MinDate
    Range("G1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M4:N4").Select
    ActiveSheet.Paste
    Range("M5").Value = ">=" & Range("M6").Value
    Range("N5").Value = "<=" & Range("N7").Value
        Range("M8").Formula = "=DSUM(A1:I" & NoOfRowsC & ",3,M4:N5)"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("M9").Value = RemVol
    Range("M10").Formula = "=SUM(M8:M9)"
    AnnDem = Range("M10").Value
    ActiveWindow.Close
    
' Activate Cost Change Form, add data
    Windows("Cost Change Form.xls").Activate
    Sheets("Less Than $1k Increase").Select
    
    If Range("N2").Value <> "" Then
    Set NewPN = Range("N2").Value
    Else: Set NewPN = LastPN.Offset(1).Value
    End If
    Range("N2").Value = NewPN
    Range("A16").End(xlUp).Offset(1, 0).Select
    Selection.Value = frmPartLoc.txtSupplier.Value
    Selection.Offset(0, 1).Value = frmPartLoc.txtPart.Value
    Selection.Offset(0, 2).Value = frmPartLoc.txtDesc.Value
    Selection.Offset(0, 3).Value = CurrCost
    Selection.Offset(0, 4).Value = CurrDate
    Selection.Offset(0, 5).Value = frmPartLoc.txtCost.Value
    Selection.Offset(0, 6).Value = frmPartLoc.txtDate.Value
    Selection.Offset(0, 7).Value = AnnDem
    Selection.Offset(0, 8).Value = RemVol
    Selection.Offset(0, 11).Value = frmPartLoc.txtDetails.Value
    ActiveWorkbook.Save
  
' Delete original file copies
    If Dir(ThisWorkbook.Path & "\" & "1.10.2.2.xls") <> "" Then _
            Kill ThisWorkbook.Path & "\" & "1.10.2.2.xls"

    If Dir(ThisWorkbook.Path & "\" & "5.13.3.xls") <> "" Then _
            Kill ThisWorkbook.Path & "\" & "5.13.3.xls"

    If Dir(ThisWorkbook.Path & "\" & "23.16.xls") <> "" Then _
            Kill ThisWorkbook.Path & "\" & "23.16.xls"
    
' Activate Cost Change Log to add data
    Windows("2011 PSNA Purchasing Cost Change Log.xls").Activate
    
' Savings Data
    If frmPartLoc.cboType.Value = "Savings" Then
    Range("D:D").Find("Subtotal Actual").Offset(0, -3).End(xlUp).Offset(1, 0).Select
    Selection.Offset(0, 1).Value = frmPartLoc.txtPart.Value
    Selection.Offset(0, 2).Value = frmPartLoc.cboReason.Value
    Selection.Offset(0, 3).Value = frmPartLoc.txtDetails.Value
   End If

' Increase Data
    If frmPartLoc.cboType.Value = "Increase" Then
    Range("D:D").Find("Economic Increases Actual").Offset(0, -3).End(xlUp).Offset(1, 0).Select
    Selection.Offset(0, 1).Value = frmPartLoc.txtPart.Value
    Selection.Offset(0, 2).Value = frmPartLoc.cboReason.Value
    Selection.Offset(0, 3).Value = frmPartLoc.txtDetails.Value
   End If
   
   ActiveWorkbook.Save
   ActiveWindow.Close
   
    MyPath = ThisWorkbook.Path & "\"
    
    If Right(frmPartLoc.cboBuyer.Value, 1) = "A" Then
    Name MyPath & "11A\New Folder" As MyPath & "11A\" & NewPN
    End If
    
    If Right(frmPartLoc.cboBuyer.Value, 1) = "B" Then
    Name MyPath & "11B\New Folder" As MyPath & "11B\" & NewPN
    End If
    
    If Right(frmPartLoc.cboBuyer.Value, 1) = "D" Then
    Name MyPath & "11D\New Folder" As MyPath & "11D\" & NewPN
    End If
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi,

NewPN is declared as a Range, so you must use the Set keyword and return a reference to a range. Knock off the .Value:
Code: 
Dim NewPN As Range
'
'
'
Set NewPN = LastPN.Offset(1)

If you want to return the value from NewPN (work with a literal rather than a Range reference) then declare NewPN as a variant and remove the Set keyword:
Code: 
Dim NewPN As Variant
'
'
'
NewPN = LastPN.Offset(1).Value
 
Last edited:
Upvote 0
Colin Legg said:
Hi,

NewPN is declared as a Range, so you must use the Set keyword and return a reference to a range. Knock off the .Value:
Code: 
Dim NewPN As Range
'
'
'
Set NewPN = LastPN.Offset(1)

If you want to return the value from NewPN (work with a literal rather than a Range reference) then declare NewPN as a variant and remove the Set keyword:
Code: 
Dim NewPN As Variant
'
'
'
NewPN = LastPN.Offset(1).Value
Click to expand...

Beautiful! Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Listing file names based on column value
Replies
0
Views
450
CBlauv
C
G
  • Question Question
VBA Windows 10 code crashes excel when executing. Works when stepping through. Runs on Windows 7 fine.
Replies
1
Views
1K
DanteAmor
DanteAmor
B
  • Question Question
VBA Problems...
Replies
2
Views
597
Blytheee
B
B
  • Question Question
VBA to check for file type
2
Replies
10
Views
47K
Fluff
Fluff
S
  • Question Question
Function is not Working
Replies
2
Views
315
sriraghav
S

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top