Solved

Struggling with variables

Posted on 2013-12-26
20
160 Views
Last Modified: 2013-12-30
Hi again,
I'm really struggling here for the past few hours to the point where I can't remember my starting point.

I have some code that I think should work, but Excel has other Ideas.
Public Sub UpdateOrders()
On Error GoTo Err_Ctrl

'Exit sub if someone has the CallOff workbook open
    If IsFileOpen(GetMyPath() & "Call_Offs.xlsm") Then
        MsgBox "File is already open on another workstation!" & vbCrLf & _
        "Please try again later and use the Update ShipNote option"
        Exit Sub
    End If

Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim ColNum As Long
Dim Pnum As String
Dim RowNum As Long
Dim OrderDate As Date
Set WBK1 = ThisWorkbook
Set WBK2 = Workbooks.Open(GetMyPath() & "Call_Offs.xlsm")
Set WS1 = WBK1.Sheets("qryUploadOrders")
Set WS2 = WBK2.Sheets("NonAutoBase")

Do Until WS1.Range("A2") = ""
    Pnum = WS1.Range("A2")
    OrderDate = WS1.Range("B2")

'Find column ref
    ColNum = WS2.Range("DelDatez").Find(OrderDate, lookat:=xlWhole)
    
'Find row ref
    RowNum = WS2.Range("ProdPnumZ").Find(Pnum, lookat:=xlWhole)
    
    WS2.Range(RowNum, ColNum) = WS1.Range("C2")
    WS1.Range("A2").EntireRow.Delete
Loop

Exit_Sub:
    Exit Sub
Err_Ctrl:
MsgBox Err.Description
    Resume Exit_Sub
End Sub

Open in new window

I think it may be that I'm declaring the variables wrongly, and I've tried any number of types, but I keep getting the "Object variable or With block not set" error.

I'm trying to put a value into a cell where row number is equal to a partnumber and the column number is equal to a date, then when the data has been transferred, delete the row and loop until the orders have been updated (cleared). I hope you can see what I am trying to do as the workbooks are rather large and my internet connection would take a week to upload them.

As always any help is appreciated.
0
Comment
Question by:Stephen Byrom
  • 12
  • 5
  • 3
20 Comments
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Update
After searching on here and Google I have changed a few things to try and get this to work but to no avail.

new code (still throws up error)
Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WBK1 = ThisWorkbook
Set WBK2 = Workbooks.Open(GetMyPath() & "Call_Offs.xlsm", , , , "MandyM00")
Set WS1 = WBK1.Sheets("qryUploadOrders")
Set WS2 = WBK2.Sheets("NonAutoBase")

Dim myRng As Range
Dim ResRow As Integer
Dim ResCol As Integer
Dim Trget As Range

Set myRng = WS2.Range("NonAutoTable")

Do Until WS1.Range("A2") = ""
     ResRow = Application.Match(WS1.Range("A2").Value, myRng.Columns(1, 1), 0)
     ResCol = Application.Match(WS1.Range("B2").Value, myRng.Rows(1, 1), 0)

     Trget = myRng(ResRow, ResCol)
     Trget.Value = WS1.Range("C2").Value

     WS1.Range("A2").EntireRow.Delete
Loop

Open in new window

0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
You cannot use the range object with two numbers. The range object eg

WS2.Range(RowNum, ColNum)

or

myRng(ResRow, ResCol)

needs to use to be in the format column letter + row number eg Range("A1")

To use two numbers easiest option is to replace Range with Cells ie

WS2.Cells(RowNum, ColNum)

Michael
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Thanks for your comment Michael,
I see my error, although the "cells" thing still threw up the object variable etc etc error.

I came up with this,
    WS2.Range("A & RowNum", ColNum & RowNum) = WS1.Range("C2")

But it's still not right.
Maybe you would have a better Idea of the correct syntax.

Thanks for your time
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
I reverted back to my original code block and tried the "Cells" ref (row24)
Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim ColNum As Long
Dim Pnum As String
Dim RowNum As Long
Dim OrderDate As Date
Set WBK1 = ThisWorkbook
Set WBK2 = Workbooks.Open(GetMyPath() & "Call_Offs.xlsm")
Set WS1 = WBK1.Sheets("qryUploadOrders")
Set WS2 = WBK2.Sheets("NonAutoBase")

Do Until WS1.Range("A2") = ""
    Pnum = WS1.Range("A2")
    OrderDate = WS1.Range("B2")

'Find column ref
    ColNum = WS2.Range("DelDatez").Find(OrderDate, lookat:=xlWhole)

'Find row ref
    RowNum = WS2.Range("ProdPnumZ").Find(Pnum, lookat:=xlWhole)
    
    WS2.Cells(ColNum & RowNum) = WS1.Range("C2")
    WS1.Range("A2").EntireRow.Delete
Loop

Open in new window

But still no joy
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
What error are you getting and at what line is it occurring
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Error is "Object variable or With block variable not set"

Which makes me think I'm declaring the variables incorrectly.

