Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
I have 15 minute interval data in column A and through vb code I need to
select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ....... 11:30 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
Hey stan, not sure if this helps you or not... but using an alternative
instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
I can certainly work with that. Many thanks!
Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
I haven't messed with the VBA code for autofilters in a while and being
untested this is just a guess... but just looking at the code I have to question what exactly are you doing there. You are trying to turn a filter on, filter for field 1 critieria 1... then turn the filter off. I think turning the filter off is contradicting what you just did and it errors out. But im not completely sure "Stan" wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
Activesheet.autofiltermode = false
will remove the arrows (and show all the data) To show all the data, but keep the arrows. with activesheet If .FilterMode Then .ShowAllData End If End if Stan wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
That's perfect Dave. Many thanks!
I do have another question. The function =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like to also return the value in column C. In other words, if there is a match for column B to Column A then I would like to copy and paste the value from Column C from the row where columns A and B match. Any help you can provide would be greatly appreciated!! "Dave Peterson" wrote: Activesheet.autofiltermode = false will remove the arrows (and show all the data) To show all the data, but keep the arrows. with activesheet If .FilterMode Then .ShowAllData End If End if Stan wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
Do you mean you want to change the formula or do you mean that you really want
to copy|paste? I'm guessing the formula: =if(iserror(match(a1,$b$1:$b$6,0)),"",index($c$1:$ c$6,match(a1,$b$1:$b$6,0))) Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) Stan wrote: That's perfect Dave. Many thanks! I do have another question. The function =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like to also return the value in column C. In other words, if there is a match for column B to Column A then I would like to copy and paste the value from Column C from the row where columns A and B match. Any help you can provide would be greatly appreciated!! "Dave Peterson" wrote: Activesheet.autofiltermode = false will remove the arrows (and show all the data) To show all the data, but keep the arrows. with activesheet If .FilterMode Then .ShowAllData End If End if Stan wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
You've done it again Dave! You've helped me out before and I really
apprecaiate it! You're incredibly knowledgeable and there hasn't been anything I've submitted you haven't been able to answer. Many thanks for all your help sir! "Dave Peterson" wrote: Do you mean you want to change the formula or do you mean that you really want to copy|paste? I'm guessing the formula: =if(iserror(match(a1,$b$1:$b$6,0)),"",index($c$1:$ c$6,match(a1,$b$1:$b$6,0))) Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) Stan wrote: That's perfect Dave. Many thanks! I do have another question. The function =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like to also return the value in column C. In other words, if there is a match for column B to Column A then I would like to copy and paste the value from Column C from the row where columns A and B match. Any help you can provide would be greatly appreciated!! "Dave Peterson" wrote: Activesheet.autofiltermode = false will remove the arrows (and show all the data) To show all the data, but keep the arrows. with activesheet If .FilterMode Then .ShowAllData End If End if Stan wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
vb code question
Glad it worked for you.
(And I just got here first!) Stan wrote: You've done it again Dave! You've helped me out before and I really apprecaiate it! You're incredibly knowledgeable and there hasn't been anything I've submitted you haven't been able to answer. Many thanks for all your help sir! "Dave Peterson" wrote: Do you mean you want to change the formula or do you mean that you really want to copy|paste? I'm guessing the formula: =if(iserror(match(a1,$b$1:$b$6,0)),"",index($c$1:$ c$6,match(a1,$b$1:$b$6,0))) Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) Stan wrote: That's perfect Dave. Many thanks! I do have another question. The function =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like to also return the value in column C. In other words, if there is a match for column B to Column A then I would like to copy and paste the value from Column C from the row where columns A and B match. Any help you can provide would be greatly appreciated!! "Dave Peterson" wrote: Activesheet.autofiltermode = false will remove the arrows (and show all the data) To show all the data, but keep the arrows. with activesheet If .FilterMode Then .ShowAllData End If End if Stan wrote: I can certainly work with that. Many thanks! Oh, and by the way once auto filter is turn on, do you know the code to turn it off? Selection.AutoFilter.Off is working but giving me an error. Range("B2:B2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="7760" Selection.AutoFilter.Off "akphidelt" wrote: Hey stan, not sure if this helps you or not... but using an alternative instead of VB code, which would be a much more complicated method then this. But say you hide column B or put the data in Column C and hide Column C. In column C put the formula =If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) Copy this formula and drag down to the end of your data... make sure you change B6 to wherever the data ends and to make sure to keep the $ signs. This formula will leave any value that does not match blank, and if the value exists it will fill in the value. So you will get your desired results... im just not sure if this is how you wanted to get them. "Stan" wrote: I have 15 minute interval data in column A and through vb code I need to select the data in column B and have it moved to match the cooresponding row in column A. In other words, if there isn't an interval of 6:15 in column B then I want to leave that row blank. In the example below, the interval in Column B row 2 should be moved to row 3 since it matches what's in column A. Any help you can provide would be greatly appreciated! Many thanks! Column A Column B Desired Results for Column B 1) 6:00 6:00 6:00 2) 6:15 6:30 3) 6:30 6:45 6:30 4) 6:45 7:15 6:45 5) 7:00 6) 7:15 7:15 ...... 11:30 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vb code question | Excel Discussion (Misc queries) | |||
question on VB code | Excel Discussion (Misc queries) | |||
Another code question | Excel Discussion (Misc queries) | |||
VB Code Question | Excel Discussion (Misc queries) | |||
VB Code Question | Excel Discussion (Misc queries) |