Hi,

I am attempting to limit a find function to the single colum J,
starting in row 2 and searching until there is a blank cell in the
column or until the search has reached its logical conclusion (i.e. it
has searched every filled cell in that column). I would prefer the
latter but would be satisfied with either.

Thanks


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=558592

Hi Merritts,

Try:

'=============
Public Sub Testerw001()
Dim rng As RangeConst col As Long ="J"

On Error Resume Next
Set rng = Columns(col).SpecialCells(xlBlanks)(1)
On Error GoTo 0

If Not rng Is Nothing Then
'do something, e.g.:
MsgBox rng.Address
Else
MsgBox "No blank cells found in stipulated range"
End If
End Sub
'<<=============


--
---
Regards,
Norman



"merritts" wrote in
message ...

Hi,

I am attempting to limit a find function to the single colum J,
starting in row 2 and searching until there is a blank cell in the
column or until the search has reached its logical conclusion (i.e. it
has searched every filled cell in that column). I would prefer the
latter but would be satisfied with either.

Thanks


--
merritts
------------------------------------------------------------------------
merritts's Profile:
http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=558592

Were you hoping to do something with all of the hits, or just the first hit,
or just the last hit, or... Is your search case sensitive. What do you plan
on doing with the hit(s)...
--
HTH...

Jim Thomlinson


"merritts" wrote:


Hi,

I am attempting to limit a find function to the single colum J,
starting in row 2 and searching until there is a blank cell in the
column or until the search has reached its logical conclusion (i.e. it
has searched every filled cell in that column). I would prefer the
latter but would be satisfied with either.

Thanks


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=558592

Hi Merritts,

The code was intended as:

'=============
Public Sub Tester()
Dim rng As Range
Const col As String = "J"

On Error Resume Next
Set rng = Columns(col).SpecialCells(xlBlanks)(1)
On Error GoTo 0

If Not rng Is Nothing Then
'do something, e.g.:
MsgBox rng.Address
Else
MsgBox "No blank cells found in stipulated range"
End If
End Sub
'<<=============


---
Regards,
Norman

Hi Merritts,

Re-reading your question, in response to Jim's questions, it is apparent
that I have misunderstood your requirements.

My code returns the first blank cell in column J, which is not what you
asked, so apologies and disregard my post.


---
Regards,
Norman

First, Thanks for all your help. I really appreciate it.

I will be taking values from another workbook in column G the
searching column J for that value. If the value (an alpha-numeri
combination) match the F columns (only numeric) will be summed. Ther
should only be one match in that column, but not the spreadsheet as
whole. That is why i would like to limit it to one column

--
merritt
-----------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...fo&userid=3580
View this thread: http://www.excelforum.com/showthread.php?threadid=55859

Thanks for all your help, i appreciate it.

The search will not be case sensitive and there should only be one hi
in the column. If there is more than one hit I would like there to b
an error message, but that may be beyond my immediate concerns for now


The larger idea behind the question is a search and update between tw
spreadsheets. I am hoping to write a macro that will select the firs
alpha-numeric name from spreadsheetB in column G. The macro will the
search spreadsheetA, column J for the alpha-numeric name. Once it ha
been located, values from their respective rows will b
summed/differenced. For example, AAAA#### from G3 (spreadsheetB) is th
first item. The macro would then search column J for AAAA####. Onc
found (for example in J50), the value located in F3 (spreadsheetB) wil
be added/subtracted from F50 (spreadsheetA).

The process would begin again at G4 and continue till spreadsheetB wa
complete.

I hope that helps

--
merritt
-----------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...fo&userid=3580
View this thread: http://www.excelforum.com/showthread.php?threadid=55859

I am still a little vague on exactly what you wnat but give this a try...

Sub test()
Dim rng As Range

Set rng = FindStuff("A3B")
If Not rng Is Nothing Then MsgBox rng.Address
Set rng = FindStuff("A30B")
If Not rng Is Nothing Then MsgBox rng.Address

End Sub

Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("Sheet1")
With wksToSearch
Set rngToSearch = .Range(.Range("J2"), .Cells(Rows.Count, "J").End(xlUp))
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function
--
HTH...

Jim Thomlinson


"merritts" wrote:


First, Thanks for all your help. I really appreciate it.

I will be taking values from another workbook in column G then
searching column J for that value. If the value (an alpha-numeric
combination) match the F columns (only numeric) will be summed. There
should only be one match in that column, but not the spreadsheet as a
whole. That is why i would like to limit it to one column.


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=558592