Populate Spreadsheet from SQL database?

mightymike · Feb 15, 2012

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot]Could anybody offer any advice/pointers on the best way forward?

I have an excel workbook which is basically a P&L, each department has its own worksheet.

Each sheet is laid out as follows, columns as months (12 of course) and rows as individual items/nominal codes; of course there are totals here and there. Currently I have to manually update this workbook each month with the actual results and it is very time consuming.

Is it possible to have some sort of SQL query which could populate it automatically?

I have nominal codes listed down the side of each spreadsheet so it could use those in the SQL query to find the actual number for the period. And I thought I could name the columns as named ranges i.e. column C is Jan or period 1 and this would tell it which column to put the result in.

Any advice or pointers would be great, I am just trying to work out how best I should go about this.

Thanks in advance

Mike

[/FONT]

RoryA · Feb 15, 2012

You could probably produce a summary table from all the SQL data and load that into a worksheet (using the Data menu) and then use lookups to populate your current table.

mightymike · Feb 15, 2012

Thanks Rory,

I was doing something similar previously. I would create a pivot table for each department and then use lookups to populate the worksheets. It was not perfect but it kind of worked, I did hit a problem where it would not populate some rows which I could never work out why.

I thought maybe using a SQL query to find and store the results would be a better and quicker option. Then using the stored data (array is it?) to populate the spreadsheet, without all the need to create new spreadsheets and use lookups.
[FONT=&quot]
Maybe I am wrong and what I was doing was/is the best way?[/FONT]

RoryA · Feb 15, 2012

If you do one query per line, it will probably be less efficient than just querying all the data once, dumping it into a worksheet and then looking up the values. You shouldn't really need a pivot table if you write a SQL query that summarises the data - and that should be easier to lookup from.

mightymike · Feb 15, 2012

Good point about the efficiency. It never really occured to me about just dumping the data into a usable format into a worksheet and working from there.

It was a few years ago I went the pivot table route and sql/vba was not my best subjects then.

Time to hit the spreadsheets, I am sure I will hit a hurdle soon enough and be back on asking for help

mightymike · Feb 20, 2012

I have almost got this working but hit a problem, could anybody look at the below code and see what/where I am going wrong?

Code:

Sub get_SQL_data()

Dim Range_Lookup As Range
Dim DepRange As Range
Dim wb As Workbook
Dim QT As QueryTable
Dim findstring As String
Dim Found_Value As Long
Dim Department_Code As String
Dim Fill_Range As Range
Dim DEPWS As Worksheet

