Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Struggling with variables

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
Stephen Byrom
Asked:
Stephen Byrom
  • 12
  • 5
  • 3
1 Solution
 
Stephen ByromAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
Stephen ByromAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Stephen ByromAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
What error are you getting and at what line is it occurring
0
 
Stephen ByromAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
0
 
clarkscottCommented:
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
 
Stephen ByromAuthor Commented:
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
 
clarkscottCommented:
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
 
Stephen ByromAuthor Commented:
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
 
clarkscottCommented:
You are welcome.  Have a great day!

Scott C
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 12
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now