Do While True "Select" problem.
Keep the checks in Tom's code:
if not rng is nothing then
rng.EntireColumn.Copy Destination:=Sheets("Password").Range("L1")
Else
msgbox "Not found"
End if
But I would think his second suggestion would be much quicker with this change:
if not iserror(res) then
rng.EntireColumn.Copy Destination:=Sheets("Password").Range("L1")
else
msgbox "Not found"
end if
===
You're just searching that range (A1:A75) and if there's any match copy that
entire column to L1?
Shawn wrote:
I tried this variation to no avail:
Dim cell As Range, rng As Range
For Each cell In Worksheets("CodeGrid").Range("A1:A75")
If cell.Value = Worksheets("Coding" _
).Range("A1").Value Then
Set rng = cell
Exit For
End If
Next
cell.EntireColumn.Copy Destination:=Sheets("Password").Range("L1")
--
Thanks
Shawn
"Tom Ogilvy" wrote:
Dim cell as Range, rng as Range
for each cell in worksheets("CodeGrid").Range("A1:A75")
if cell.Value = Worksheets("Coding" _
).Range("A1").Value Then
set rng = cell
exit for
end if
next
if not rng is nothing then
msgbox "found match as " & rng.Address(0,0,xlA1,True)
Else
msgbox "Not found"
End if
or
Dim tgt, rng1 as Range, res, rng as Range
set rng1 = worksheets("CodeGrid").Range("A1:A75")
tgt = Worksheets("Coding" _
).Range("A1").Value
res = Application.Match(tgt,rng1,0)
if not iserror(res) then
set rng = rng1(1,res)
msgbox "found match as " & rng.Address(0,0,xlA1,True)
else
msgbox "Not found"
end if
--
Regards,
Tom Ogilvy
"Shawn" wrote:
Below is my code. I want to be able to do this without having to Select
anything on the CodeGrid Sheet. When I select I am having to unlock the
workbook. Is there a way I can do this without having to "select"?
ActiveWorkbook.Unprotect Password:="Time"
Worksheets("CodeGrid").Visible = True
Worksheets("CodeGrid").Activate
Range("A1").Select
Do While True And ActiveCell.Column < 75
If Worksheets("Coding").Range("A1").Value = _
ActiveCell.Value Then
Exit Do
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.EntireColumn.Select
Selection.Copy Destination:=Sheets("Password").Range("L1")
--
Thanks
Shawn
--
Dave Peterson
|