Transform a Date and Concatenate the date

[gheyman]

I need to move data from one sheet to another but some of the data needs to be transformed when its moved

1) I have a table that has three columns (Source Type, Type, Subcatagory). The table name is "Source_Type" and its on a table named "Source Type"

For the code below where I have Type; what I need to do is take the value thats in Sheets("3 Enter Quote Data").Range("I12").Value and look up the value in the table. I12 is the Source Type. Can you lookup a value in VBA? I would like to have the lookup in the code.

2) For the Escalation Base Date
I need to convert the value that is Sheets("3 Enter Quote Data").Range("R12").Value which is a date to MM/yyyy
"Escalation Base Date", each Date.ToText([To Date],"MM/yyyy"))

3) For the Escalation Rate
I need to concatenate the value in Sheets("3 Enter Quote Data").Range("L7").Value the Year from the "To Date" which is the value in Sheets("3 Enter Quote Data").Range("I12").Value
[IHS_Code]&"-"&Number.ToText([Year])

Any help is very much appreciated

Sub AddtoTemplate1()

Dim TPLR As Long

  TPLR = Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Cost Sources").Range("A" & LR).Value = Sheets("3 Enter Quote Data").Range("F15").Value
Sheets("Cost Sources").Range("B" & LR).Value = "Part"
'Type
Sheets("Cost Sources").Range("C" & LR).Value = Sheets("3 Enter Quote Data").Range("I12").Value 'Need to lookup the value in table
Sheets("Cost Sources").Range("D" & LR).Value = Sheets("3 Enter Quote Data").Range("L5").Value
Sheets("Cost Sources").Range("E" & LR).Value = Sheets("3 Enter Quote Data").Range("P5").Value
Sheets("Cost Sources").Range("F" & LR).Value = Sheets("3 Enter Quote Data").Range("O12").Value
'To Date
Sheets("Cost Sources").Range("G" & LR).Value = Sheets("3 Enter Quote Data").Range("R12").Value
Sheets("Cost Sources").Range("H" & LR).Value = "(Common)"
Sheets("Cost Sources").Range("I" & LR).Value = Sheets("3 Enter Quote Data").Range("F5").Value
Sheets("Cost Sources").Range("J" & LR).Value = Sheets("3 Enter Quote Data").Range("F17").Value
Sheets("Cost Sources").Range("K" & LR).Value = Sheets("3 Enter Quote Data").Range("P17").Value
'  Escalation Base Date
'Sheets("Cost Sources").Range("M" & LR).Value =?
'  Escalation Rate
'Sheets("Cost Sources").Range("M" & LR).Value = ?


End Sub

Thank You!

 

[gheyman]

So far I figured out how to do the Escalation rate
But still dont know how to transform the date to MM/yyyy when its moved to Sheets("Cost Sources").Range("M" & LR).Value

Nor have I figured out the lookup.

Any help is appreciated

Here is how I solved the Escalation rate concatenation

Sub AddtoTemplate1()

Dim TPLR As Long
Dim ToDate As String
Dim RDate As String

  TPLR = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row + 1
  
  ToDate = Sheets("3 Enter Quote Data").Range("R12").Value
  RDate = Right(ToDate, 4)
  