' Source Data Sheet
Sheets.Add
ActiveSheet.Name = "Monthly Report"
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Server;Description=Database;UID=Name;APP=2007 Microsoft Office system;WSID=MY-PC;DATABASE=To" _
        ), Array("pLayer;Trusted_Connection=Yes")), Destination:=Range("$A$1")). _
        QueryTable
        .CommandText = Array( _
        "SELECT Statement....)
        .RowNumbers = False
        .ListObject.DisplayName = "SQL_Data"
        .Refresh BackgroundQuery:=False
    End With
    
' Set the range for the lookup
Set Range_Lookup = Workbooks("New Monthly Report.xlsm").Worksheets("Monthly Report").Range("SQL_Data")

' Open the workbook to be updated
Set wb = Workbooks.Open("Jan-12 v1.0.xlsx", False, False)

' for each department cell in the source data sheet
For Each depcell In Workbooks("New Monthly Report.xlsm").Worksheets("Monthly Report").Range("SQL_Data").Columns(2).Cells

Department_Code = depcell.Value

MsgBox Department_Code ' testing

' set the worksheet and range to be updated
Select Case Department_Code
            Case 210
                Set DEPWS = Worksheets("ProdMgt")
                Set DepRange = Range("Prod_2012_1")
            More Case.....
End Select

MsgBox DEPWS.Name ' testing
MsgBox DepRange.Name ' testing

' activate the worksheet to be updated
DEPWS.Activate ' testing

' set the range on the worksheet that needs to be updated
Set Fill_Range = Workbooks("Jan-12 v1.0.xlsx").Worksheets(DEPWS.Name).Range(DepRange.Name)

MsgBox Fill_Range.Name ' testing

For Each fillcell In Fill_Range.Cells

fillcell.Activate ' testing

findstring = Cells(fillcell.Row, 1) & Cells(fillcell.Column, 1)

        On Error Resume Next
        Err.Clear

If findstring <> "" Then
    Found_Value = Application.WorksheetFunction.VLookup(findstring, Range_Lookup, 3, False)
    If Err.Number = 0 Then
        fillcell.Value = Found_Value
    Else
        fillcell.Value = ""
    End If
End If

Next fillcell

Set DEPWS = Nothing ' Free memory
Set DepRange = Nothing ' Free memory
Set Fill_Range = Nothing ' Free memory


Next depcell

Set Range_Lookup = Nothing ' Free memory
Set wb = Nothing ' Free memory

'Sheets("Monthly Report").Delete

End Sub

I think I may have gone about it completly wrong as the end result is not working

Once it gets to the second for each statement (fillcell) it just ignores where it was on the first foreach depcell and runs through the whole source range looking for values and places them in the wrong sheets

Any help greatly appreciated.

Thaks

RoryA · Feb 20, 2012

You don't appear to be using most of the object references you create:

Code:

Sub get_SQL_data()

    Dim Range_Lookup As Range
    Dim DepRange As Range
    Dim wb As Workbook
    Dim QT As QueryTable
    Dim findstring As String
    Dim Found_Value As Long
    Dim Department_Code As String
    Dim Fill_Range As Range
    Dim DEPWS As Worksheet
    Dim wsReport As Worksheet

    ' Source Data Sheet
    Set wsReport = Sheets.Add
    With wsReport
        .Name = "Monthly Report"

        With .ListObjects.Add(SourceType:=0, _
                    Source:=Array(Array("ODBC;DSN=Server;Description=Database;UID=Name;APP=2007 Microsoft Office system;WSID=MY-PC;DATABASE=To"), _
                    Array("pLayer;Trusted_Connection=Yes")), Destination:=.Range("$A$1")).QueryTable
            .CommandText = Array("SELECT Statement....")
            .RowNumbers = False
            .ListObject.DisplayName = "SQL_Data"
            .Refresh BackgroundQuery:=False
        End With

        ' Set the range for the lookup
        Set Range_Lookup = .Range("SQL_Data")
    End With
    ' Open the workbook to be updated
    Set wb = Workbooks.Open("Jan-12 v1.0.xlsx", False, False)

    ' for each department cell in the source data sheet
    For Each depcell In Range_Lookup.Columns(2).Cells

        Department_Code = depcell.Value

        MsgBox Department_Code    ' testing

        ' set the worksheet and range to be updated
        Select Case Department_Code
        Case 210
            Set DEPWS = Worksheets("ProdMgt")
            Set DepRange = DEPWS.Range("Prod_2012_1")
            'More Case.....
        End Select

        MsgBox DEPWS.Name    ' testing
        MsgBox DepRange.Name    ' testing

        ' activate the worksheet to be updated
        With DEPWS    ' testing

            ' set the range on the worksheet that needs to be updated

            MsgBox Fill_Range.Name    ' testing

            For Each fillcell In DepRange.Cells

                fillcell.Activate    ' testing

                findstring = .Cells(fillcell.Row, 1) & .Cells(fillcell.Column, 1)

                On Error Resume Next
                Err.Clear

                If findstring <> "" Then
                    Found_Value = Application.WorksheetFunction.VLookup(findstring, Range_Lookup, 3, False)
                    If Err.Number = 0 Then
                        fillcell.Value = Found_Value
                    Else
                        fillcell.Value = ""
                    End If
                End If

            Next fillcell
        End With

        Set DEPWS = Nothing    ' Free memory
        Set DepRange = Nothing    ' Free memory
        Set Fill_Range = Nothing    ' Free memory


    Next depcell

    Set Range_Lookup = Nothing    ' Free memory
    Set wb = Nothing    ' Free memory

    'Sheets("Monthly Report").Delete

End Sub

mightymike · Feb 20, 2012

Thanks Rory,

I have removed the object references not being used.

That code looks alot more refined and better than my poor attempt, but, I am still having the problem of it not updating the sheets correctly.

<table style="border-collapse: collapse; width: 154pt;" border="0" cellpadding="0" cellspacing="0" width="205"><col style="width: 45pt;" width="60"> <col style="width: 62pt;" width="82"> <col style="width: 47pt;" width="63"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 45pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" height="20" width="60">Nominal</td> <td style="width: 62pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="82">Department</td> <td style="width: 47pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="63">Value</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">210</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">8250</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">220</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">6916.67</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">253</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">6881.63</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">254</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">16856.22</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">255</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">14366.04</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">256</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">13935.03</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">257</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">9008.98</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">259</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">9489.55</td> </tr> </tbody></table>
That is what the source data table looks like once created, each department has a seperate sheet in the workbook that needs updating. So, my thinking is, it should pick the first row, set the sheet name and range to be filled and then find the lookup_value(nominal) and place the value where required. Then move on to the next row?

RoryA · Feb 20, 2012

That looks, at first glance, to be what it is doing. In what way is it not working?

mightymike · Feb 20, 2012

<table style="border-collapse: collapse; width: 154pt;" border="0" cellpadding="0" cellspacing="0" width="205"><col style="width: 45pt;" width="60"> <col style="width: 62pt;" width="82"> <col style="width: 47pt;" width="63"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 45pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" height="20" width="60">Nominal</td> <td style="width: 62pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="82">Department</td> <td style="width: 47pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 1.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(79, 129, 189);" width="63">Value</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">210</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">8250</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">220</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">6916.67</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">253</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">6881.63</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">254</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">16856.22</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">255</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">14366.04</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">256</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">13935.03</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">257</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">9008.98</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5000</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">259</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">9489.55</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5050</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">253</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">455.07</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5050</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">254</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">218.81</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5050</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">255</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">4298.19</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5050</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">256</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">121.23</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20">5050</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);">259</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">22.48</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20">5100</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);">210</td> <td style="font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(219, 229, 241);" align="right">1057.21</td> </tr> </tbody></table>
I have extended the source table out a bit more. It is not switching between sheets to place the values, once it gets to the second for each statement

Code:

For Each fillcell In DepRange.Cells

it just continues looking for matches in the source data ignoring what department and sheet it should be on.

It finds department 210, sets the sheet name and range and goes looking for a match on 5000 lookup_value which it finds and places correctly, it must ignore the rest as it does nothing. It then finds a match on 5050 lookup_value but ignores the fact that it is for a different department so should be on a different sheet. Do you need to see the layout of the destination sheets?

Cheers

Mike

Populate Spreadsheet from SQL database?

mightymike

Board Regular

RoryA

MrExcel MVP, Moderator

mightymike

Board Regular

RoryA

MrExcel MVP, Moderator

mightymike

Board Regular

mightymike

Board Regular

RoryA

MrExcel MVP, Moderator

mightymike

Board Regular

RoryA

MrExcel MVP, Moderator

mightymike

Board Regular

Similar threads

Share this page

Populate Spreadsheet from SQL database?

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock