Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
I have this code here where it finds and copies the entire row where
the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
Have you tried changing
rngFoundAll.EntireRow.Copy rngDestination to rngFoundAll.EntireRow.Cut rngDestination -- Regards, Tom Ogilvy "RigasMinho" wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
Yeah i tried that already and it gives me an error.
I'm able to use the cut on this other code i made but that code is really hard to understand lol. Tom Ogilvy wrote: Have you tried changing rngFoundAll.EntireRow.Copy rngDestination to rngFoundAll.EntireRow.Cut rngDestination -- Regards, Tom Ogilvy "RigasMinho" wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
Yeah i tried that already and it gives me an error.
I'm able to use the cut on this other code i made but that code is really hard to understand lol. Tom Ogilvy wrote: Have you tried changing rngFoundAll.EntireRow.Copy rngDestination to rngFoundAll.EntireRow.Cut rngDestination -- Regards, Tom Ogilvy "RigasMinho" wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
How about just using .copy and then delete those original rows?
rngFoundAll.EntireRow.Copy rngDestination rngfoundall.entirerow.delete RigasMinho wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
Heheh thought of that too
But at the end i will use that command line if i have too. but currently i dont want to do it that way. i guess i'm just bored so i want to try to figure this out. I also tried putting the cut line inside the loop. That cuts it over but craps out and asks to be debugged. Thanks though for your suggestion Dave Peterson wrote: How about just using .copy and then delete those original rows? rngFoundAll.EntireRow.Copy rngDestination rngfoundall.entirerow.delete RigasMinho wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
I didn't examine your code closely enough. To the best of my knowledge, you
can't CUT a discontiguous range. Just to confirm: From VBA help on the CUT method: The cut range must be made up of adjacent cells. -- Regards, Tom Ogilvy "RigasMinho" wrote: Yeah i tried that already and it gives me an error. I'm able to use the cut on this other code i made but that code is really hard to understand lol. Tom Ogilvy wrote: Have you tried changing rngFoundAll.EntireRow.Copy rngDestination to rngFoundAll.EntireRow.Cut rngDestination -- Regards, Tom Ogilvy "RigasMinho" wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut instead of Copy
If you're cutting the line, you'll have to change the way your do/loop works.
You'll never find the first occurence again--it's already been moved. Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Do Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) if rngfound is nothing then exit do end if rngfound.entirerow.cut _ destination:=rngdestination set rngdestination=rngdestination.offset(1,0) loop ===== Untested, uncompiled. Watch for typos. RigasMinho wrote: Heheh thought of that too But at the end i will use that command line if i have too. but currently i dont want to do it that way. i guess i'm just bored so i want to try to figure this out. I also tried putting the cut line inside the loop. That cuts it over but craps out and asks to be debugged. Thanks though for your suggestion Dave Peterson wrote: How about just using .copy and then delete those original rows? rngFoundAll.EntireRow.Copy rngDestination rngfoundall.entirerow.delete RigasMinho wrote: I have this code here where it finds and copies the entire row where the search is made. But instead of copying the entire row is there a way to cut the information instead? Let me know thanks Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A1") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("E:F") Set rngFound = rngToSearch.Find(What:="AC", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress 'rngFoundAll.Copy rngDestination 'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3 rngFoundAll.EntireRow.Copy rngDestination End If -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
How copy format, font, color and border without copy/paste? | Excel Programming |