Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Take active cell value in for loop and offset based on another cellvalue for columns to offset | Excel Programming | |||
Copy link to offset sheet | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Find value from sheet 1 on sheet 2 and copy to an offset from there | Excel Programming | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming |