Find next Function using 2 criteria help

[wlyon]

Hello All,


First of all sorry if this is answered somewhere already, but I didn't know exactly how to word it.

I am an Engineering Intern that has somehow been tasked with writing Excel Macro for the last month. I've taught myself quite a lot so far but cant figure this one out.

The first part of the macro needs to search Column Z for the next cell that is not blank nor contain a value of 12 in it and then select it. (I.E. a cell contain a value of 8 or 4.2)

The next part of the macro needs run a goal seek function changing column X until Column Z is 12

Then End itself when all of Column Z is either blank or 12


I have written This code so far and it works perfectly except it checks every single row. Which is super slow since there are currently 75500 rows at the moment and will only increase over the years.

Sub GoalSeek()


Dim i As Long
For i = 5 To 75500
If (Range("Z" & i) = 12 Or Range("Z" & i) = "") Then
Else
With Range("Z" & i)
.GoalSeek Goal:=12, ChangingCell:=Range("X" & i)
End With

End If
Next i
End Sub

My theory for writing it like this was to hopefully speed up the checking process before goal seek was used. but it is still very slow. ( I know the goal seek part will have to be slow no matter what)

This code runs when the file is saved and that will only happen if someone updates a value which will change minimum 27 rows and maximum 2145 rows, So the bulk of the 75500 rows will not need to be looked at before saving. (Which I can't seem to avoid)

Any help or lessons would be greatly appreciated, I'm a quick study most of the time, but I can't find any info on this one to study unfortunately.

 

[WarPigl3t]

I don't see another way besides checking each row like you already did. Here's a modification to your macro because you stated that the number of rows will increase over time.

Sub GoalSeek()
     lastRow = Range("Z" & Rows.Count).End(xlUp).Row
     For i = 5 To lastRow
          If Range("Z" & i) <> 12 And Range("Z" & i) <> "" Then
               With Range("Z" & i)
                    .GoalSeek Goal:=12, ChangingCell:=Range("X" & i)
               End With
          End If
     Next i
End Sub

 

[JoeMo]

As you surmised, the goal seek is time consuming.Still, you will get some relief by simply suppressing calculations and screen updating. I used the modified code below for a quick test. In column Z (Z5) I had the simple formula:

=X5+2
with random values between -10 and +10 in column X I looped through 1000 rows in column Z. With suppression of calculation and screen updating the execution time was 16 seconds, while w/o suppression it was 74 seconds for an identical data set in column X.

Sub GoalSeek()


Dim i As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For i = 5 To 1005       '75500
If (Range("Z" & i) = 12 Or Range("Z" & i) = "") Then
Else
With Range("Z" & i)
.GoalSeek Goal:=12, ChangingCell:=Range("X" & i)
End With

End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

This code runs when the file is saved and that will only happen if someone updates a value which will change minimum 27 rows and maximum 2145 rows, So the bulk of the 75500 rows will not need to be looked at before saving. (Which I can't seem to avoid)

Perhaps there is some way to use a change event to identify the cells that are changed, thereby reducing the cells that you need to loop through.

 

[wlyon]

Thanks WarPigl3t JoeMo,

From what I understand about the part you modified WarPigl3t, that will use the last cell in the row as a reference even though there are random blanks through out the data.

Your Code compared to mine JoeMo was 60% faster on average, thank you very much. Still need to figure something out but this will help a whole lot for sure.


I was thinking and I have a way to always have the blank cells in column Z show 12 which wouldn't be to big of an issue for people using it, removing one of the criteria for the macro and then it would only skip cells with 12 in it. Not sure if it will help at all, but worth a shot.

Thanks again for the quick replies and the macro lessons.

 

[JoeMo]

Thanks WarPigl3t JoeMo,

From what I understand about the part you modified WarPigl3t, that will use the last cell in the row as a reference even though there are random blanks through out the data.

Your Code compared to mine JoeMo was 60% faster on average, thank you very much. Still need to figure something out but this will help a whole lot for sure.


I was thinking and I have a way to always have the blank cells in column Z show 12 which wouldn't be to big of an issue for people using it, removing one of the criteria for the macro and then it would only skip cells with 12 in it. Not sure if it will help at all, but worth a shot.

Thanks again for the quick replies and the macro lessons.

You are welcome. Are the blank cells truly blank or do they have a formula that returns ""?

 

[wlyon]

You are welcome. Are the blank cells truly blank or do they have a formula that returns ""?

Yes it's a formula so I can easily change "" to 12 or anything else if need be

 

[JoeMo]

Yes it's a formula so I can easily change "" to 12 or anything else if need be

Yes, but you still need to loop through the cell and test if it contains 12 so I doubt it will improve execution time. If you have a reasonably large fraction of cells that contain 12 or "" you might think about filtering them out and then using the specialcells method to loop through visible cells only. Typically, what fraction of the cells contain 12 or ""?