Copy Cells Based On Value Of A Cell With A Fomula( a Date) That Is Less Than Another Date

AcornNut

Board Regular
Joined
Aug 19, 2014
Messages
51
Hello all! I'm very new to Excel and VBA coding. I've searched the internet and found a lot of close topics, but still haven't been able to solve my delima.
Basically, I have column "F" in "sheet1" in which the cells contain a fomula ("=Sheet3!B4", or "=Sum(Sheet3!B4:Sheet3!B55") that return date values (ex. 9/19/2014). I need a code that, when a command button is pressed... if the dates are less than 30 days away (items are expiring), will copy the cells in columns B,C, and E to the corrosponding cells on Sheet2.

Here's what I have so far:

Sub fillorder()

Dim finalrow As Integer
Dim i As Integer
Dim ExpDate As Date
Dim lngRow As Long, lngCol As Long

'Sheet2's range is the actual content area of an order form
Sheets("Sheet2").Range("B25:J44").ClearContents
'column M containes quantity of an item needed as a value = or > 0
finalrow = Sheets("Sheet1").Range("M315").End(xlUp).Row ExpDate = Date + 30

For i = 7 To finalrow
If Cells(i, 13) > 0 Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 13).Copy 'column 13 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
End If
Next i

Sheets("Sheet1").Cells(i, 6).Value = dblVal

For lngRow = 7 To finalrow
If Cells(i, 6) < ExpDate Then
Range(Cells(i, 2), Cells(i, 3)).Copy 'columns 2 and 3 contain the order code and item name respectively
Sheets("Sheet2").Range("B44").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Cells(i, 5).Copy 'column 5 contains the quantity of an item needed
Sheets("Sheet2").Range("B44").End(xlUp).Offset(0, 5).PasteSpecial xlPasteFormulasAndNumberFormats
End If

Next lngRow

End Sub

The first "If Then" works just fine, but the second "If Then" returns nothing...and there are about 6 items in my inventory that are expiring.
Any help would be greatly appreciated.
Thanks in advance.

 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Quick glance - your second if-then uses the variable "i" which has been set to a static value in the first if-then. Should that "i" be lngRow?
 
Upvote 0
Thank you. I missed it. I'm now getting another error, "Run-time error '1004': Application-defined or object-defined error.

Debug highlights: Sheets("Sheets1").Cells(lngRow,6).Value= dblVal

Not sure what's wrong.
 
Upvote 0
Thank you. I missed it. I'm now getting another error, "Run-time error '1004': Application-defined or object-defined error.

Debug highlights: Sheets("Sheets1").Cells(lngRow,6).Value= dblVal

Not sure what's wrong.

Should that be: Sheets("Sheet1").Cells(lngRow,6).Value= dblVal

and .... where is dblVal defined in your code?
 
Upvote 0
I'm obviously an amature at this. I was using this format from similar codes I found on Excel forums. I somewhat understand this. But, I'm trying to get the second sent of If Then to read each cell in that column as a date format (a result of a fomula referencing sheet 3) and not the formula. I want to compare that date to ExpDate and the do the exact same thing as the first If Then, but instead of copying the date, copy the cell from column 5 and paste it to sheet 2.

I thought this code was telling the program to read the data in column 6 as the resulting date and not the formula in the cells. Am I wrong? How would I go about doing this?
 
Upvote 0
I'm obviously an amature at this. I was using this format from similar codes I found on Excel forums. I somewhat understand this. But, I'm trying to get the second sent of If Then to read each cell in that column as a date format (a result of a fomula referencing sheet 3) and not the formula. I want to compare that date to ExpDate and the do the exact same thing as the first If Then, but instead of copying the date, copy the cell from column 5 and paste it to sheet 2.

I thought this code was telling the program to read the data in column 6 as the resulting date and not the formula in the cells. Am I wrong? How would I go about doing this?
You didn't answer the questions I posted.
 
Upvote 0
Sorry, I thought I typed them in, but didn't. I copied the code from another site, so I don't know what the definition was. So in short, dbl.Val is not defined anywhere (and i didn't know it had to be or what it should be). You were correct about the sheet name (I fixed that).
 
Upvote 0
Sorry, I thought I typed them in, but didn't. I copied the code from another site, so I don't know what the definition was. So in short, dbl.Val is not defined anywhere (and i didn't know it had to be or what it should be). You were correct about the sheet name (I fixed that).
The way your code stands now dblVal is empty so this line:

Sheets("Sheet1").Cells(lngRow,6).Value= dblVal

will make that cell on Sheet1 empty (i.e. blank). Is that what you want to happen?
 
Upvote 0
Actually, no. When I test the code, it runs through all 313 rows, reguardless of whether or not the date in that is less than ExpDate and copies all Range(Cells(i,2), (i,3)) onto sheet2 in every other row and does not insert any value into any other column on sheet2.

If I remove this second IF THEN section, the first one runs perfectly. So I have no idea what to do from here. Do I need to replace this with another code? Autofilter? Loop? I'm completely lost.
 
Upvote 0
Actually, no. When I test the code, it runs through all 313 rows, reguardless of whether or not the date in that is less than ExpDate and copies all Range(Cells(i,2), (i,3)) onto sheet2 in every other row and does not insert any value into any other column on sheet2.

If I remove this second IF THEN section, the first one runs perfectly. So I have no idea what to do from here. Do I need to replace this with another code? Autofilter? Loop? I'm completely lost.
Instead of trying to make some code you gleaned from the web fit your need, consider posting your layout with a description of what you want to achieve. Start a new thread - you are likely to get what you want here.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top