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"
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")
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.