vba code to extract data from data table and update data sheet

Hi All

I have a workbook with table 1 and data sheet .

the data sheet requires an index and match from column I of data sheet to table 1 sheet column A.

extract data from column C, D,E,F to paste to data sheet from column AS TO AW.

however if data sheet column K has SADC IN ROW 2 then INSERT header SADC IN AX 1, then put Y if there are sadc reference and adjust column AT on data sheet to 0.

otherwise if there is no sadc then put the rate as from table 1 sheet to 15 to datasheet from column D and put N IN COLUMN AT

Note the datasheet goes to 5000 to 20000 rows doing this manually is a true pain

Hi Jean

Originally Posted by JEAN1972

otherwise if there is no sadc then put the rate as from table 1 sheet to 15 to datasheet from column D and put N IN COLUMN AT

Could you please clarify: is this data coming from 'Table 1'!column D, or from 'Table 1'!column N? Both have a value of 15.

Post #3 code and workbook updated to Final Versions 25 May 2017 12:50
I didn't get an answer to my question, so I set Column AT = corresponding 'Table 1' values (Column D) in rows where no SADC was found. Hope this suits-Lee

Sub DataSheetUpdate()

Dim SourceRange As Range
Dim DestinationRange As Range
Dim found As Range
Dim LookupRange As Range
Dim sLastRow As Long
Dim dLastRow As Long
Dim rw As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim SADCs As Boolean

Set WS1 = Worksheets("table 1")
Set WS2 = Worksheets("DATA SHEET")

With WS1
    Set SourceRange = .Columns("C:F")
    sLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set LookupRange = .Cells(2, "A").Resize(sLastRow - 1)
End With

With WS2
    Set DestinationRange = .Columns("AS:AV")
    dLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    Set found = .Columns("K").Find("SADC*(", LookIn:=xlFormulas, Lookat:=xlPart)
    If Not found Is Nothing Then
        .Cells(1, "AW").Resize(dLastRow).Copy
        .Cells(1, "AX").PasteSpecial xlPasteFormats
        .Cells(1, "AX") = "SADC"
        SADCs = True
        Application.CutCopyMode = False
    End If

For rw = 2 To dLastRow
    Set found = LookupRange.Find(.Cells(rw, "I"))
    If found Is Nothing Then
        DestinationRange.Rows(rw) = "XXX"
        Debug.Print "Tariff # not found on row " & rw
    Else
        DestinationRange.Rows(rw).Value = SourceRange.Rows(found.Row).Value
        .Cells(rw, "AW").Value = WS1.Cells(found.Row, "O")
        If SADCs Then
            On Error Resume Next
            If Left(.Cells(rw, "K"), 4) = "SADC" Then
                .Cells(rw, "AT") = 0
                .Cells(rw, "AX") = "Y"
            Else
                .Cells(rw, "AX") = "N"
            End If
            On Error GoTo 0
        End If
    End If
Next

End With

End Sub

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Yes, I corrected the difference between the 2 sheets. So, my code already does this:

.If DATA cell K2 has a SADC number, add a SADC column (AX). Formats for new column are copied from column AW.
.For each row in DATA, use the Tariff Number (cell I) to find the corresponding row in TABLE 1 (cell A).
.Copy cells C,D,E,F,O from TABLE 1 row to DATA row cells AS,AT,AU,AV,AW.
.If DATA row cell K has a SADC number, put "Y" in cell AX and CHANGE cell AT = 0...
....Else if cell K is blank (but SADCs are tracked), put "N" in cell AX. Cell AT RETAINS value from TABLE 1 row cell D.

Do I need to change anything? For example:
.Was I correct in assuming DATA column AW takes values from TABLE 1 column O? You mentioned AW, but not O.
.If SADCs are present, will row 2 ALWAYS have one, or should I just check entire column before looping through rows?
.What should happen if NO corresponding row is found in TABLE 1? (Current code puts "XXX" in DATA cells AS,AT,AU,AV)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee