Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with "searching" and "end"
On sheet "sort" (not the active sheet) I have column C with a list of locations (all unique data). For each location in column C, in that same row, I have a list of corresponding locations, starting at column G and moving to the right. There is no set number of corresponding locations for each location, so the amount of columns that each row expands to is variable. What I am trying to do is search column C for a specific location (I've grabbed the row number), and then copy the list of locations that correspond to that found location. "A3" on "Current Territory Worksheet" is the location that I am searching for.. Here's my code that doesn't work (the red section is where I am getting errors): RowOfHostStore = Sheets("Sort").Columns("C:C").Find(What:=Worksheet s("Current Territory Worksheet").Range("A3"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Row With Worksheets("Sort") Range(Cells(RowOfHostStore, 7), Range(Cells(RowOfHostStore, 7).End(x1ToRight))).Copy End With Worksheets("Current Territory Worksheet").Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Any help is greatly appreciated -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=388477 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with "searching" and "end"
Hi,
Try this: With Worksheets("Sort") Range("G" & RowOfHostStore, Range("G" & RowOfHostStore).End(xlToRight)).Copy End With In your original there was a typo - x1ToRight instead of xlToRight (1 (one) instead of l) HTH "grime" wrote: On sheet "sort" (not the active sheet) I have column C with a list of locations (all unique data). For each location in column C, in that same row, I have a list of corresponding locations, starting at column G and moving to the right. There is no set number of corresponding locations for each location, so the amount of columns that each row expands to is variable. What I am trying to do is search column C for a specific location (I've grabbed the row number), and then copy the list of locations that correspond to that found location. "A3" on "Current Territory Worksheet" is the location that I am searching for.. Here's my code that doesn't work (the red section is where I am getting errors): RowOfHostStore = Sheets("Sort").Columns("C:C").Find(What:=Worksheet s("Current Territory Worksheet").Range("A3"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Row With Worksheets("Sort") Range(Cells(RowOfHostStore, 7), Range(Cells(RowOfHostStore, 7).End(x1ToRight))).Copy End With Worksheets("Current Territory Worksheet").Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Any help is greatly appreciated -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=388477 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with "searching" and "end"
Why won't this paste? It copies and pastes empty cells... Please help. Here's the code. With Worksheets("Sort") Range("G" & RowOfHostStore, Range("G" & RowOfHostStore).End(xlToRight)).Copy End With Worksheets("Current Territory Worksheet").Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=388477 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with "searching" and "end"
You use "With worksheets("sort")"
but then you leave your ranges unqualified. maybe... With Worksheets("Sort") .Range("G" & RowOfHostStore, _ .Range("G" & RowOfHostStore).End(xlToRight)).Copy End With Worksheets("Current Territory Worksheet").Range("A4").PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Notice the dots in front of each .Range() bit. That means they belong to the previous "with" object--in this case, Worksheets("sort"). grime wrote: Why won't this paste? It copies and pastes empty cells... Please help. Here's the code. With Worksheets("Sort") Range("G" & RowOfHostStore, Range("G" & RowOfHostStore).End(xlToRight)).Copy End With Worksheets("Current Territory Worksheet").Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=388477 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with "searching" and "end"
Doh! <slaps forhead Thanks Dave -- grim ----------------------------------------------------------------------- grime's Profile: http://www.excelforum.com/member.php...fo&userid=1922 View this thread: http://www.excelforum.com/showthread.php?threadid=38847 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |