Need Loop to Search for cell containing two values

Maybe...

Please Login or Register  to view this content.

Maybe...

Please Login or Register  to view this content.

Hi theOctonaut and welcome to ExcelForum,

You probably only need one find command to solve your problem.

There are several techniques that can be used to do what you want. They include:
a. Using .find with a wildcard one time only search WHOLE cell (included below).
b. Using .find with a wildcard one time only search PART of cell (included below).
c. Using .find with a wildcard and find all matches search WHOLE cell (included below).
d. Search for one item only e.g. TTT (part of cell) using .find command and then use:
(1) Like command to find additional matches in the cell (not included).
(2) Regular Expressions command to find additional matches in the cell (not included).

See the attached file which includes a., b. and c. above.

Please Login or Register  to view this content.

Lewis

Another way...

Please Login or Register  to view this content.

One more question:

If I put a Do Until loop to search through the discovered row, how do I save each cell to an array value? If I don't use an array, I can get it to save the last cells value. Everytime I put it an array, it leaves each value as empty.

Please Login or Register  to view this content.

Hi Octo,

Thank you very much for the rep points.

I think you are missing the .FindNext call to find further values. See the following example code (included in the atttached file) which may help you. It is combines two solutions in one:
a. Use of a Dynamic Array
b. Use of a Dynamic Array of a Structure

Please Login or Register  to view this content.

Key Points:
a. Option Explicit - To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx
b. Structure Definition - It should go after 'Option Explicit' and before the first routine (Sub or Function).

Please Login or Register  to view this content.

c. Creation of Dynamic Array and Dynamic Structure Array - Notice that there are no dimensions inside.

Please Login or Register  to view this content.

d. Initialization of the items created in c. above. The method I use is probably not the best way to do things, but it has always worked for me without problems. Redim expands (or contracts) the array size and removes any existing data. Please note that the Redim command can be tricky when using multi-dimensional arrays

Please Login or Register  to view this content.

e. Putting data in the arrays. Redim Preserve expands (or contracts) the array size and keeps any existing data.
f. Offset command. The syntax is Offset(number of rows, number of columns). r.Offset(0,1) means take the address of r and move it 1 column to the right.

Please Login or Register  to view this content.

----------------
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

I hope this helps.

Lewis