Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet

Can I copy FndPrd to a list on the same sheet and/or to another sheet.
What I have gives me TRUE in K2 and I have marching ants around the
..Union arguments 1 & 2 and 3 & 4 on the sheet.

Thanks,
Howard

Option Explicit
Option Compare Text

Sub TheUnionOf()

Dim FndPrd As String
Dim c As Range

FndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)

For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If c = FndPrd Then
Set FndPrd = Application.Union(c.Offset(0, 1), c.Offset(0, 2), c.Offset(0, 4), c.Offset(0, 5))
Range("K100").End(xlUp).Offset(1, 0) = FndPrd
End If

Next
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Fri, 27 Sep 2013 23:55:51 -0700 (PDT) schrieb Howard:

Can I copy FndPrd to a list on the same sheet and/or to another sheet.
What I have gives me TRUE in K2 and I have marching ants around the
.Union arguments 1 & 2 and 3 & 4 on the sheet.


try:

Sub TheUnionOf2()
Dim rngFndPrd As Range
Dim sFndPrd As String
Dim c As Range

sFndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)

For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If c = sFndPrd Then
Set rngFndPrd = Union(c.Offset(0, 1), c.Offset(0, 2), _
c.Offset(0, 4), c.Offset(0, 5))
rngFndPrd.Copy
Range("K100").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End If
Next
End Sub

or:

Sub TheUnionOf()
Dim strFndPrd As String
Dim sFndPrd As String
Dim varOut As Variant
Dim c As Range

sFndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)

For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If c = sFndPrd Then
strFndPrd = c.Offset(0, 1) & "," & c.Offset(0, 2) & "," & _
c.Offset(0, 4) & "," & c.Offset(0, 5)
varOut = Split(strFndPrd, ",")
Range("K100").End(xlUp).Offset(1, 0) _
.Resize(rowsize:=UBound(varOut) + 1) = _
WorksheetFunction.Transpose(varOut)
End If
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 12:25:42 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Fri, 27 Sep 2013 23:55:51 -0700 (PDT) schrieb Howard:



Can I copy FndPrd to a list on the same sheet and/or to another sheet.


What I have gives me TRUE in K2 and I have marching ants around the


.Union arguments 1 & 2 and 3 & 4 on the sheet.




try:



Sub TheUnionOf2()

Dim rngFndPrd As Range

Dim sFndPrd As String

Dim c As Range



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)



For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

If c = sFndPrd Then

Set rngFndPrd = Union(c.Offset(0, 1), c.Offset(0, 2), _

c.Offset(0, 4), c.Offset(0, 5))

rngFndPrd.Copy

Range("K100").End(xlUp).Offset(1, 0).PasteSpecial _

Paste:=xlPasteAll, Transpose:=True

End If

Next

End Sub



or:



Sub TheUnionOf()

Dim strFndPrd As String

Dim sFndPrd As String

Dim varOut As Variant

Dim c As Range



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)



For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

If c = sFndPrd Then

strFndPrd = c.Offset(0, 1) & "," & c.Offset(0, 2) & "," & _

c.Offset(0, 4) & "," & c.Offset(0, 5)

varOut = Split(strFndPrd, ",")

Range("K100").End(xlUp).Offset(1, 0) _

.Resize(rowsize:=UBound(varOut) + 1) = _

WorksheetFunction.Transpose(varOut)

End If

Next

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2


Thanks, Claus. Both run smooth and good as gold.

Regards,
Howard
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sat, 28 Sep 2013 00:50:09 -0700 (PDT) schrieb Howard:

Both run smooth and good as gold.


it will run a bit faster if you use the find method instead of looping
through the range:

Sub TheUnionOf2()
Dim rngFndPrd As Range
Dim sFndPrd As String
Dim c As Range
Dim LRow As Long

sFndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set c = Range("A1:A" & LRow).Find(sFndPrd, LookIn:=xlValues)
If Not c Is Nothing Then
Set rngFndPrd = Union(c.Offset(0, 1), c.Offset(0, 2), _
c.Offset(0, 4), c.Offset(0, 5))
rngFndPrd.Copy
Range("K100").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End If
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet


it will run a bit faster if you use the find method instead of looping

through the range:



Sub TheUnionOf2()

