Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
I have a workbook with multiple sheets, what I want to do is create a
macro that will check the date in one cell on sheet 1 against the date on a cell in sheet 2, if they match I want it to copy the data from F5:F22 on sheet 1 (named current month) and paste to sheet 2 (named data) in the rows under the cell where the date was found; Example: May-12 is in column AD, so I want to paste the data into AD3:AD20, then in June it will shift a column to AE3:20. Here is where it gets tricky though, I need the macro to also check the number and see if the meter rolled over (EG. 99999 to 00002) and if it did I need it to basically adapt that to what it would have been if it hadn't rolled over (EG. take 99999 and add 00002 to get 100001). So it will need to take sheet 1 (current month) F5:22 and compare it to E5:22 and if the number in F5:22 is lower than E5:22 it will need to add the number from G5:22 to E5:22 and then paste the result of that. It would also be handy if the macro could annotate the cell with a note like "rolled over" on it so we can track how fast these meters roll over. I have the following formula in cells G5:22 to account for a roll over: Code:
=IF(F16<E16,(F16+1000000)-E16,F16-E16) the first page to show the proper amount of usage. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
Justin, the good news is what you want to do is very doable. You have
done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLC http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23*pm, Justin Pulley wrote: I have a workbook with multiple sheets, what I want to do is create a macro that will check the date in one cell on sheet 1 against the date on a cell in sheet 2, if they match I want it to copy the data from F5:F22 on sheet 1 (named current month) and paste to sheet 2 (named data) in the rows under the cell where the date was found; Example: May-12 is in column AD, so I want to paste the data into AD3:AD20, then in June it will shift a column to AE3:20. Here is where it gets tricky though, I need the macro to also check the number and see if the meter rolled over (EG. 99999 to 00002) and if it did I need it to basically adapt that to what it would have been if it hadn't rolled over (EG. take 99999 and add 00002 to get 100001). So it will need to take sheet 1 (current month) F5:22 and compare it to E5:22 and if the number in F5:22 is lower than E5:22 it will need to add the number from G5:22 to E5:22 and then paste the result of that. It would also be handy if the macro could annotate the cell with a note like "rolled over" on it so we can track how fast these meters roll over. I have the following formula in cells G5:22 to account for a roll over: Code:
=IF(F16<E16,(F16+1000000)-E16,F16-E16) the first page to show the proper amount of usage. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
On Jun 7, 9:39*am, Bob Flanagan wrote:
Justin, the good news is what you want to do is very doable. *You have done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. *If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. *And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLChttp://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23*pm, Justin Pulley wrote: Well I have very little experience using VBA at all; I have a bit of C+ + and Java/HTML, but those are different. If there is somewhere I can go to get an idea of the commands available in VBA I would be happy to try my hand at writing some of the code. I might just have to bite the bullet and buy a VBA programming book, but I want to avoid that if possible because I am not being paid back for those kinds of things. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell and copying/pasting data
Justin Pulley wrote on 6/7/2012 :
On Jun 7, 9:39*am, Bob Flanagan wrote: Justin, the good news is what you want to do is very doable. *You have done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. *If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. *And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLChttp://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23*pm, Justin Pulley wrote: Well I have very little experience using VBA at all; I have a bit of C+ + and Java/HTML, but those are different. If there is somewhere I can go to get an idea of the commands available in VBA I would be happy to try my hand at writing some of the code. I might just have to bite the bullet and buy a VBA programming book, but I want to avoid that if possible because I am not being paid back for those kinds of things. IMO, if you're being paid to do VBA programming projects then you owe it to yourself to upgrade your employable skills on your own dime & time. Otherwise, doing the task manually while recording a macro is a good start toward getting exposure to VBA commands, and the objects Excel exposes to VBA. You can do this via Tools-Macros-Record Macro (pre-XL2007), or via the Developer tab in versions using the MSO Ribbon. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
On Jun 7, 2:25*pm, GS wrote:
Justin Pulley wrote on 6/7/2012 : On Jun 7, 9:39 am, Bob Flanagan wrote: Justin, the good news is what you want to do is very doable. You have done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLChttp://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23 pm, Justin Pulley wrote: Well I have very little experience using VBA at all; I have a bit of C+ + and Java/HTML, but those are different. *If there is somewhere I can go to get an idea of the commands available in VBA I would be happy to try my hand at writing some of the code. *I might just have to bite the bullet and buy a VBA programming book, but I want to avoid that if possible because I am not being paid back for those kinds of things. IMO, if you're being paid to do VBA programming projects then you owe it to yourself to upgrade your employable skills on your own dime & time. Otherwise, doing the task manually while recording a macro is a good start toward getting exposure to VBA commands, and the objects Excel exposes to VBA. You can do this via Tools-Macros-Record Macro (pre-XL2007), or via the Developer tab in versions using the MSO Ribbon. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion- Hide quoted text - - Show quoted text - I'm not being paid for this, nor am I in a field that typically uses VBA coding. I am, however, eventually going to be in upper management and this skill may prove useful. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell and copying/pasting data
Justin Pulley explained on 6/7/2012 :
On Jun 7, 2:25*pm, GS wrote: Justin Pulley wrote on 6/7/2012 : On Jun 7, 9:39 am, Bob Flanagan wrote: Justin, the good news is what you want to do is very doable. You have done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLChttp://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23 pm, Justin Pulley wrote: Well I have very little experience using VBA at all; I have a bit of C+ + and Java/HTML, but those are different. *If there is somewhere I can go to get an idea of the commands available in VBA I would be happy to try my hand at writing some of the code. *I might just have to bite the bullet and buy a VBA programming book, but I want to avoid that if possible because I am not being paid back for those kinds of things. IMO, if you're being paid to do VBA programming projects then you owe it to yourself to upgrade your employable skills on your own dime & time. Otherwise, doing the task manually while recording a macro is a good start toward getting exposure to VBA commands, and the objects Excel exposes to VBA. You can do this via Tools-Macros-Record Macro (pre-XL2007), or via the Developer tab in versions using the MSO Ribbon. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion- Hide quoted text - - Show quoted text - I'm not being paid for this, nor am I in a field that typically uses VBA coding. I am, however, eventually going to be in upper management and this skill may prove useful. Best wishes in your endeavors... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
On Jun 7, 6:26*pm, GS wrote:
Justin Pulley explained on 6/7/2012 : On Jun 7, 2:25*pm, GS wrote: Justin Pulley wrote on 6/7/2012 : On Jun 7, 9:39 am, Bob Flanagan wrote: Justin, the good news is what you want to do is very doable. You have done an excellent job of describing. The bad news is those who answer posts will seldom create large application answers like you described. If you divide your task up into many small tasks, like get sheets to compare, get cells to compare, how to compare two cells, how to loop through cells, how to edit paste the results, and even finer this allows you to tackle the pieces. And, when you get stuck, to post the code you have written so others can comment. Robert Flanagan Add-ins.com LLChttp://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel On Jun 6, 12:23 pm, Justin Pulley wrote: Well I have very little experience using VBA at all; I have a bit of C+ + and Java/HTML, but those are different. *If there is somewhere I can go to get an idea of the commands available in VBA I would be happy to try my hand at writing some of the code. *I might just have to bite the bullet and buy a VBA programming book, but I want to avoid that if possible because I am not being paid back for those kinds of things. IMO, if you're being paid to do VBA programming projects then you owe it to yourself to upgrade your employable skills on your own dime & time. Otherwise, doing the task manually while recording a macro is a good start toward getting exposure to VBA commands, and the objects Excel exposes to VBA. You can do this via Tools-Macros-Record Macro (pre-XL2007), or via the Developer tab in versions using the MSO Ribbon. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion- Hide quoted text - - Show quoted text - I'm not being paid for this, nor am I in a field that typically uses VBA coding. *I am, however, eventually going to be in upper management and this skill may prove useful. Best wishes in your endeavors... -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion- Hide quoted text - - Show quoted text - Thanks, so glad you could help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
Sub DataTransfer()
' ' DataTransfer Macro ' should show me how to copy and paste in a macro ' ' Keyboard Shortcut: Ctrl+Shift+T ' Dim strPosition As String Windows("Data").Activate With Range("A2:ZZ2") strCheck = .Find(what:="Current Month!H1", After:=.Cells(1, 1), LookIn:=xlValues, _ Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) End With If strCheck = True Then Sub CopyCells() Range("H5:H16").Select Selection.Copy Sheets("Data").Select Range("AE3").Select ActiveSheet.Paste Range("AE26").Select End Sub End Sub that is what I have so far and I don't even know if I am heading the right direction down this rabbit hole. I know there are a lot of gaps in that code that I have to fill, but I'd like to know if I am even going the right way first. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for checking data in a cell against another cell andcopying/pasting data
Not sure I understood it completely, but I think this code should do what I managed to understand:
sub DataTransfer() dim Month2 as Worksheets dim Month1 as Worksheets dim data as Worksheets dim ColOfInterest(3) as Integer dim dateHeader as Integer = 2 'Assuming your date is stored in ' row 2. Change to the row number of the date set Month2 = Worksheets("May") set Month1 = Worksheets("June") set data = worksheets("data") for i = 1 to 100 ColOfInterest(3) = i for j = 1 to 100 if ( data.Cells(dateHeader, ColOfInterest(3)) = _ Month1.Cells(dateHeader,j) ) then ColOfInterest(1) = j if ( data.Cells(dateHeader, ColOfInterest(3)) = _ Month2.Cells(dateHeader,j) ) then ColOfInterest(2) = j next j if j = 100 then goto skip_label for j = 5 to 22 if ( Month2.Cells(j,ColOfInterest(2)) _ Month1.Cells(j,ColOfInterest(1)) ) then data.Cells(j,ColOfInterest(3)) = _ Month1.Cells(j,ColOfInterest(1)) + Month2.Cells(j,ColOfInterest(2)) data.Cells(j,ColOfInterest(3)).font.color _ = RGB( 255, 0, 0 ) else data.Cells(j,ColOfInterest(3)) = _ Month1.Cells(j,ColOfInterest(1)) end if next j skip_label: next i end sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - Do not want to allow pasting into the cell | Excel Programming | |||
Why does data validation not work when pasting data into a cell. | Excel Discussion (Misc queries) | |||
How do I stop cell borders dissappearing when pasting data? | Excel Worksheet Functions | |||
Pasting data top 1st empty cell in range | Excel Discussion (Misc queries) | |||
Checking if a cell is filled up with data | Excel Discussion (Misc queries) |