VB - Copying data to a particular row using two conditions

[hotpants49]

I am looking for some help with VB coding for an 'Update' button that saves information from one sheet (where users enter the data into specific cells) to a database sheet which will be hidden from user view so they don't break it. I would also like it to retrieve data from this database sheet whenever someone changes the date or shift number so they can see that it has already been entered and do not attempt to make a duplicate entry.

The spreadsheet I am working on is designed to track the denomination of currency we take through our 5 tills e.g. $100, $50, $20 etc. Each till is counted 3 times per day (we call these shifts). I have previously made a spreadsheet for keeping track of our total sales for each day using a userform.

I tried copying the VB code from that spreadsheet over to this sheet but I am unable to get it to work using two criteria for finding the correct row (all the rows in the database tab have the date in column A and the shift number in column B). It works fine just finding the date, but as I said we need it to record 3 shifts per day.

I also tried using a sort of ID number instead of date and shift number. I combined the date & shift number together in the same cell so 28/5/11 Shift 1 becomes 406911 but i couldn't get it to work.


Here is the code for the 'Update' button which saves the information to the database on another sheet, where:
"Data" is the sheet where I want to store the data.
"Key" is the named range of the ID number (date & shift number combined).
"Storage" is the named range inside the "Data" sheet where I want it stored, with the first column in the range being the ID number.
"Hundred" is the named range for the $100 currency cell.

The error I am getting is "Object variable or with block variable not set" when it gets to the irow = range("Stor.... etc.

Dim iRow As Long
Dim ws As Worksheet
Dim test1 As Long
Set ws = Worksheets("Data")
test1 = Range("Key")


 iRow = Range("Storage").Find(What:=test1, LookIn:=xlFormulas _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row


              
'copy the data to the database
ws.Cells(iRow, 4).Value = Range("hundred")

Either way is fine (using a two column date & shift number system, or using a single column id number).

If it is easier without named ranges then:
"Key" = worksheets("Data Entry").range("V2")
"Hundred" = worksheets("Data Entry").range("Q9")
Storage I would like to keep as a named range if possible as I will be extending the sheet downwards when we have used all the days/shifts in it currently

Thankyou for your time

 

[hyat]

Glad that's sorted. Well, there are two ways I can think of. The value of "Key" will change given that cells S2 and/or S3 change. So you could have, for your "Data Entry" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("S2:S3")) Is Nothing Then Exit Sub
'code for loading information from database
End Sub

However even that might not help if S2 and S3 change as a result of formulas. So the second way would be, again, for your "Data Entry" sheet:

Private Sub Worksheet_Calculate()
If Range("Key").Value <> prevval Then
    'code for loading information from database
End If
prevval = Range("Key").Value
End Sub

 

[hotpants49]

I tried the first one but nothing happened when the cells changed. The shift number cell changes because it is linked to a list box so I went with the second one instead.

I am getting the error " Method 'Range' of object '_Worksheet' failed "

when it gets to Set Ifound = Range...etc

Private Sub Worksheet_Calculate()
If Range("Key").Value <> prevval Then
    'code for loading information from database
Dim iRow As Long, iFound As Range
Dim ws As Worksheet
Dim test1 As Long
Set ws = Worksheets("Data")
test1 = Range("Key")

Set iFound = Range("Storage").Find(What:=test1, LookIn:=xlValues _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not iFound Is Nothing Then
    iRow = iFound.Row
              
'copy the data to the database
Range("l1hundred") = ws.Cells(iRow, 4).Value
End If
prevval = Range("Key").Value
End If
End Sub

I'm sorry if you can't use the same find string for both macros, i was under the assumption that because you are finding the same thing only the copying the data to the database part needed to be changed

 

[hyat]

Try changing:

Set iFound = Range("Storage").Find(What:=test1, LookIn:=xlValues _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

to:

Set iFound = ws.Range("Storage").Find(What:=test1, LookIn:=xlValues _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

..so that it knows it has to look in the ws sheet.

EDIT: Also, just noticed, you've got the End If after prevval = Range("Key").Value
This will cause you problems later on.

 

[hotpants49]

Now it says "Application-defined or object-defined error"

 

[hyat]

Has Range("l1hundred") already been defined in your sheet? I.e. the sheet where this code is being run.

 

[hyat]

If you want to add it from the code instead of manually, then:

ActiveWorkbook.Names.Add Name:="l1hundred", RefersTo:=Range("A1") 'change A1 as required

 

[hotpants49]

I have defined all the names already, and I double checked then just to make sure.

Using this code (I added another couple cells to the bottom)

Private Sub Worksheet_Calculate()
If Range("Key").Value <> prevval Then
    'code for loading information from database
Dim iRow As Long, iFound As Range
Dim ws As Worksheet
Dim test1 As Long
Set ws = Worksheets("Data")
test1 = Range("Key")

Set iFound = ws.Range("Storage").Find(What:=test1, LookIn:=xlValues _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
              
If Not iFound Is Nothing Then
    iRow = iFound.Row
              
'copy the data to the database
Range("l1hundred").Value = ws.Cells(iRow, 4).Value
Range("l1fifty").Value = ws.Cells(iRow, 5).Value
Range("l1twenty").Value = ws.Cells(iRow, 6).Value
End If
prevval = Range("Key").Value
End If
End Sub

It updates the first cell ("l1hundred") but then errors. It also doesn't let me select other cells even after I have ended it and I have to restart excel before it will let me click things again, also it errors when I click the 'Update' button unless I close excel then open it again, after which it works fine until i try to change the date or shift.

 

[hotpants49]

bump

 

[hyat]

Tried your code, it's working fine here. But maybe this will help:

Private Sub Worksheet_Calculate()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
 
On Error GoTo ExitHere
 
If Range("Key").Value <> prevval Then
    'code for loading information from database
    Dim iRow As Long, iFound As Range
    Dim ws As Worksheet
    Dim test1 As Long
    Set ws = Worksheets("Data")
    test1 = Range("Key")
    Set iFound = ws.Range("Storage").Find(What:=test1, LookIn:=xlValues _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
              
    If Not iFound Is Nothing Then
        iRow = iFound.Row
              
        'copy the data to the database
        Range("l1hundred").Value = ws.Cells(iRow, 4).Value
        Range("l1fifty").Value = ws.Cells(iRow, 5).Value
        Range("l1twenty").Value = ws.Cells(iRow, 6).Value
    End If
End If

prevval = Range("Key").Value
 
ExitHere:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Also you may consider stepping through the code above using F8 to identify where it breaks and what makes it break. If this solution does not work for you, consider opening a new thread for this problem as I will be away for some time and may not be able to check this thread.

 

[hotpants49]

It now updates all the cells I have included there but afterward it still causes the 'Update' button to error. I think I may try a different approach and make a 'Load Data' button that will pull data from the database manually instead of when they change the "Key" value.

*10 minuets later*

The code works fine in button form. I think something was making it constantly refresh and that's what was causing my problems.

Thanks again for all your help, hope you have a good time on your trip