Not see all of my if statement

[wafs]

I have an if statement that is looking for two variables, but it only see the first variable. What am I missing that is making it only look at one variable instead of both. Here is the code that I'm using for the if statement.

If Sheets(sh).Range(TestRange) > 60 And Sheets(sh).Range(TestRange2) = 0 Then

 

[Loomah]

What are the values of the 2 cells?
What happens if TeestRange = 100 and TEstRange2 = 0?

What makes you think that only one condition is being tested?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Are TestRange & TestRange2 both SINGLE CELL references?

Did you BREAK and use your Watch Window to inspect the values in each of these references? What values are there?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

TestRange and TestRange2 are being looked at upto 900 rows, TestRange is column AF and TestRange 2 is column AE. Both have numbers in them and I need AF to be greater then 60 and AE to be equal to zero. When I do a watch window, it is changing the count each time it goes through the code, but it brings over all data in column AF that is greater than 60 and brings over column AE no matter what the number is.

 

[SkipVought]

You CANNOT compare COLUMNS.

You CAN compare distinct values.

"When I do a watch window, it is changing the count each time it goes through the code"

Of course it does! That's the beauty of using a Watch at each BREAK. You see what's happening!

So tell us WHAT'S HAPPENING at the BREAK. What are these 2 values at the point when the logic fails?



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

I will have to run it again and write the values down.

 

[wafs]

It shows TestRange as "AF8" and TestRange2 as "AE8" never as 30 or 0. I never get a value for those two. Here is my full code, maybe there is something else.

Sub Over_60_Days()
'==============================================================
' POPULATE THE OVER 60 DAYS OLD TAB
'
' Description: This macro moves and deletes all part numbers that
' has been shipped more than 60 days and has future of 0
' future parts.
'
'Macro created on 3/1/2006
'Written by Wendy Smith
'=================================================================

Dim cellstart As Integer 'starting point on source sheet (row)
Dim reportlocation As String
Dim destsh As String 'destination sheet
Dim Output(1 To 66) As String
Dim sh As String 'source sheet
Dim i As String
Dim lastrow As String 'last row in sheet to look at
Dim z As Integer
Dim x As Integer
Dim TestRange As String 'Test string for over 30 days old
Dim TestRange2 As String 'Test string for future 6 wks
Dim TestRange3 As String
Dim TestRange4 As String
Dim parts As String
Dim m As String
Dim n As String
Dim v As Integer
Dim y As Integer

cellstart = 3 'starting point on source sheet
parts = 0

sh = "Daily" 'Sheet where the parts information is stored DO NOT CHANGE NAME OF SHEET!!
destsh = "Over_60_Days" 'Name of sheet where over 30 days since last shipped DO NOT CHANGE NAME OF SHEET!!

'Turn off Screen Updating
Application.ScreenUpdating = False

'Build Over 60 Sheet

Range("A2:AF903").sort Key1:=Range("AF2"), Order1:=xlDescending, header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal 'sort column AF

'Get Data From Rows
i = cellstart
TestRange = "AF" + i 'over 60 days column
TestRange2 = "AE" + i 'future 6 wks production column
TestRange3 = "A" + i 'part number column
For x = 1 To 1000
---> If UCase(Trim(Sheets(sh).Range(TestRange) > 60) And UCase(Trim(Sheets(sh).Range(TestRange2) = 0))) Then

'prep machine
Output(1) = "B" + i
Output(2) = Sheets(sh).Range(Output(1)).Value
'prep weight
Output(3) = "C" + i
Output(4) = Sheets(sh).Range(Output(3)).Value
'prep PointType
Output(5) = "D" + i
Output(6) = Sheets(sh).Range(Output(5)).Value

'Output info to Over 60 sheet
TestRange3 = "A" + i
parts = parts + 1
m = parts + 2 'tells where to start importing information on dest sheet (row)
reportlocation = "A" + m 'starting column for destination sheet
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
reportlocation = "B" + m ' machine
Sheets(destsh).Range(reportlocation) = Output(2)
reportlocation = "C" + m 'weight
Sheets(destsh).Range(reportlocation) = Output(4)
reportlocation = "D" + m 'point type
Sheets(destsh).Range(reportlocation) = Output(6)
'Sheets(sh).Range(TestRange3).Delete
TestRange = "AF" + i
TestRange2 = "AE" + i
i = i + 1
Else
TestRange = "AF" + i
TestRange2 = "AE" + i
i = i + 1
End If
Next
'Turn on screen updating
Application.ScreenUpdating = True

End Sub

 

[SkipVought]

What is the value of
[tt]
Sheets(sh).Range(TestRange).row
Sheets(sh).Range(TestRange2).row
[/tt]
at that point?
Is it the ROW that you expect?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

The values of Sheets(sh).Range(TestRange).Row is AF3 and changes as I step through the program. Same thing goes for Sheet(sh).Range(TestRange2).Row (AE3). When I step through the code, everything seems to be working the way it should except that it brings over the AF column when AE is greater than 0.

 

[SkipVought]

What is the AE VALUE greater than 0 that seems to be failing?

This is like PULLING TEETH to get the info!!!!!!!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

Sorry, I did not mean to turn you into a dentist. All AE values that are greater than 0 are failing. The values are 0 or greater than 5000. The value in AE changes depending on how much stuff is in stock and what was shipped out.

It brings over AF when it is greater then 60, but does not seem to be looking at AE. I know when I do a watch, the values change for AE everytime it goes through the code. so it is looking at the column, but does not seem to see if it is greater than 0.

 

[SkipVought]

    If Sheets(sh).Range(TestRange) > 60 And Sheets(sh).Range(TestRange2) = 0 Then

I know I suggested the Trim & Ucase earlier, but these are NUMBERS you're testing.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

Found the problem and fixed it. Now I have to do is add a delete statement for those rows. Thanks for your help Skip and being patient with my questions.

Wendy

 

[Loomah]

Ah solved eh?

I need to know now out of interest how it was solved as I've just come to the conclusion that the If statement wasn't the problem!!

So assuming the data you wanted to copy was in the same row where the conditions were met then moving i=i+1 up 2 lines (in both occurances), ie before re-defining TestRange & TestRange2, should have been the solution?

Just a word of warning though - If you're going to start deleting rows in the middle of your code you are going to have to re think your loop to start from the bottom of your data.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[wafs]

That is right, I moved the i = i + 1 up two lines and it moves only what I want to move. It took me looking at the watch five times before it clicked on me that the first two times around that the numbers weren't changing.

I am going to put the delete code at the end as another function. I wanted to go through amd move over first then go back through and delete.

Do you think that there is a better way to delete out those rows. What I need to do after those rows are deleted out is create about 29 different reports with the left over data.

 

[Gavona]

Could you use advance filter to display the rows meeting the criteria and then delete visible rows?

Or Advanced filter to extract the rows you want and put them on a different sheet to build your report from.

VBA is excellent for making advanced filter easy. Use namedranges especially if you want the output on a different sheet to the database.......and something like Range("database").currentRegion.name = "database" to expand/contract the database as your data changes.



Gavin