Do While True "Select" problem.
 

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

Do While True "Select" problem.
 

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

Do While True "Select" problem.
 

Try this:
Dim cnt As Long
ActiveWorkbook.Unprotect Password:="Time"
Worksheets("CodeGrid").Visible = True
Worksheets("CodeGrid").Activate
Range("A1").Select
cnt = 0
Do While True And ActiveCell.Column < 75
If Worksheets("Coding").Range("A1").Value = _
Worksheets("CodeGrid").Range("A1").Offset(0, cnt) _
.Value Then Exit Do
cnt = cnt + 1
Loop
Columns(cnt + 1).Copy Destination:=Sheets("Password").Range("L1")

Charles
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

Do While True "Select" problem.
 

Shawn
Try this:
Dim cell As Range
With Worksheets("CodeGrid")
For Each cell In .Range(.Cells(1, 1), .Cells(1, 75))
If cell.Value = Worksheets("Coding").Range("A1").Value Then
cell.EntireColumn.Copy
Destination:=Sheets("Password").Range("L1")
Exit For
End If
Next
End With

NickHK

"Shawn" ...
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

Do While True "Select" problem.
 

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

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