Dim rngFndPrd As Range

Dim sFndPrd As String

Dim c As Range

Dim LRow As Long



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)



LRow = Cells(Rows.Count, 1).End(xlUp).Row

Set c = Range("A1:A" & LRow).Find(sFndPrd, LookIn:=xlValues)

If Not c Is Nothing Then

Set rngFndPrd = Union(c.Offset(0, 1), c.Offset(0, 2), _

c.Offset(0, 4), c.Offset(0, 5))

rngFndPrd.Copy

Range("K100").End(xlUp).Offset(1, 0).PasteSpecial _

Paste:=xlPasteAll, Transpose:=True

End If

End Sub


Regards

Claus B.



I'll give it a go. I'm using the codes on a small test data set, actual use could be around 5500+ rows.

I'm sure that will make a BIG difference.

Thanks, Claus.

Appreciate it.

Regards,
Howard


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sat, 28 Sep 2013 03:33:06 -0700 (PDT) schrieb Howard:

I'll give it a go. I'm using the codes on a small test data set, actual use could be around 5500+ rows.


can your search string be found more than once? Then try:

Sub TheUnionOf3()
Dim varOut() As Variant
Dim sFndPrd As String
Dim c As Range
Dim LRow As Long
Dim firstaddress As String
Dim i As Integer
Dim j As Integer
Dim myCount As Integer

sFndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)

LRow = Cells(Rows.Count, 1).End(xlUp).Row
myCount = WorksheetFunction.CountIf(Range("A1:A" & LRow), sFndPrd)
With Range("A1:A" & LRow)
i = 1
Set c = .Find(sFndPrd, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ReDim Preserve varOut(myCount, 4)
varOut(i, 1) = c.Offset(0, 1)
varOut(i, 2) = c.Offset(0, 2)
varOut(i, 3) = c.Offset(0, 4)
varOut(i, 4) = c.Offset(0, 5)
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And c.Address < firstaddress
End If
.Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount, 4) = varOut
' .Cells(Rows.Count, "K").End(xlUp)(2) _
' .Resize(4, myCount) = WorksheetFunction.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 3:42:57 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 28 Sep 2013 03:33:06 -0700 (PDT) schrieb Howard:



I'll give it a go. I'm using the codes on a small test data set, actual use could be around 5500+ rows.




can your search string be found more than once? Then try:



Sub TheUnionOf3()

Dim varOut() As Variant

Dim sFndPrd As String

Dim c As Range

Dim LRow As Long

Dim firstaddress As String

Dim i As Integer

Dim j As Integer

Dim myCount As Integer



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)



LRow = Cells(Rows.Count, 1).End(xlUp).Row

myCount = WorksheetFunction.CountIf(Range("A1:A" & LRow), sFndPrd)

With Range("A1:A" & LRow)

i = 1

Set c = .Find(sFndPrd, LookIn:=xlValues)

If Not c Is Nothing Then

firstaddress = c.Address

Do

ReDim Preserve varOut(myCount, 4)

varOut(i, 1) = c.Offset(0, 1)

varOut(i, 2) = c.Offset(0, 2)

varOut(i, 3) = c.Offset(0, 4)

varOut(i, 4) = c.Offset(0, 5)

Set c = .FindNext(c)

i = i + 1

Loop While Not c Is Nothing And c.Address < firstaddress

End If

.Cells(Rows.Count, "K").End(xlUp)(2) _

.Resize(myCount, 4) = varOut

' .Cells(Rows.Count, "K").End(xlUp)(2) _

' .Resize(4, myCount) = WorksheetFunction.Transpose(varOut)

End With

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2


I was just ready to post back after trying the find method, for that very reason.

The search string will almost always be in multiples and the return values for identical strings will be different as the code goes down the list in column A. So the end product will be a progression of changes relative to the search string as it is found on down the line.

I'll give this newest code a test flight.

I would like the option to produce the out come list to another sheet also.

Would it look something like this if data was on sheet 10?


Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount, 4) = varOut

Howard
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount, 4) = varOut


that is correct. But you must have Sheet10 active when running the macro
or you have to reference to Sheet10 into the code


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 4:04:50 AM UTC-7, Claus Busch wrote:
Hi Howard,



Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _


.Resize(myCount, 4) = varOut