Sheets("Cost Sources").Range("A" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F15").Value
Sheets("Cost Sources").Range("B" & TPLR).Value = "Part"
Sheets("Cost Sources").Range("C" & TPLR).Value = Sheets("3 Enter Quote Data").Range("I12").Value
Sheets("Cost Sources").Range("D" & TPLR).Value = Sheets("3 Enter Quote Data").Range("L5").Value
Sheets("Cost Sources").Range("E" & TPLR).Value = Sheets("3 Enter Quote Data").Range("P5").Value
Sheets("Cost Sources").Range("F" & TPLR).Value = Sheets("3 Enter Quote Data").Range("O12").Value
'To Date
Sheets("Cost Sources").Range("G" & TPLR).Value = Sheets("3 Enter Quote Data").Range("R12").Value
Sheets("Cost Sources").Range("H" & TPLR).Value = "(Common)"
Sheets("Cost Sources").Range("I" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F5").Value
Sheets("Cost Sources").Range("J" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F17").Value
Sheets("Cost Sources").Range("K" & TPLR).Value = Sheets("3 Enter Quote Data").Range("P17").Value
'Esclation Base date
Sheets("Cost Sources").Range("M" & TPLR).Value = Sheets("3 Enter Quote Data").Range("P17").Value
'Escalation Rate -SOLVED
Sheets("Cost Sources").Range("N" & TPLR).Value = Sheets("3 Enter Quote Data").Range("L17").Value & "-" & RDate

End Sub

 

[gheyman]

The VBA VLookup

Sub SimpleVLookupTable()

Dim sRes As Variant

' Get the table

Dim table As ListObject

Set table = Sheet3.ListObjects("Table1")

' Use the table for the table_array parameter

sRes = Application.VLookup(Sheets("IHS").Range("A1").Value, table.Range, 2, False)

End Sub

Sheets("IHS").Range("A1").Value is the lookup value

table.Range is where to look

2 is which column to return

 

[gheyman]

Sub AddToCostSourceTemplate()

Dim TPLR As Long
Dim ToDate As String
Dim RDate As String
Dim SumExcess As Double
Dim SumNRE As Double
Dim SumTariff As Double
Dim SourceType As Variant
Dim SourceSubG As Variant
Dim SourceTable As ListObject

  TPLR = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row + 1
  SumExcess = Application.WorksheetFunction.Sum(Sheet4.Range("I24:I56"))
  SumNRE = Application.WorksheetFunction.Sum(Sheet4.Range("J24:J56"))
  SumTariff = Application.WorksheetFunction.Sum(Sheet4.Range("K24:K56"))
  
  Set SourceTable = Sheet6.ListObjects("Source_Type")
  
  
  'Identify Current User
  CurrentUser = Environ("UserName")

  
  ToDate = Sheets("3 Enter Quote Data").Range("R12").Value
    RDate = Right(ToDate, 4)

'Material
Sheets("Cost Sources").Range("A" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F18").Value
'Material Type
Sheets("Cost Sources").Range("B" & TPLR).Value = "Part"
'Type
SourceType = Application.VLookup(Sheets("3 Enter Quote Data").Range("I12").Value, SourceTable.Range, 2, False)
    Sheets("Cost Sources").Range("C" & TPLR).Value = SourceType
'PP ID
Sheets("Cost Sources").Range("D" & TPLR).Value = Sheets("3 Enter Quote Data").Range("L5").Value
'PP Revision
Sheets("Cost Sources").Range("E" & TPLR).Value = Sheets("3 Enter Quote Data").Range("P5").Value
'From date
Sheets("Cost Sources").Range("F" & TPLR).Value = Sheets("3 Enter Quote Data").Range("O12").Value
'To Date
Sheets("Cost Sources").Range("G" & TPLR).Value = Sheets("3 Enter Quote Data").Range("R12").Value
'Library
Sheets("Cost Sources").Range("H" & TPLR).Value = "(Common)"
'Vendor
Sheets("Cost Sources").Range("I" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F5").Value
'LT
Sheets("Cost Sources").Range("J" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F20").Value
'Min Buy Qty
Sheets("Cost Sources").Range("K" & TPLR).Value = Sheets("3 Enter Quote Data").Range("P20").Value
'Esclation Base date
Sheets("Cost Sources").Range("M" & TPLR).Value = Sheets("3 Enter Quote Data").Range("S20").Value
'Escalation Rate -SOLVED
Sheets("Cost Sources").Range("N" & TPLR).Value = Sheets("3 Enter Quote Data").Range("L20").Value & "-" & RDate
'[MF] Subcatagory
SourceSubG = Application.VLookup(Sheets("3 Enter Quote Data").Range("I12").Value, SourceTable.Range, 3, False)
    Sheets("Cost Sources").Range("C" & TPLR).Value = SourceSubG
'[MF] Created by
Sheets("Cost Sources").Range("W" & TPLR).Value = CurrentUser
'[MF] Created
Sheets("Cost Sources").Range("X" & TPLR).Value = Date
'[MF] Vendor Quote ID
Sheets("Cost Sources").Range("AD" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F12").Value
'[MF] Path/Location
Sheets("Cost Sources").Range("AC" & TPLR).Value = Sheets("3 Enter Quote Data").Range("F12").Value
'Excess
If SumExcess > 0 Then
Sheets("Cost Sources").Range("Z" & TPLR).Value = "Yes"
Else
Sheets("Cost Sources").Range("Z" & TPLR).Value = "No"
End If

'NRe
If SumTariff > 0 Then
Sheets("Cost Sources").Range("AA" & TPLR).Value = "Yes"
Else
Sheets("Cost Sources").Range("AA" & TPLR).Value = "No"
End If

'Tariff
If SumNRE > 0 Then
Sheets("Cost Sources").Range("AB" & TPLR).Value = "Yes"
Else
Sheets("Cost Sources").Range("AB" & TPLR).Value = "No"
End If

'****************************
'Cost Source Details
'****************************

'***********************************
'NEED HELP WITH THE CODE FROM HERE DOWN
'***********************************

Dim CSDLR As Long

 CSDLR = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row + 1

'***********************************
'This data is repeated for each Row
'***********************************

'Material
Sheets("Cost Source Details").Range("A" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("F18").Value
'Material Type
Sheets("Cost Source Details").Range("B" & CSDLR).Value = "Part"
'Type
Sheets("Cost Source Details").Range("C" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("W3").Value
'PP ID
Sheets("Cost Source Details").Range("D" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L5").Value
'PP Revision
Sheets("Cost Source Details").Range("E" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("P5").Value

'*****************************************************************************************
'Loop through rows with the data from Rows 24 down to 56 if there is a value in cloumn F
'*****************************************************************************************

'From Qty
Sheets("Cost Source Details").Range("F" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("F24").Value
Sheets("Cost Source Details").Range("G" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("G24").Value
Sheets("Cost Source Details").Range("H" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("H24").Value

If Sheets("3 Enter Quote Data").Range("I24").Value > 0 And Sheets("3 Enter Quote Data").Range("J24").Value > 0 And Sheets("3 Enter Quote Data").Range("K24").Value > 0 Then
Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I24").Value & "} " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K24").Value & "}" & " {NRE: $" & Sheets("3 Enter Quote Data").Range("J24").Value & "}"

Else

If Sheets("3 Enter Quote Data").Range("J24").Value > 0 And Sheets("3 Enter Quote Data").Range("I24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I24").Value & "} " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("J24").Value & "}"

Else

If Sheets("3 Enter Quote Data").Range("J24").Value > 0 And Sheets("3 Enter Quote Data").Range("K24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I24").Value & "} " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("J24").Value & "}"

Else
If Sheets("3 Enter Quote Data").Range("I24").Value > 0 And Sheets("3 Enter Quote Data").Range("K24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I24").Value & "} " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K24").Value & "}"

Else
If Sheets("3 Enter Quote Data").Range("I24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{EXCESS: $" & Sheets("3 Enter Quote Data").Range("I24").Value & "}"

Else
If Sheets("3 Enter Quote Data").Range("K24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{NRE: $" & Sheets("3 Enter Quote Data").Range("K24").Value & "}"

Else
If Sheets("3 Enter Quote Data").Range("J24").Value > 0 Then
    Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value & " " & "{TARIFF: $" & Sheets("3 Enter Quote Data").Range("K24").Value & "}"

Else

 Sheets("Cost Source Details").Range("I" & CSDLR).Value = Sheets("3 Enter Quote Data").Range("L24").Value


End If
End If
End If
End If
End If
End If
End If



End Sub