Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and loop help-multiple columns
I an trying to write a macro to search for a cell value in one column and see
if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row 'paste the info ActiveSheet.Paste 'start checking for duplicate route numbers Dim rngToSearch As Range Dim rngToFind As Range Dim rngFound As Range Dim rng As Range Dim wks As Worksheet Set rngToSearch = .Range('NEED HELP HERE--myrow+5') Set rngToFind = .Range('NEED HELP HERE--all other columns with "Route Number(s)" in Row 2') For Each rng In rngToSearch Set rngFound = rngToFind.Find(What:=rng.Value, MatchCase:=False) If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time" Else rng.Offset(0, 10).Value = "Repeat" Next rng End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and loop help-multiple columns
What do you want to do with it if you find it in other columns? If you just
want to know it it is there then you could use the CountIf method to just see if it is there. If you want to do something with any of the other cells where it is found then you could use a For...Each statement. Assume the data is located tin A through n number of colums and 2 through n number of rwos which vary by column. Dim lr As long, lc As long 'delare variables for last row and column Dim sh As Worksheet, cRng As Range 'Initialize the variables Set sh = ActiveSheet lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row lc = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, MatchCase:=False).Column Set cRng = sh.Range("A2:A" & lr) 'assign the value to find to a variable fVar = sh.Range("BB1").Value 'Arbitrary value, replace with actual 'See if the value exists in the control range Set fItem = sh.cRng.Find("fVar", After:=Range("A" & lr), LookIn:=xlValues) If Not fItem Is Nothing Then myNum = CountIf( sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)), fItem.Value) MsgBox "There are " & myNum & " other occurrences of " & fVar End If End Sub Using the For ... Each you would basically set upt the range the same but Once the item is found in the control range you would: For Each fRng in sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)) If fRng.Value = fVar Then 'Do something End If Next This code has not been tested and is provided to illustrate methods for searching items in a data base. "mattg" wrote in message ... I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row 'paste the info ActiveSheet.Paste 'start checking for duplicate route numbers Dim rngToSearch As Range Dim rngToFind As Range Dim rngFound As Range Dim rng As Range Dim wks As Worksheet Set rngToSearch = .Range('NEED HELP HERE--myrow+5') Set rngToFind = .Range('NEED HELP HERE--all other columns with "Route Number(s)" in Row 2') For Each rng In rngToSearch Set rngFound = rngToFind.Find(What:=rng.Value, MatchCase:=False) If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time" Else rng.Offset(0, 10).Value = "Repeat" Next rng End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and loop help-multiple columns
I added a couple of For loops to your code to solve the problem. I did not test the code but you should be able to figure it out yourself With lastperiod 'Find the last used column LastCol = .UsedRange.Columns.Count 'find the last row LastRow = .Cells.Find(What:="*", _ lookin:=xlvalues, _ lookat:=xlpart, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row 'paste the info Paste for ColCount = 1 to Lastcol if .cells(2,Colcount) = "Route Number(s)" then for Rowcount = Lastrow to 4 step -1 Set rngToFind = range(.cells(4,ColCount),.cells(RowCount,colcount) ) Route = .cells(RowCount,ColCount) Set rngFound = rngToFind.Find(What:=Route, MatchCase:=False) If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time" Else rng.Offset(0, 10).Value = "Repeat" end if next RowCount end if next Colcount End With -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197104 http://www.thecodecage.com/forumz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and loop help-multiple columns
Thanks for the help. What I am doing is pasting a running report. So if
value "1234" is in the newest data I want to see how many times it has appeared on the report before. So ulimately, if "1234" appeared 2 other times in past reports I would want a destination cell to say "Appears 3 times" "JLGWhiz" wrote: What do you want to do with it if you find it in other columns? If you just want to know it it is there then you could use the CountIf method to just see if it is there. If you want to do something with any of the other cells where it is found then you could use a For...Each statement. Assume the data is located tin A through n number of colums and 2 through n number of rwos which vary by column. Dim lr As long, lc As long 'delare variables for last row and column Dim sh As Worksheet, cRng As Range 'Initialize the variables Set sh = ActiveSheet lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row lc = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, MatchCase:=False).Column Set cRng = sh.Range("A2:A" & lr) 'assign the value to find to a variable fVar = sh.Range("BB1").Value 'Arbitrary value, replace with actual 'See if the value exists in the control range Set fItem = sh.cRng.Find("fVar", After:=Range("A" & lr), LookIn:=xlValues) If Not fItem Is Nothing Then myNum = CountIf( sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)), fItem.Value) MsgBox "There are " & myNum & " other occurrences of " & fVar End If End Sub Using the For ... Each you would basically set upt the range the same but Once the item is found in the control range you would: For Each fRng in sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)) If fRng.Value = fVar Then 'Do something End If Next This code has not been tested and is provided to illustrate methods for searching items in a data base. "mattg" wrote in message ... I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row 'paste the info ActiveSheet.Paste 'start checking for duplicate route numbers Dim rngToSearch As Range Dim rngToFind As Range Dim rngFound As Range Dim rng As Range Dim wks As Worksheet Set rngToSearch = .Range('NEED HELP HERE--myrow+5') Set rngToFind = .Range('NEED HELP HERE--all other columns with "Route Number(s)" in Row 2') For Each rng In rngToSearch Set rngFound = rngToFind.Find(What:=rng.Value, MatchCase:=False) If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time" Else rng.Offset(0, 10).Value = "Repeat" Next rng End With . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and loop help-multiple columns
If you just want to count the occurrences then you could use the =COUNTIF()
function in Excel without bothering with VBA Check it out in the help file. Just type in COUNTIF in the search box then click on it when the topic list come up. It will explain how to use the function.. "mattg" wrote in message ... Thanks for the help. What I am doing is pasting a running report. So if value "1234" is in the newest data I want to see how many times it has appeared on the report before. So ulimately, if "1234" appeared 2 other times in past reports I would want a destination cell to say "Appears 3 times" "JLGWhiz" wrote: What do you want to do with it if you find it in other columns? If you just want to know it it is there then you could use the CountIf method to just see if it is there. If you want to do something with any of the other cells where it is found then you could use a For...Each statement. Assume the data is located tin A through n number of colums and 2 through n number of rwos which vary by column. Dim lr As long, lc As long 'delare variables for last row and column Dim sh As Worksheet, cRng As Range 'Initialize the variables Set sh = ActiveSheet lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row lc = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _ LookIn:=xlFormulas, SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, MatchCase:=False).Column Set cRng = sh.Range("A2:A" & lr) 'assign the value to find to a variable fVar = sh.Range("BB1").Value 'Arbitrary value, replace with actual 'See if the value exists in the control range Set fItem = sh.cRng.Find("fVar", After:=Range("A" & lr), LookIn:=xlValues) If Not fItem Is Nothing Then myNum = CountIf( sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)), fItem.Value) MsgBox "There are " & myNum & " other occurrences of " & fVar End If End Sub Using the For ... Each you would basically set upt the range the same but Once the item is found in the control range you would: For Each fRng in sh.Range(sh.Cells(2, 2), sh.Cells(lc, lr)) If fRng.Value = fVar Then 'Do something End If Next This code has not been tested and is provided to illustrate methods for searching items in a data base. "mattg" wrote in message ... I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row 'paste the info ActiveSheet.Paste 'start checking for duplicate route numbers Dim rngToSearch As Range Dim rngToFind As Range Dim rngFound As Range Dim rng As Range Dim wks As Worksheet Set rngToSearch = .Range('NEED HELP HERE--myrow+5') Set rngToFind = .Range('NEED HELP HERE--all other columns with "Route Number(s)" in Row 2') For Each rng In rngToSearch Set rngFound = rngToFind.Find(What:=rng.Value, MatchCase:=False) If rngFound Is Nothing Then rng.Offset(0, 10).Value = "First Time" Else rng.Offset(0, 10).Value = "Repeat" Next rng End With . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum multiple columns; find max | Excel Programming | |||
Doing a LOOP for multiple columns, help please... | Excel Programming | |||
find number in multiple columns and rows | Excel Worksheet Functions | |||
find and delete a character from multiple columns | Excel Programming | |||
Find Values 0 Over Multiple Columns | Excel Programming |