Solved

Struggling with variables

Posted on 2013-12-26
20
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 3
20 Comments
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39740892
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:Michael Fowler
ID: 39740984
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
ID: 39741236
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39741264
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:Michael Fowler
ID: 39741271
What error are you getting and at what line is it occurring
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39741277
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:Michael Fowler
ID: 39741282
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
ID: 39741285
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:Michael Fowler
ID: 39741292
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
ID: 39741297
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
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 39741315
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
ID: 39741322
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
ID: 39741367
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
ID: 39744436
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
ID: 39744437
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39746057
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
ID: 39746117
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
ID: 39746264
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
ID: 39746367
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
ID: 39746416
You are welcome.  Have a great day!

Scott C
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

617 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