that is correct. But you must have Sheet10 active when running the macro

or you have to reference to Sheet10 into the code





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2


I plugged that in an indeed it works. Got lucky there..<lol.

One problem with the last code, it misses I entry of all the ones I've tried.

Column A is 1 to 10 with three different search strings. If there are four duplicates it returns three results. Three duplicates returns two. I cannot detect any pattern, it just returns 1 less than exist in the list.

Howard
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sat, 28 Sep 2013 04:14:09 -0700 (PDT) schrieb Howard:

One problem with the last code, it misses I entry of all the ones I've tried.


the last two lines are only if you want to transpose the array. If you
don't want, delete these lines. If you want to transpose, delete the two
lines in front.
But don't run both.

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi again,

Am Sat, 28 Sep 2013 04:14:09 -0700 (PDT) schrieb Howard:

Column A is 1 to 10 with three different search strings. If there are four duplicates it returns three results. Three duplicates returns two. I cannot detect any pattern, it just returns 1 less than exist in the list.


my bad :-(
Change it to:
Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount + 1, 4) = varOut


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sat, 28 Sep 2013 13:28:06 +0200 schrieb Claus Busch:

Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount + 1, 4) = varOut


but now:
Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _
.Resize(myCount + 1, 5) = varOut


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 4:22:12 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 28 Sep 2013 04:14:09 -0700 (PDT) schrieb Howard:



One problem with the last code, it misses I entry of all the ones I've tried.




the last two lines are only if you want to transpose the array. If you

don't want, delete these lines. If you want to transpose, delete the two

lines in front.

But don't run both.



Regards

Claus B.


I left the last two lines commented out. What I see is that it seems to over write the previous posting. The xlUp offset used in the earlier code worsk like I want it to, but coming from the top down to make the list makes more sense to me.

Howard
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 4:30:29 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 28 Sep 2013 13:28:06 +0200 schrieb Claus Busch:



Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _


.Resize(myCount + 1, 4) = varOut




but now:

Sheets("Sheet11").Cells(Rows.Count, "K").End(xlUp)(2) _

.Resize(myCount + 1, 5) = varOut





Regards

Claus B.



I missed that it was not returning all four column, the last correction solves that.

I still have the overwrite problem, It seems to post the input search string correctly, but if I want to search another string, then that result is posted over the previous, instead of compiling a list..

Howard
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sat, 28 Sep 2013 04:51:12 -0700 (PDT) schrieb Howard:

I still have the overwrite problem, It seems to post the input search string correctly, but if I want to search another string, then that result is posted over the previous, instead of compiling a list..


sorry, I forgot a important thing:
Option Base 1.
Try:

Option Explicit
Option Base 1

Sub TheUnionOf3()
Dim varOut() As Variant
Dim sFndPrd As String
Dim c As Range
Dim LRow As Long
Dim FERow As Range
Dim firstaddress As String
Dim i As Integer
Dim myCount As Integer

sFndPrd = Application.InputBox("Enter Col A Item.", _
"Col A Finder", , , , , , 2)
With Sheets("Sheet10")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
myCount = WorksheetFunction.CountIf(.Range("A1:A" & LRow), sFndPrd)
With .Range("A1:A" & LRow)
i = 1
Set c = .Find(sFndPrd, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ReDim Preserve varOut(myCount, 4)
varOut(i, 1) = c.Offset(0, 1)
varOut(i, 2) = c.Offset(0, 2)
varOut(i, 3) = c.Offset(0, 4)
varOut(i, 4) = c.Offset(0, 5)
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
End With

With Sheets("Sheet11")
Set FERow = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)
FERow.Resize(myCount, 4) = varOut
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 5:25:25 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 28 Sep 2013 04:51:12 -0700 (PDT) schrieb Howard:



I still have the overwrite problem, It seems to post the input search string correctly, but if I want to search another string, then that result is posted over the previous, instead of compiling a list..




sorry, I forgot a important thing:

Option Base 1.

Try:



Option Explicit

Option Base 1



Sub TheUnionOf3()

Dim varOut() As Variant

Dim sFndPrd As String

Dim c As Range

Dim LRow As Long

Dim FERow As Range

Dim firstaddress As String

Dim i As Integer

Dim myCount As Integer



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)

