Macro for checking data in a cell against another cell andcopying/pasting data

  • Thread starter Thread starter Justin Pulley
  • Start date Start date
J

Justin Pulley

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)
this allows
the first page to show the proper amount of usage.
 
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
 
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 <[email protected]> wrote:
Click to expand...

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.
 
Justin Pulley wrote on 6/7/2012 :
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.
Click to expand...

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
 
Justin Pulley wrote on 6/7/2012 :







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 -
Click to expand...

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.
 
Justin Pulley explained on 6/7/2012 :
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.
Click to expand...

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
 
Justin Pulley explained on 6/7/2012 :







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 -
Click to expand...

Thanks, so glad you could help.
 
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.
 
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
 
Back
Top