I could never figure out how to determine which line it breaks at. :(
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
If you run the script in the VBA script editor it will highlight the affected line or you could place a break point at the top of the code and step through.

Can you post a copy of the workbook?

Michael
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Hi
It breaks at this line

'Find column ref
    ColNum = WS2.Range("DelDatez").Find(OrderDate, lookat:=xlWhole)

As I said at the top, the two workbooks are rather large and here in the countryside I'm lucky if i can stay connected long enough to read my mails let alone download things :(
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Colnum is a long value but the Find function is returning a range try

 ColNum = WS2.Range("DelDatez").Find(OrderDate, lookat:=xlWhole).Column

Michael
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
I changed it to this
    ColNum = WS2.Range("B1:CH1").Find(OrderDate, , lookat:=xlWhole).Column

'Find row ref
    RowNum = WS2.Range("ProdPnumZ").Find(Pnum, , lookat:=xlWhole).Row
    
    WS2.Cells(ColNum & RowNum) = WS1.Range("C2")
    WS1.Range("A2").EntireRow.Delete

Open in new window

and added row also at the end of the rownum line, but I still get the same error and it still breaks at 'Find col ref. I even hard coded the date range in case that was a problem
0
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.

 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Place a break point at this line and then when the code breaks highlight WS2.Range("B1:CH1").Find(OrderDate, , lookat:=xlWhole)
right click and select Add Watch...

Does it return a value?

You could also try
ColNum = WS2.Range("B1:CH1").Find(OrderDate, lookat:=xlWhole).Column

Other than this I am at a loss and would need to see the workbook. Anyway to cut it down so you can post it?

Michael
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
OK
I tried that but still no joy. (the Watch wanted an expression?)

I'm goina get some breakfast and then take an axe to 75% of both workbooks.
I'll then hopefully be able to upload them later.

Thanks for your perseverance.
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
OK here goes.
I've trimmed them down to the bare bones.
Hopefully you will be able to see what I'm trying to do a little clearer now.

I know I needn't mention this but change the file path in the VBA of WB2 to your location.
I sometimes overlook that little detail, but that's me.

Thanks again for your time
Call-Offs.xlsm
Interface2.xlsm
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
OK,
I'm still struggling here with "Object variable or With block variable not set" in my code.
I have updated the code behind the workbook samples (attached) and hope that someone can find the time to sort this out for me.
I am trying to place the quantity of WKB1 WS1 C2  to a specific cell in WKB2 WS2 depending on the find criteria in my code, but struggling badly.

Any help is much appreciated as always.
Interface2.xlsm
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
Pnum = WS1.Range("A2")

change to :

Pnum = WS1.CELLS(2,1).VALUE

You're trying to assign a RANGE to a STRING.


Scott C
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Thanks for your pointer Scott, but I still get the Error "Object variable or With block variable not set". I have been tinkering around with an old loop routine I've had kicking around for some years now and it almost works in as much as it finds the correct row and column number and inserts the quantity but it keeps adding the quantity to itself in the cell and gets stuck in the loop. Maybe you could help on getting out of the loop? Or should I ask a separate question? Please see the code and I have attached the updated workbooks.
Option Explicit

Public Sub UpdateOrders()
On Error GoTo Err_Ctrl

Dim WBK1 As Workbook
Dim WBK2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WBK1 = ThisWorkbook
Set WBK2 = Workbooks.Open("C:\Users\StevieB\Desktop\Call_Offs.xlsm")
Set WS1 = WBK1.Sheets("qryUploadOrders")
Set WS2 = WBK2.Sheets("NonAutoBase")
Dim P As Long
Dim R1 As Long
Dim C As Long
Dim R2 As Long
Dim insDate As Long

WS1.Activate
    P = 2
        Do
            R1 = 1
                Do
                    R1 = R1 + 1
                Loop Until WS1.Cells(P, 1) = WS2.Cells(R1, 1) Or WS2.Cells(R1, 1) = ""
                    insDate = WS1.Range("C2")
                    R2 = 1
                    C = 2
                    Do Until WS2.Cells(R2, C) > insDate
                        If WS2.Cells(R2, C) = insDate Then
                            WS2.Cells(R1, C) = WS2.Cells(R1, C) + WS1.Range("B2").Value
                        Else
                            C = C + 1
                        End If
                    Loop
            WS1.Range(P & ":" & P).Select
            WS1.Range(P & ":" & P).Delete
        Loop Until WS1.Cells(P, 1) = ""

Err_Ctrl:
MsgBox Err.Description
    Exit Sub
End Sub

Open in new window

Thanks again for your time.
Interface.xlsm
Call-Offs.xlsm
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 500 total points
Comment Utility
Here you go.
I added a new sub and assigned to your button.


Further...
I assume every item is in BOTH workbooks and every date will find a match.
Otherwise, you will have to put in some sort of message box stating that a NUMBER and/or DATE found no match.

Scott C
Interface.xlsm
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
Comment Utility
Wow!
Thanks so much for your time and instruction.

Regarding items in both books etc. The interface book is where I dump the orders to from MS Access, and then the button updates the Excel workbook (WBK2) that everyone else looks at. The parts are always in WBK2 and get updated anytime a new part comes on line. The Interface (WBK1) can only have items that are in WBK2. There could be a scenario where the order date is not found if the order is for more than 3 months into the future, but that would be the exception rather than the rule.

Thanks again for your time and expertise.
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
You are welcome.  Have a great day!

Scott C
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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