With Sheets("Sheet10")

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

myCount = WorksheetFunction.CountIf(.Range("A1:A" & LRow), sFndPrd)

With .Range("A1:A" & LRow)

i = 1

Set c = .Find(sFndPrd, LookIn:=xlValues)

If Not c Is Nothing Then

firstaddress = c.Address

Do

ReDim Preserve varOut(myCount, 4)

varOut(i, 1) = c.Offset(0, 1)

varOut(i, 2) = c.Offset(0, 2)

varOut(i, 3) = c.Offset(0, 4)

varOut(i, 4) = c.Offset(0, 5)

Set c = .FindNext(c)

i = i + 1

Loop While Not c Is Nothing And c.Address < firstaddress

End If

End With

End With



With Sheets("Sheet11")

Set FERow = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)

FERow.Resize(myCount, 4) = varOut

End With

End Sub


Regards

Claus B.



That stacks them in there just right.

Once again, thanks Claus.

Regards,
Howard
  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

Not sure what or how I screwed this up, as it was working just fine until I made some small changes.

123
456
789
147
258
369
321
654
987

These are my search strings and are on Sheet 1 in Col G w/Header.
They are repeated four times down col G.

I want to return six offsets from the column G search string chosen and they a H, J, L, N, P, R

So with any search string I would have four rows of offsets on Sheet 2
K through P and the search string listed once in col J.

Strangely if I select 123 as the search string I get the first set of offsets ONLY, search string is correctly in col J. I should have three more rows of offset data.

If I select 456 I get an error 400 with only the search string entered in col J.

Scratching my head???

Thanks,
Howard

Option Explicit
Option Base 1
Option Compare Text

Sub TheUnionOfOpBaseOneClaus()
Dim varOut() As Variant
Dim sFndPrd As String
Dim c As Range
Dim LRow As Long

Dim FERow As Range

Dim firstaddress As String
Dim i As Integer

Dim myCount As Integer

sFndPrd = Application.InputBox("Enter Col G Item.", _
"Col G Finder", , , , , , 2)

Sheets("Sheet2").Range("K100").End(xlUp).Offset(1, -1) = sFndPrd

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
myCount = WorksheetFunction.CountIf(.Range("G2:G" & LRow), sFndPrd)
With .Range("G2:G" & LRow)

i = 1
Set c = .Find(sFndPrd, LookIn:=xlValues)

If Not c Is Nothing Then
firstaddress = c.Address
Do
ReDim Preserve varOut(myCount, 6)
varOut(i, 1) = c.Offset(0, 1)
varOut(i, 2) = c.Offset(0, 3)
varOut(i, 3) = c.Offset(0, 5)
varOut(i, 4) = c.Offset(0, 7)
varOut(i, 5) = c.Offset(0, 9)
varOut(i, 6) = c.Offset(0, 11)
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And c.Address < firstaddress
End If

End With
End With

With Sheets("Sheet2")
Set FERow = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)
FERow.Resize(myCount, 6) = varOut
End With
End Sub
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Saturday, September 28, 2013 5:52:34 PM UTC-7, Howard wrote:
Not sure what or how I screwed this up, as it was working just fine until I made some small changes.



123

456

789

147

258

369

321

654

987



These are my search strings and are on Sheet 1 in Col G w/Header.

They are repeated four times down col G.



I want to return six offsets from the column G search string chosen and they a H, J, L, N, P, R



So with any search string I would have four rows of offsets on Sheet 2

K through P and the search string listed once in col J.



Strangely if I select 123 as the search string I get the first set of offsets ONLY, search string is correctly in col J. I should have three more rows of offset data.



If I select 456 I get an error 400 with only the search string entered in col J.



Scratching my head???



Thanks,

Howard



Option Explicit

Option Base 1

Option Compare Text



Sub TheUnionOfOpBaseOneClaus()

Dim varOut() As Variant

Dim sFndPrd As String

Dim c As Range

Dim LRow As Long



Dim FERow As Range



Dim firstaddress As String

Dim i As Integer



Dim myCount As Integer



sFndPrd = Application.InputBox("Enter Col G Item.", _

"Col G Finder", , , , , , 2)



Sheets("Sheet2").Range("K100").End(xlUp).Offset(1, -1) = sFndPrd



