IF formula?

[Eucalypta]

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta

 

[Joel]

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) <> ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) > 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

 

[Eucalypta]

Hi Joel,
Thanks for your help.
If I type the macro below, I get errors: my version of Excel (2003) does not
understand .Range etc. The help function tells me "An identifier beginning
with a period is valid only within a With block." Should I use something else?
KG, Eucalypta

 

[Joel]

The with block is Activesheet. the code is using the activesheet as the
source and Sheet2 as the destination. The error is probably because you
don't have a sheet2. Change "Sheet2" to match the names of your worksheets.
You may want to change
with activesheet to match you sheet name
from
with activesheet
to
with sheets("MysheetName")

 

[Eucalypta]

Hi Joel,
I have retyped the macro and it runs oke now. That is, without giving
warnings. It does not however copy anything although criteria are met. I have
changed the sheetnames too.
Awaiting your kind input.
KG, Eucalypta

 

[Joel]

I think the problem is with this line of code
from
If IsDate(.Range("A" & OldRowCount)) Then
to
If IsDate(.Range("B" & OldRowCount)) Then

If above doesn't work then the usual problems with dates is that they are
not in date format instead the are actually text. One way to determine the
format is to click on a cell with the date then go to the menu Format - Cell
- Numbers and then check if a date format or text format is highlighted.
usually if it is dates, then a sinhgle quote is in front of the date and
should be removed .

You may need to step through the code to determine the problem. Add break
points by pressing line of code and then pressing F9. You can also single
step through the code by press F8.

 

[Eucalypta]

Hi Joel,
Thank you for your reply. I have made the change you mention below. I have
checked the data format and changed it to a different format to be on the
save side. Still I do not get the desired result.

In your last paragraph you mention adding break points by pressing line of
code. I do not understand what you mean by this. I am not that familiar with
macros and VBA. Please be so kind as to explain.

Is it helpful to know that instead of , I have to use ; in formulas?
Awaiting your kind response.
KG, Eucalypta

 

[Joel]

Not sure what you mean by theis "Is it helpful to know that instead of , I
have to use ; in formulas?" If you mean worksheet functions, they are not
capabble of copying from one location to another locattion. Worksheet
functions can only referrence other cells.

To debug code there are a lot of diffferent ways of finding problems. the
following are different methods of debuging macro. You first havve to open
the VBA window

1) Run Code - Press F5
2) Start code from beginning. VBA menu - Run - Reset
3)Single step - Click on first line of code. Then press F8 to step through
code. Keep on pressing F8 to understand whaty the code is actually doing
4) Break Point - click on any line of code. Then press F5 to run code until
you get to break point
5) Watch. Add variable to watch window by hightling variable (like
rowCount) and right click with mouse. Select Add to Watch.


A) The important thing to fgind out is if OldRowCount is counting through
all the rows of your data. Add OldRowCount to watch.
B) Next find out if it is finding a date in column A. Does the code pass
the "If IsDate()" and gettting to the line of code

If (Date - .Range("B" & OldRowCount)) > 60 And _
.Range("L" & OldRowCount) = "" Then


Question:

IS this 11-01-08 November 1st or January 11th. Becuase this is an
international website it could be either. I'm not surre if you are looking
for dates older than 60 days or within 60 days of a future date. The code
may be backwards

from : Older dates A(Date is the present date)
If (Date - .Range("B" & OldRowCount)) > 60
to : Newer dates
If (.Range("B" & OldRowCount) - Date) > 60

 

[Eucalypta]

Hi Joel, unfortunately it does not work. Thanks for all your help and patience.
Kind regards, Eucalypta

 

[Joel]

email me the file att

joel dot warburg at itt dot com