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]

I guess the error occurs when test1 is not found in "Storage". To handle that, you could consider modifying your code:

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:=xlFormulas _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not iFound Is Nothing Then
    iRow = iFound.Row
    'copy the data to the database
    ws.Cells(iRow, 4).Value = Range("hundred")
End If

 

[hotpants49]

Using that code it no longer has the error however it fails to write anything to the database.

 

[hyat]

Well what the code you posted was trying to do is:

1. Assign the value of the "Key" range to a variable called test1
2. Find test1 in the "Storage" range
3. If test1 is found in the "Storage" range, then assign that row to a variable called iRow
4. In column "D", row iRow of the "Data" sheet, enter the value of the "hundred" range.

Is this not what you require?

 

[hotpants49]

Yes that is exactly what I want, although nothing copies when the macro is run

 

[hyat]

Hmm.. And does the "Storage" range contain the value of the "Key" range?

E.g. if "Key" is 406911, then does the "Storage" range include 406911 somewhere?

 

[hyat]

If you need the "Key" value to be stored in "Storage" if it is not found, then maybe:

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:=xlFormulas _
              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not iFound Is Nothing Then
    iRow = iFound.Row
    'copy the data to the database
    ws.Cells(iRow, 4).Value = Range("hundred")
Else
    ws.Cells(Range("Storage").SpecialCells(xlCellTypeBlanks).Cells(1).Row, 4).Value = Range("hundred")
    Range("Storage").SpecialCells(xlCellTypeBlanks).Cells(1) = test1
End If

 

[hotpants49]

yes, in the first column. the range is from A3:Q1340 with column A having the ID numbers and the rest are blank for the information to be put in them (the first two rows are headers).

The ID numbers aren't individually entered, they use a formula (the date and shift number are in columns R & S) so the acutal cell has =R3&S3 in it. could this effect it? The "Key" range's cell has =S2&S3 in it (on a different sheet). I am not sure if excel looks at the formulas or the end result of the function.

 

[hyat]

Oh ok. Maybe this'll work:

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
    ws.Cells(iRow, 4).Value = Range("hundred")
End If

The Range.Find method was looking at formulas before, now it's looking at values.

 

[hotpants49]

Works great thanks makes me feel like an idiot though for not seeing that xlformulas bit...was stuck on this for hours haha.

Thanks so much

The only other bit left to do is to get it to load information from that database now. In my old sheet it was all on a userform so i made a sub for DTpicker1 (change) but how would I go about doing this for when a cells value changes (like "Key")?