Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Typing "true" excel 2007 change it to "TRUE" | Excel Discussion (Misc queries) | |||
Setting values for "TRUE","FALSE" and "#REF!" | Excel Programming | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
IF(VLOOKUP("MYDATA", MYNAME, 4) = 0, "TRUE", "FALSE") | Excel Programming |