With Sheets("Sheet1")

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

myCount = WorksheetFunction.CountIf(.Range("G2:G" & LRow), sFndPrd)

With .Range("G2:G" & LRow)



i = 1

Set c = .Find(sFndPrd, LookIn:=xlValues)



If Not c Is Nothing Then

firstaddress = c.Address

Do

ReDim Preserve varOut(myCount, 6)

varOut(i, 1) = c.Offset(0, 1)

varOut(i, 2) = c.Offset(0, 3)

varOut(i, 3) = c.Offset(0, 5)

varOut(i, 4) = c.Offset(0, 7)

varOut(i, 5) = c.Offset(0, 9)

varOut(i, 6) = c.Offset(0, 11)

Set c = .FindNext(c)

i = i + 1

Loop While Not c Is Nothing And c.Address < firstaddress

End If



End With

End With



With Sheets("Sheet2")

Set FERow = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)

FERow.Resize(myCount, 6) = varOut

End With

End Sub


I found my error!

I moved my search strings to column G and made code changes to accommodate that.

I failed to change

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

to

LRow = .Cells(.Rows.Count, 7).End(xlUp).Row

Is working fine now.

Howard
  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Hi Howard,

Am Sun, 29 Sep 2013 00:10:20 -0700 (PDT) schrieb Howard:

Is working fine now.


thank you for the feedback. Have a nice weekend


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

I found my error!

I moved my search strings to column G and made code changes to
accommodate that.

I failed to change

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

to

LRow = .Cells(.Rows.Count, 7).End(xlUp).Row

Is working fine now.


Just want to emphasize that using defined name for the search string
col doesn't break your code...

Dim lSearchStrCol&
lSearchStrCol = Range("SearchStrings").Column
LRow = .Cells(.Rows.Count, lSearchStrCol).End(xlUp).Row

OR

LRow = .Cells(.Rows.Count, Range("SearchStrings").Column).End(xlUp).Row

...because Excel will always know where it is!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion




  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

Just want to emphasize that using defined name for the search string

col doesn't break your code...



Dim lSearchStrCol&

lSearchStrCol = Range("SearchStrings").Column

LRow = .Cells(.Rows.Count, lSearchStrCol).End(xlUp).Row



OR



LRow = .Cells(.Rows.Count, Range("SearchStrings").Column).End(xlUp).Row



..because Excel will always know where it is!



--

Garry



Okay, I think a little light is making its way into my head here.

So I would name the column (or the range within the column) "SearchStrings"?

And if I move the column of strings from A to G like I did here, I would just change the range reference of the name "SearchStrings" in the Name Manager?

Howard
  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Just want to emphasize that using defined name for the search string

col doesn't break your code...



Dim lSearchStrCol&

lSearchStrCol = Range("SearchStrings").Column

LRow = .Cells(.Rows.Count, lSearchStrCol).End(xlUp).Row



OR



LRow = .Cells(.Rows.Count,
Range("SearchStrings").Column).End(xlUp).Row



..because Excel will always know where it is!



--

Garry



Okay, I think a little light is making its way into my head here.

So I would name the column (or the range within the column)
"SearchStrings"?

And if I move the column of strings from A to G like I did here, I
would just change the range reference of the name "SearchStrings" in
the Name Manager?

Howard


No! Excel will automagically update the ref for you!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet


No! Excel will automagically update the ref for you!



--

Garry


How does Excel know I decided I want col G instead of col A?

Howard
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

No! Excel will automagically update the ref for you!



--

Garry


How does Excel know I decided I want col G instead of col A?

Howard


When you design the name Excel creates absolute refs ($) by default.
When you move the range, Excel updates the absolute refs to the new
location. This has been standard behavior for as far back as I can
remember!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet

On Sunday, September 29, 2013 9:33:10 AM UTC-7, GS wrote:
No! Excel will automagically update the ref for you!








--




Garry






How does Excel know I decided I want col G instead of col A?




Howard




When you design the name Excel creates absolute refs ($) by default.

When you move the range, Excel updates the absolute refs to the new

location. This has been standard behavior for as far back as I can

remember!



--

Garry


Okay, I think I got it. I just now selected A1:A5 and named it MyString.

