PDA

View Full Version : Solved: 12 month Update Code



paddysheeran
02-21-2009, 07:27 PM
Hi all,

I've come up against a problem where I dont really know where to start with the coding.

I've just used animal names in the spreadsheet attached. I need a piece of code that will:

Check the first animal name on the current_month sheet against the animals in the 12_month sheet. If the animal appears in the 12_month sheet then the corresponding cell in column M (Feb-10 in the example) will update with the total from the current_month sheet. If the Animal doesn't appear in the 12_month sheet then it will be added into the first available row in column A and the corresponding cell in column M will be updated.

Once the part of the process has completed I would repeat the process for the second animal, repeated until all the animal totals in the current_month sheet have been updated into the 12_month sheet.

If anyone can help or point me in the right direction that would be great.

mdmackillop
02-22-2009, 05:13 AM
Hi Paddy,
Welcome to VBAX.
Give this a try
Option Explicit
Sub Animals()
Dim Dte As Date, cel As Range, Rng As Range, a As Range
Dim t As Range
Dim CM As Worksheet, M12 As Worksheet, Col As Long

Set CM = Sheets("current_month")
Set M12 = Sheets("12_month")

'Get date; find corresponding column
Dte = CDate("1/" & Month(Date) & "/" & Year(Date) + 1)
Col = M12.Rows(1).Find(Dte).Column - 1

'Lopop through cells; find location on column1
For Each a In Range(CM.Cells(2, 1), CM.Cells(Rows.Count, 1).End(xlUp))
Set t = M12.Columns(1).Find(a, lookat:=xlWhole)
'Write values to sheet
If Not t Is Nothing Then
t.Offset(, Col) = a.Offset(, 1)
Else
With M12.Cells(Rows.Count, 1).End(xlUp)
.Offset(1) = a
.Offset(1, Col) = a.Offset(, 1)
End With
End If
Next
End Sub

paddysheeran
02-22-2009, 02:06 PM
Thanks mate this seems to have worked a treat. Is there any chance (if you have some spare that is) to provide a few more comment lines within the code to explain the different stages of the process. Im trying to learn vba at the moment and always work best when directed through an example. I appreciate all your help with this.

mdmackillop
02-22-2009, 02:18 PM
Option Explicit
Sub Animals()
Dim Dte As Date, cel As Range, Rng As Range, a As Range
Dim t As Range
Dim CM As Worksheet, M12 As Worksheet, Col As Long

'Set variables for each sheet
Set CM = Sheets("current_month")
Set M12 = Sheets("12_month")

'Create date for 1st of current month for next year
Dte = CDate("1/" & Month(Date) & "/" & Year(Date) + 1)
'Search Row 1 of 12 Month to locate correct date column
Col = M12.Rows(1).Find(Dte).Column - 1

'Loop through cells from 2 to last cell
For Each a In Range(CM.Cells(2, 1), CM.Cells(Rows.Count, 1).End(xlUp))
'Search for animal; set variable to found cell (if any)
Set t = M12.Columns(1).Find(a, lookat:=xlWhole)
'Write values to sheet if animal is found
If Not t Is Nothing Then
t.Offset(, Col) = a.Offset(, 1)
Else
'Write new values to sheet if animal is not found
'Find position of last cell
With M12.Cells(Rows.Count, 1).End(xlUp)
'Put data in next row
.Offset(1) = a
.Offset(1, Col) = a.Offset(, 1)
End With
End If
Next
End Sub

paddysheeran
02-22-2009, 02:21 PM
Hi,

just had a run through the code and noticed one problem. When I update the table to start the run on the next month, Feb10 gets popoulated again rather than column M (now Mar10) being populated.

mdmackillop
02-22-2009, 02:28 PM
When posters use terms like current month, I tend to take them literally. Please be clear in your question exactly what you are after. If you want to specify the month, add an Inputbox to get user input for the date.

paddysheeran
02-23-2009, 02:08 AM
Hi there, sorry for the confusion. the column header with the new month will be already updated in the 12 month sheet before the code to populate the column is run. I also have weekly updates that I intend to use the code for so just need the process to populate column M or a different column dependant on the table being populated in other spreadsheets. hope this makes sense.

mdmackillop
02-23-2009, 11:38 AM
As I said, use an InputBox in this line

Dte = CDate("1/" & Month(Date) & "/" & Year(Date) + 1)


You need to decide where the results are to go and how you want to reference/find the location.

paddysheeran
02-25-2009, 05:47 AM
Hi all sorted now I just referenced the column and cell. thanks for all your help on this.

