search columns, set variable value
 

I have two questions. First, I want to search down a column. If the first
row is blank, skip to the next, if blank skip to next, so on... I want excel
to stop at the first row that has a value in it, then set that value into a
declared variable. If there's no value within the entire column, then leave
the variable as an empty type value.

That's it... piece of cake for you guys... I can't get the syntax right to
save my life!!

Thanks!

search columns, set variable value
 

This code looks in for the first value in column A below A1 on the active
sheet...

Sub test()
Dim rngFound As Range

Set rngFound = ActiveSheet.Range("A1").End(xlDown)

If rngFound.Row < 65536 Then MsgBox rngFound.Value
End Sub

replace msgbox with "YourVariable = "

HTH

"Robert" wrote:

I have two questions. First, I want to search down a column. If the first
row is blank, skip to the next, if blank skip to next, so on... I want excel
to stop at the first row that has a value in it, then set that value into a
declared variable. If there's no value within the entire column, then leave
the variable as an empty type value.

That's it... piece of cake for you guys... I can't get the syntax right to
save my life!!

Thanks!

search columns, set variable value
 

That would miss A1 if it were populated.

Sub test()
Dim rngFound As Range
if isempty(ActiveSheet.Range("A1")) then
Set rngFound = ActiveSheet.Range("A1").End(xlDown)
else
set rngFound = ActiveSheet.Range("A1")
end if
if Not isempty(rngFound) then
myval = rngFound.Value
End if
End Sub

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
This code looks in for the first value in column A below A1 on the active
sheet...

Sub test()
Dim rngFound As Range

Set rngFound = ActiveSheet.Range("A1").End(xlDown)

If rngFound.Row < 65536 Then MsgBox rngFound.Value
End Sub

replace msgbox with "YourVariable = "

HTH

"Robert" wrote:

I have two questions. First, I want to search down a column. If the
first
row is blank, skip to the next, if blank skip to next, so on... I want
excel
to stop at the first row that has a value in it, then set that value
into a
declared variable. If there's no value within the entire column, then
leave
the variable as an empty type value.

That's it... piece of cake for you guys... I can't get the syntax right
to
save my life!!

Thanks!