The data all comes from various places in the same workbook. The cell's being copied are formula's. Could that have something to do with it? If I add to the end of the macro to paste the copied range into a blank area of the same worksheet it works no problem.
Posts by CaulfieldS
-
-
RoyUK correct, if it didnt glitch then the "select" wouldn't be needed. however to diagnose where it's having its issue i replaced "copy" with "select" then after the macro has run i "Ctrl+C" then paste elsewhere and it works just fine. Which leads me to understand there is an issue with the last "copy" command. The weird part is that its not consistent, some instances the final copy command works, and some not.
-
thank you RoyUK. It is now retaining the right information on the clipboard a third of the time during my tests. If i revise line 12 of your code "copy" to "select" then manually "Ctrl+C" then switch programs and paste it works flawlessly the entire time
-
I have a macro that takes the current selection, copies it, paste's it in a certain cell, copies it again (unsure if that is necessary) paste's again forming a list then its supposed to copy a range that i paste outside the program (non Microsoft program). The issue im running into is that in excel it shows the final range selected and copied (Green boarder with white "ants" circling around) however when i switch programs to paste the data, the original cell i copied is whats pasted.
Code
Display MoreSub SwitchRiderForTimes() Dim numrows As Long ' SwitchRiderForTimes Macro Application.ScreenUpdating = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("AP2").Select Selection.Copy Range("An1").End(xlDown).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False numrows = Range("aP1") Range("D1:D" & numrows).Select Selection.Copy End Sub -
Are you saying that you no longer need a macro?
I guess at this point I do not. Thank you Mumps for attempting to help!
-
I currently have it set up so that on worksheet "Input" I take a horses name then i move over to a column im adjusting, add in a new date and continue until i have added all new information. On page Dats at the very top is where all this information is compiled,
moving to worksheet "dats"
range e200:T225 is a link to the same information i input on worksheet "input"range E228:T253 looks up the names found in E:200:E225 and pulls the previously entered information from the top of the page
range E256:T281 compares the two ranges above substituting information from E:228:T253 with information from E200:T225
range E328:T370 compiles the entire section of data at the top of the page with the new data mixed with old data in range E256:T281 which i can then use a simple copy, paste value macro to update the data at the top of the page.
Im much better at formula's then macros so while this may seem like a huge run around to get to my goal it end's up with the same expected results
-
I downloaded it and it seemed to work just fine :o2.
I may have figured out a way to go about this, well, maybe just an easier way?
On worksheet "dats" starting down at E199 i have brought over the information from worksheet "Input" and with vlookup and if statement's compiled the data to contain what i need. i just need to figure out a macro that will, go through E256:E281, find cells not equal to 0, copy from column F to column T then return to the top of the page, find a match to the cell in column E and paste.
-
this may be far stretch but im wondering how(if) i could create a macro that will do the following,
check cell for contents > copy contents > move to separate sheet > search for condition one (relative to original cell's location) > search for condition two (relative to original cell's contents) > find where x/y intersect and paste.
This to my basic knowledge seems like the most direct way about it I figure i could use a long process of lookup and true/false, but with 645 cells to go through might take awhile to write/go through
Id like a macro that will search each cell in the range E29:S50 on worksheet "Input" and if a cell is occupied it copies that cell moves to worksheet "Dats" then looks down column E to find the corresponding name from "input" worksheet and paste. Looking at the workbook may help make this make sense.
Thanks in advance,
-
I assume i would use some version of the below formula, except im unsure how to instead of trying to match two criteria to match one of three criteria returning the first match
=INDEX("DATA RANGE", SMALL(IF(COUNTIF("VALUE 1", "POSSIBLE VALUE 1 RANGE")*COUNTIF("VALUE 2", "POSSIBLE VALUE 2 RANGE"), ROW("RESULT LOCATION")-MIN(ROW("RESULT LOCATION"))+1), ROW(A1)), COLUMN(A1))
-
Hey everyone,
Plain and Simple - Looking for some help putting together a formula that will search a range for one of three different values (those values can either be defined, or cell references) then return the first match, then second, then third and so forth.
Reference- I create a schedule weekly that excel has proven to be very helpful in creating, the only downside is that the receiving end of the schedule prefers it in a clean PDF file thus i have a blank Publisher document with a grid table setup that i manual transfer information from excel over to.
I have attached a copy of the worksheet i am trying to put together with this formula,
Thanks in advance.
-
Hey everyone,
Plain and Simple - Looking for some help putting together a formula that will search a range for one of three different values (those values can either be defined, or cell references) then return the first match, then second, then third and so forth.
Reference- I create a schedule weekly that excel has proven to be very helpful in creating, the only downside is that the receiving end of the schedule prefers it in a clean PDF file thus i have a blank Publisher document with a grid table setup that i manual transfer information from excel over to.
I have attached a copy of the worksheet i am trying to put together with this formula, along with an example of the preferred format for the receiving end.
Thanks in advance.
[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1214608}[/ATTACH]
-
If you have previously done a Find operation manually and specified to look in the whole workbook, your Replace operation in code will pick that up. Doing a spurious Find operation in code as I suggested will clear that.
Just did a test and you are right, while preparing to upload a single sheet i went through to change names, and numbers by using "find and replace" localized to that one page, then running the macro to "test" and it worked only on that page. So as you stated Rory "find and replace" will take the same setting from a previous "find and replace" done manually and use them in the automated macro thus adding in a line of code that does a "find" on that particular sheet will set up the next line to only find/replace that sheet.
-
Not sure exactly what happened, I opened the workbook this morning, copied the page out to a new book to upload here. Ran the macro one more time to confirm everything would continue to work with only one page of the book and it works just the way it did the first time i set it up.
Here is the code as it sits now as i do not plan on poking it if it works. Thank you Carim!Code
Display MoreSub CommandButton1_Click() Dim ws As Worksheet ' Set ws as another worksheet ... if need be ... Set ws = Sheets("Weekly Schedule") Application.ScreenUpdating = False With ws .Range("AM2:BD50").Copy .Range("A2").PasteSpecial Paste:=xlPasteValues .Range("A2:C50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("D2:F50").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("G2:I50").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("J2:L50").Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("M2:O50").Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("P2:R50").Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("schedule").Replace What:="x", Replacement:="", LookAt:=xlWhole .Range("schedule").Replace What:="0", Replacement:="", LookAt:=xlWhole .Range("AN53:AZ81").Copy .Range("B53").PasteSpecial Paste:=xlPasteValues .Range("A51").Select End With Application.ScreenUpdating = False End Sub -
Sorry new to VBA which is why im having some issue's still edited as so with activesheet replaced with "sheets("weekly schedule") it still operates throughout the entire workbook.
Code
Display MorePrivate Sub CommandButton1_Click() Dim ws As Worksheet ' Set ws as another worksheet ... if need be ... Set ws = Sheets("Weekly Schedule") Application.ScreenUpdating = False With ws .Range("AM2:BD50").Copy .Range("A2").PasteSpecial Paste:=xlPasteValues .Range("A2:C50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("D2:F50").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("G2:I50").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("J2:L50").Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("M2:O50").Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("P2:R50").Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("schedule").Replace What:="x", Replacement:="", LookAt:=xlWhole .Range("schedule").Replace What:="0", Replacement:="", LookAt:=xlWhole .Range("AN53:AZ81").Copy .Range("B53").PasteSpecial Paste:=xlPasteValues .Range("A51").Select End With Application.ScreenUpdating = False End Sub -
Unfortunately this did not work
I still end up with "replace" replacing all "x" & "0" throughout the entire workbook instead of just Range: Schedule. i could even settle for being limited to worksheet "Weekly Schedule"Display MoreHello,
You could test following macro :
Code
Display MorePrivate Sub CommandButton1_Click() Dim ws As Worksheet ' Set ws as another worksheet ... if need be ... Set ws = ActiveSheet Application.ScreenUpdating = False With ws .Range("AM2:BD50").Copy .Range("A2").PasteSpecial Paste:=xlPasteValues .Range("A2:C50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("D2:F50").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("G2:I50").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("J2:L50").Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("M2:O50").Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("P2:R50").Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Range("schedule").Replace What:="x", Replacement:="", LookAt:=xlWhole .Range("schedule").Replace What:="0", Replacement:="", LookAt:=xlWhole .Range("AN53:AZ81").Copy .Range("B53").PasteSpecial Paste:=xlPasteValues .Range("A51").Select End With Application.ScreenUpdating = False End SubHope this will help
Edit : Hello Kenneth ...:wink:
-
That was pretty much the whole macro
Code
Display MorePrivate Sub CommandButton1_Click() Application.ScreenUpdating = False Range("AM2:BD50").Select Selection.Copy Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2:C50").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("D2:F50").Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("G2:I50").Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("J2:L50").Select Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("M2:O50").Select Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("P2:R50").Select Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("schedule").Select Selection.Replace What:="x", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("AN53:AZ81").Select Selection.Copy Range("B53").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A51").Select End Sub -
Im having a hard to figuring out how to narrow the "selection.replace" code in my macro to only search one sheet of my workbook. My thought was prior to this line including Range ("").Select would give the "selection.replace" a defined area to work in but that appears to be untrue.
FYI: i am working in excel 2003, Old, outdated i know, but until I outgrow potential of this program i cannot justify the cost of the newest edition.
Here is the macro being used to condense information on multiple sheets to one sheet, then to exclude empty data replace "x" and "0" with blank cells.
Code
Display MoreApplication.ScreenUpdating = False Range("AM2:BD50").Select Selection.Copy Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2:C50").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("D2:F50").Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("G2:I50").Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("J2:L50").Select Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("M2:O50").Select Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("P2:R50").Select Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("schedule").Select Selection.Replace What:="x", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("AN53:AZ81").Select Selection.Copy Range("B53").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A51").Select -
The excel book im currently (and forever) updating is used to co-ordinate lessons and rides for boarders on horses. Currently if a boarder is taking a lesson they get onto the horse 15 minutes prior to the lesson If the trainer (David) is taking a lesson he gets on at the lesson start time since he is on another horse prior. Therefore i have the following code,
Code=IF(ISERROR(IF(C18="David",B86,B86-TIME(0,15,0))),"",IF(C18="David",B86,B86-TIME(0,15,0)))My question stems from when i have a boarder taking back to back lessons for the second lesson they too have to get on the horse at the lesson start time and not 15 minutes prior thus my question if there is a simple way to add in a second stipulation to achieve this goal without having to manually change the time.
-
Re: VLOOKUP return multiple values
Since the entire book contains some "privileged" information ive moved a couple pages i have been toying with to get a formula working to a new book with some raw data.
The attached workbook has 3 pages, 1st sheet "same page formula" has the data and formula working off the same page which will not be how i use the formula in the long run. the following page "lookup" contains the formula and the "keyword" then the last page "Data" contains the data table the formula should be searching for answers.
Note that on the lookup page the formula seems to work but the data i copied to the right being the data it should bring back shows the information it is grabbing is not accurate.
Thank you everyone for the help. It is much appreciated.
-
Re: VLOOKUP return multiple values
NBVC This is the formula i was able to make work,
=OFFSET(F1,SMALL(IF(F2:F5000=A3,ROW(F2:F5000)-ROW(INDEX(F2:F5000,1,1))+1),ROW()-2),COLUMN()-1)
For some reason (i suspect that its excell 2003) when using the "$" signs the code does not work since the following does not work
=OFFSET($F$1,SMALL(IF($F$2:$F$5000=$A$3,ROW($F$2:$F$5000)-ROW(INDEX($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1)
that being said even though i have the first formula working on a set of "test" data on a test worksheet, when i try to alter the code to work off a table on the same worksheet it comes up with "#NUM!" . im stumped becuase i replace everything correctly outside of excel before pasting back into excel, or even go into formula edit and drag the boxes into the table i want to search and still get the same "#NUM!" error.