If I select A1:A4 and drag it to G, range stays A1:A5.
If I select A1:A5 (or more, say A1:A8) and drag to G the new reference is G1:G5 and the code looks for MyString where ever it is

Howard


  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

If I select A1:A4 and drag it to G, range stays A1:A5.
If I select A1:A5 (or more, say A1:A8) and drag to G the new
reference is G1:G5 and the code looks for MyString where ever it is


Well.., you should be explicit when stating range refs. In your
example, the original ref was...

=$A$1:$A$5

...and the new ref after moving is...

=$G$1:$G$5

...where this is a *fully absolute* ref. What you posted was *fully
relative* refs. What determines this is the *$* symbol and where it is
positioned with respect to the col/row labels in the range address.

Also, you didn't state that you *explicitly* forced local (sheet level)
scope by including the sheetname when you typed the range name in the
Namebox. The result is the defined named has global (workbook level)
scope which you would *only use when absolutely necessary* if you
follow using 'best practices' in your work!


There are 2 more ref types...

ColAbsolute/RowRelative: e.g.: $A1:$A5

ColRelative/RowAbsolute: e.g.: A$1:A$5

...both of which present specific behavior *relative* to the
non-absolute portion of the ref, and *absolute* to the non-relative
portion.

<Usage example
Say you have a table of amounts on Sheet1 that you need a simple SUM
formula at the bottom. This table has a headings row.

Select A2

Defined name: 'Sheet1'!Hdr_Row
ReferTo: =A$1
Description: Creates a colRelative/rowAbsolute ref *relative* to the
same col as the currently active cell.

Defined name: 'Sheet1'!FirstCell
ReferTo: =OFFSET(Hdr_Row,1,0)
Description: Creates a colRelative/rowAbsolute ref *relative* to the
same col as the currently active cell, absolute to the row below
the
headings row.

Defined name: 'Sheet1'!LastCell
ReferTo: =A1
Description: Creates a fully relative ref *relative* to the
same col as the currently active cell and the row before the
currently active cell.

So your table has say 10 rows of amounts and the totals go in row 12 so
that the table area is contiguous headings to totals. Select all the
cells to receive totals in the totals row and type the following
formula...

=SUM(FirstRow:LastRow)

...then while holding down the Ctrl key, press Enter to put the formula
into all selected cells in one shot.

Now you can insert/delete rows anywhere between the headings row and
the totals row without breaking anything.

Note that the formula reads the same in each totals cell, and it's
self-explanatory as to what cells are involved. IMO, this makes for
easier understanding of how the formula works compared to using cell
addresses that have to be followed in order for the user to figure it
out!

Note that all the named ranges have local scope, allowing the same
names to be used on other sheets with similar requirements.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to alist on same & another sheet


Okay, so I should do the naming using Define Name where I can select the sheet name from the Scope drop down.

Seems to me the default is *fully absolute* as the name always shows up in the name manager $A$1:$A$5...

I think I'm getting more or less what you are saying, just need to start trying to use it more often.

Howard



  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list on same & another sheet

Okay, so I should do the naming using Define Name where I can select
the sheet name from the Scope drop down.

Seems to me the default is *fully absolute* as the name always shows
up in the name manager $A$1:$A$5...

I think I'm getting more or less what you are saying, just need to
start trying to use it more often.

Howard


Yes, fully absolute is the default ref type. You'll have to manually
configure if you want to use one of the other ref types.

For fully absolute refs you can just type in the Namebox left of the
FormulaBar. Just remember to prepend the sheetname as shown for
assigning local scope. Note that if there's no spaces in the sheetname
then the apostrophes are not required.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Take active cell value in for loop and offset based on another cellvalue for columns to offset burl_h Excel Programming 0 May 23rd 12 03:20 AM
Copy link to offset sheet SNACK D Excel Worksheet Functions 5 December 14th 07 02:38 AM
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul RyGuy Excel Worksheet Functions 2 September 28th 07 10:54 PM
Find value from sheet 1 on sheet 2 and copy to an offset from there L. Howard Kittle Excel Programming 3 March 2nd 07 10:32 PM
Find, Copy offset to offset on other sheet, Run-time 1004. Finny[_3_] Excel Programming 10 December 8th 06 12:46 AM


All times are GMT +1. The time now is 05:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"