referencing cellswith values in them

Not sure if this is what you mean, but in VBA you could use something like this, which would select all cells with constants (text/number/errors/logical values entered manually, not using formulas) in the range A1:Z100:
Or this, to select all cells with formulas that result in text/numbers/errors/logicals:
These perform the same function as using F5-->Special-->Constants or F5-->Special-->Formulas.

no i dontwant to use a range, because the values could be in any cell throughout the spreadsheet, so how do i reference code to any cell in a sheet that has a value in it?

the only thing i have used so far us

For Each c In Sheets("sheet1").UsedRange.Cells<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If Not c = Empty Then<o></o>

Umm, then switch out Range("A1:Z100") with UsedRange

Using SpecialCells will be MUCH quicker than looping through every cell in your sheet.

Range("A1:Z100").SpecialCells(xlCellTypeConstants, 23).Select
Range("A1:Z100").SpecialCells(xlCellTypeFormulas, 23).Select

Click to expand...

You don't need "23" when you are referring to all types.

Good to know, Jindon. I just copied what Excel's macro recorder spit out. I tested a few others and the number changed, so I left it. Thanks.