paddysheeran
04-17-2009, 09:42 AM
HI there, i've tried to alter this code to fit the needs of a recent piece of work. Unfortunately I get an error message on the Col=M12 row.

The error message is:

Run-time error '91':

Object or With block variable not set. Can anyone help?

Sub Cause_Item()
Dim Dte As Range, cel As Range, Rng As Range, a As Range
Dim t As Range
Dim CM As Worksheet, M12 As Worksheet, Col As Long

Dim CECtemplate As Workbooks, CecCalc As Workbooks

Set CM = Application.Workbooks("CEC_Calculations").Worksheets("Count of Cause Item")
Set M12 = Application.Workbooks("CEC Weekly Performance Report -Template").Worksheets("Cause Item")


'Get date; find corresponding column
Set Dte = M12.Range("g5")
Col = M12.Rows(1).Find(Dte).Column - 1

'Lopop through cells; find location on column1
For Each a In Range(CM.Cells(5, 1), CM.Cells(Rows.Count, 1).End(xlUp))
Set t = M12.Columns(1).Find(a, lookat:=xlWhole)
'Write values to sheet
If Not t Is Nothing Then
t.Offset(, Col) = a.Offset(, 1)
Else
With M12.Cells(Rows.Count, 1).End(xlUp)
.Offset(1) = a
.Offset(1, Col) = a.Offset(, 1)
End With
End If
Next
End Sub

mdmackillop
04-17-2009, 09:53 AM
What line gives you the error?

paddysheeran
04-17-2009, 09:56 AM
Col = M12.Rows(1).Find(Dte).Column - 1

mdmackillop
04-17-2009, 10:01 AM
Finding dates is fiddly. Can you post the relevant sheet?

paddysheeran
04-20-2009, 03:35 AM
attached are the files used:

file 1:

paddysheeran
04-20-2009, 03:36 AM
Second File:

paddysheeran
04-21-2009, 09:36 AM
any luck with this?

mdmackillop
04-21-2009, 11:03 AM
In this line,
Col = M12.Rows(1).Find(Dte).Column - 1

there are no dates to be found. Can you repeat your question with regard to the revised sample posted?

paddysheeran
04-22-2009, 02:36 AM
The process is as follows:

The Date refers to Cell G5 (date header). G6 downwards is the column to be updated.

It is updated from the CEC calculatons Count of Cause Item worksheet.

If any of the categories are not present in column B6 downwards then they are added to the bottom of the column and their value entered into the corresponding cell in column G

paddysheeran
04-23-2009, 09:09 AM
hi all - anyone have any idea with this?

mdmackillop
04-23-2009, 10:18 AM
Option Explicit
Sub Updates()
Dim CI As Range
Dim cel As Range
Dim C As Range, D As Long
Dim ws As Worksheet
Dim wsSource As Worksheet
Dim Dte As Date
Dte = CDate(InputBox("Enter date"))
Set wsSource = Workbooks("CEC_Calculations").Sheets("Count of Cause Item")
Set ws = Workbooks("CEC Weekly Performance Report -Template").Sheets("Cause Item")
D = ws.Rows(5).Find(Dte, LookIn:=xlFormulas).Column
Set CI = Range(wsSource.Range("A5"), wsSource.Range("A5").End(xlDown).Offset(-1))
For Each cel In CI
Set C = ws.Columns(2).Find(cel)
If C Is Nothing Then
With ws.Cells(Rows.Count, 2).End(xlUp)
.EntireRow.Insert
.Offset(-1) = cel
.Offset(-1, D - 2) = cel.Offset(, 1)
End With
Else
C.Offset(, D - 2) = cel.Offset(, 1)
End If
Next
ws.Cells(Rows.Count, D - 1).End(xlUp).Offset(, 1) = wsSource.Cells(Rows.Count, 2).End(xlUp)
End Sub

paddysheeran
04-24-2009, 04:04 AM
Thats great - thanks very much - I just need one line amending - The date header will always be in Range("G5") of the ws worksheet therefore I dont need an input box

I tried to change the codes from :

Dte = CDate(InputBox("Enter date"))
Set wsSource = Workbooks("CEC_Calculations").Sheets("Count of Cause Item")
Set ws = Workbooks("CEC Weekly Performance Report -Template").Sheets("Cause Item")
D = ws.Rows(5).Find(Dte, LookIn:=xlFormulas).Column


to this

Set month = ws.Range("G5")
D = ws.Rows(5).Find(month, LookIn:=xlFormulas).Column

But am now getting an error message

mdmackillop
04-24-2009, 04:26 AM
try Month = cdate(ws.Range("G5"))
or
Month = ws.Range("G5")