1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| Sub Test()
Dim TS, CP, DS, DA, DR, VD As Range
Dim RPP, rngPP, PP As String
Dim LRPP As Long
Sheets("LAPS").Select
Set TS = Rows(1).Find(what:="TS", LookIn:=xlValues, lookat:=xlWhole)
Set CP = Rows(1).Find(what:="CP", LookIn:=xlValues, lookat:=xlWhole)
Set DS = Rows(1).Find(what:="DS", LookIn:=xlValues, lookat:=xlWhole)
Set DA = Rows(1).Find(what:="DA", LookIn:=xlValues, lookat:=xlWhole)
Set DR = Rows(1).Find(what:="DR", LookIn:=xlValues, lookat:=xlWhole)
Set VD = Rows(1).Find(what:="VD", LookIn:=xlValues, lookat:=xlWhole)
If TS Is Nothing Then MsgBox "TS Column Not Found"
If CP Is Nothing Then MsgBox "CP Column Not Found"
If DS Is Nothing Then MsgBox "DS Column Not Found"
If DA Is Nothing Then MsgBox "DA Column Not Found"
If DR Is Nothing Then MsgBox "DR Column Not Found"
If VD Is Nothing Then MsgBox "DV Column Not Found"
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "KEY"
ActiveCell.Offset(1, 0).Select
PP = "&"",""&"
ActiveCell.Value = "=" & TS.Offset(1, 0).Address(False, False) & PP & CP.Offset(1, 0).Address(False, False) & _
PP & DS.Offset(1, 0).Address(False, False) & PP & DA.Offset(1, 0).Address(False, False) & _
PP & DR.Offset(1, 0).Address(False, False)
RPP = Split(ActiveCell.Address, "$")(1)
rngPP = (":" & RPP)
LRPP = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range(RPP & ActiveCell.Row & rngPP & LRPP) |
Partager