I am hoping someone can help me come up with a way populate a table from a list of data automatically. Sounds easy I know.
I work for a construction company that has a number of different sites active at any one time. Once a quarter we compare the costs of the site against how we thought it would perform previously.
The first 6 tabs are sites. They contain a table with a number of column’s. I would like to fill the Column named “Cost to Date”.
In the next tab “Download of costs” is a download from our accounting system with the costs for the sites. The report has been pasted straight from our system and used the “text to columns” function in excel to make it legible. Other than that it is under editable which I imagine will make this task more difficult.
As you will be able to see I will want all of the costs in the “To Date” figures from the download to be populated into its corresponding cell on the site tabs. The left hand column (2000/2005/2010) etc is cost heads.
Eg: I would like code 2000 (Site Manager) for £1 to be put in to the first tab (Site 1) in the cost to date column to line up with code 2000 there.
In my example I have only changed the figures and site names to keep all our data secure and there are only 6 sites. In reality there are around 100 sites.
If this will involve VBA which I must add I have very little knowledge of, I will need a code that is simple, and can be edited easily as the list of sites grow etc.
Some one pleeeeease help me. This tasks takes hours and hours to complete manually and with the company growing it is only likely to become worse. I want to save time and eradicate the chances of human error..
Any more info needed let me know
Cheers
Chris
Last edited by chris-streeter; 09-30-2014 at 11:17 AM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Private Sub CommandButton1_Click()
Dim Sht As Worksheet
Dim Dic As Object
Dim Str As String
Dim nRng As Range
Dim Num As Integer
Dim Site As String
Dim R As Range
Dim Rng As Range
Dim Dn As Range
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
With Sheets("Download of Costs")
Set Rng = .Range(.Range("A10"), .Range("A" & Rows.Count).End(xlUp))
End With
For Each Dn In Rng
If Dn = "CONTRAC" Then
If Str = "" Then
Str = Str & "," & Dn.Address
Else
Str = Str & ":" & Dn.Offset(-1).Address & "," & Dn.Address
End If
End If
Next Dn
Str = Str & ":" & Range("A" & Rng.Count + 10).Address
Set nRng = Range(Mid(Str, 2))
For Each Dn In nRng.Areas
Num = InStr(Dn(1).Offset(, 1), "Site")
Site = Mid(Dn(1).Offset(, 1), Num, (Len(Trim(Dn(1).Offset(, 1))) - Num) - 1)
For Each R In Dn
If Application.IsNumber(R.Value) = True Then
If Not Dic.exists(Site) Then
Set Dic(Site) = CreateObject("Scripting.Dictionary")
End If
Dic(Site)(R.Value) = R.Offset(, 4)
End If
Next R
Next Dn
For Each Sht In Worksheets
If Not Sht.Name = "Download of Costs" Then
If Dic.exists(Sht.Name) Then
With Sheets(Sht.Name)
Set Rng = .Range(.Range("A10"), .Range("A" & Rows.Count).End(xlUp))
End With
For Each Dn In Rng
If Dic(Sht.Name).exists(Dn.Value) Then
Dn.Offset(, 3).Value = Dic(Sht.Name).Item(Dn.Value)
End If
Next Dn
End If
End If
Next Sht
MsgBox "Run"
End Sub
[PHP]
[/PHP]
Hi this is great! Exactly what I am looking for.
Is there anyway you can split the code up into sections and put something brief to describe what each part of the code is doing.. i.e top section is looking through searching for site name, next section bla bla bla..hopefully I will be able to understand it then and edit it slightly, as this needs to be adjusted to search through multiple tabs (which are sites).
I can do that if you think it will help !!!, but the code should already search through all the sheets and alter them if the name of the sheet is in sheet "Download of costs" column "B" next to word "CONTRAC". (NB:- the Site name being in that string)
Regrds Mick
PS :- Have you Checked the results to see their correctness?????
if you don't mind I think it will help yes. Mainly because it just got stuck when I tried it with real data.
I was hoping that along with a little research and my boss, who knows a lot more about VBA we will be able to fine tune it so I don't have to keep pestering you.
And yes I checked the result very good indeed!
Thanks
Chris
Last edited by chris-streeter; 09-30-2014 at 11:19 AM.
Thank you for the File.
I think the main problem is the "Sheet Names" in relation to the String of words that are suppose to relate to those names in Column "B" of "Download of Cost".
Below is a list oF the sheets Names and Next to them a list of the Related sheet names from column "B". In between them are the names that appears to have show some relationship.
As you will see this relationship appears a bit tenuous.
If you can come up with a more specific way to find the correct relationships, I think the code will work ???
I see the problem yes, some don't have much common ground.
Is it not possible for the code to search for the "Site Code number" I.E Crinnis Wood has a site number of C0064. There is a site number on all of the sheet names and the download of costs col b names?
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks