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

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

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

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

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