Scribd
Upload
42 views10 pages

EW VBscript Rev01

This macro copies cell values from one sheet to another, applies filters, copies and pastes formulas across ranges, and formats cells based on text values. Specifically, it 1) copies data between sheets; 2) filters data based on criteria; and 3) copies formulas down columns to analyze vibration data.

Uploaded by

Muhammad Fahmmi Bin Mahmud
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
42 views10 pages

EW VBscript Rev01

This macro copies cell values from one sheet to another, applies filters, copies and pastes formulas across ranges, and formats cells based on text values. Specifically, it 1) copies data between sheets; 2) filters data based on criteria; and 3) copies formulas down columns to analyze vibration data.

Uploaded by

Muhammad Fahmmi Bin Mahmud
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Sub addd()

'
' addd Macro
'

'
Sheets("Coding").Select
Range("D6").Select
[Link]
Sheets("VibesReadings").Select
Range("D139").Select
[Link]
Range("B139").Select
End Sub
Sub Button_Evaluate_1()
'
' Button_Evaluate_1 Macro
'

'
Columns("B:Z").Select
[Link] = False
[Link]
Range("H1").Select
[Link]("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="mm/Sec"
Sheets("Coding").Select
Range("D76").Select
[Link]
Sheets("VibesReadings").Select
Range("D1").Select
[Link]
Range("D1").Select
[Link] = False
[Link]
Range("D1:D3002").Select
[Link]
Range("B1").Select
[Link]("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"E1A", "E1H", "E1V", "E2A", "E2H", "E2V"), Operator:=xlFilterValues
Range("D1").Select
[Link] = False
[Link] = 2
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<3,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>12,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>6,""ALARM1"",""OK"")))))))"
Range("D1").Select
[Link]
Range("D1:D3002").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"A1A", "A1H", "A1V", "A2A", "A2H", "A2V"), Operator:=xlFilterValues
Range("D1").Select
[Link] = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<3,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>11,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>5,""ALARM1"",""OK"")))))))"
Range("D1").Select
[Link]
Range("D1:D3002").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"G1A", "G1H", "G1V", "G2A", "G2H", "G2V", "G2X", "G2Y", "G3H", "G3V"),
Operator:= _
xlFilterValues
Range("D1").Select
[Link] = False
[Link]
Range("D1:D3002").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=1
[Link]("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="G-s"
Range("D1").Select
[Link] = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<0.2,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>2,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>1,""ALARM1"",""OK"")))))))"
Range("D1").Select
[Link]
Range("D1:D3002").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="Microns"
[Link] = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<10,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>50,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>40,""ALARM1"",""OK"")))))))"
Range("D1").Select
[Link]
Range("D1:D3002").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=7
Columns("D:D").Select
[Link] = False
[Link]
Columns("F:F").Select
[Link]
Range("B1").Select
'
' Button_Evaluate_7 Macro
'

'
[Link] Reference:="R1C7"
Range("G1:M3002").Select
[Link] What:="(", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
[Link] What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
[Link] Down:=-78
[Link] Reference:="R1C5"
Range("G1:M3002").Select
[Link] What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[Link]("$B$1:$Z$1150").AutoFilter Field:=2, Criteria1:="-"
Range("N1").Select
ActiveCell.FormulaR1C1 = "=MAXA(RC[-6]:RC[-1])"
Columns("N:N").Select
[Link] = "[$-en-MY,1]d/m/yy;@"
Columns("H:M").Select
Range("N1").Select
[Link]
Range("N1:N3001").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=2
Range("N1").Select
[Link]("$B$1:$Z$3001").AutoFilter Field:=13, Criteria1:="="
Range("N2").Select
[Link] = False
[Link] = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("N2").Select
[Link]
Range("N2:N3001").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=13
Range("B1").Select
'
' Button_Evaluate_4 Macro
'
Columns("D:D").Select
[Link] Type:=xlTextString, String:=">100%", _
TextOperator:=xlContains
[Link]([Link]).SetFirstPriority
With [Link](1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With [Link](1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
[Link](1).StopIfTrue = False
[Link] Type:=xlTextString, String:=">50%", _
TextOperator:=xlContains
[Link]([Link]).SetFirstPriority
With [Link](1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With [Link](1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
[Link](1).StopIfTrue = False
Range("B1").Select
'
' Button_Evaluate_3 Macro
'
[Link]("$B$1:$Z$3001").AutoFilter Field:=2, Criteria1:="-"
Sheets("Coding").Select
Range("D1").Select
[Link]
Sheets("VibesReadings").Select
Range("D15").Select
[Link]
Sheets("Coding").Select
Range("D2").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D46").Select
[Link]
Sheets("Coding").Select
Range("D3").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D66").Select
[Link]
Sheets("Coding").Select
Range("D4").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D77").Select
[Link]
Sheets("Coding").Select
Range("D5").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D88").Select
[Link]
Sheets("Coding").Select
Range("D6").Select
[Link]
Sheets("VibesReadings").Select
Range("D99").Select
[Link]
Sheets("Coding").Select
Range("D7").Select
[Link]
Sheets("VibesReadings").Select
Range("D110").Select
[Link]
Sheets("Coding").Select
Range("D8").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D121").Select
[Link]
Sheets("Coding").Select
Range("D9").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D132").Select
[Link]
Sheets("Coding").Select
Range("D10").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D164").Select
[Link]
Sheets("Coding").Select
Range("D11").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D196").Select
[Link]
Sheets("Coding").Select
Range("D12").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D232").Select
[Link]
Sheets("Coding").Select
Range("D13").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D268").Select
[Link]
Sheets("Coding").Select
Range("D14").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D304").Select
[Link]
Sheets("Coding").Select
Range("D15").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D340").Select
[Link]
Sheets("Coding").Select
Range("D16").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D362").Select
[Link]
Sheets("Coding").Select
Range("D17").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D384").Select
[Link]
Sheets("Coding").Select
Range("D18").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D406").Select
[Link]
Sheets("Coding").Select
Range("D19").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D423").Select
[Link]
Sheets("Coding").Select
Range("D20").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D440").Select
[Link]
Sheets("Coding").Select
Range("D21").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D457").Select
[Link]
Sheets("Coding").Select
Range("D22").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D468").Select
[Link]
Sheets("Coding").Select
Range("D23").Select
[Link]
Sheets("VibesReadings").Select
Range("D479").Select
[Link]
Sheets("Coding").Select
Range("D24").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D490").Select
[Link]
Sheets("Coding").Select
Range("D25").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D512").Select
[Link]
Sheets("Coding").Select
Range("D26").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D534").Select
[Link]
Sheets("Coding").Select
Range("D27").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D580").Select
[Link]
Sheets("Coding").Select
Range("D28").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D600").Select
[Link]
Sheets("Coding").Select
Range("D29").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D620").Select
[Link]
Sheets("Coding").Select
Range("D30").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D666").Select
[Link]
Sheets("Coding").Select
Range("D31").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D686").Select
[Link]
Sheets("Coding").Select
Range("D32").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D706").Select
[Link]
Sheets("Coding").Select
Range("D33").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D750").Select
[Link]
Sheets("Coding").Select
Range("D34").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D770").Select
[Link]
Sheets("Coding").Select
Range("D35").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D790").Select
[Link]
Sheets("Coding").Select
Range("D36").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D812").Select
[Link]
Sheets("Coding").Select
Range("D37").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D834").Select
[Link]
Sheets("Coding").Select
Range("D38").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D856").Select
[Link]
Sheets("Coding").Select
Range("D39").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D878").Select
[Link]
Sheets("Coding").Select
Range("D40").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D900").Select
[Link]
Sheets("Coding").Select
Range("D41").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D922").Select
[Link]
Sheets("Coding").Select
Range("D42").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D946").Select
[Link]
Sheets("Coding").Select
Range("D43").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D970").Select
[Link]
Sheets("Coding").Select
Range("D44").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D999").Select
[Link]
Sheets("Coding").Select
Range("D45").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1050").Select
[Link]
Sheets("Coding").Select
Range("D46").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1101").Select
[Link]
Sheets("Coding").Select
Range("D47").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1152").Select
[Link]
Sheets("Coding").Select
Range("D48").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1203").Select
[Link]
Sheets("Coding").Select
Range("D49").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1225").Select
[Link]
Sheets("Coding").Select
Range("D50").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1247").Select
[Link]
Sheets("Coding").Select
Range("D51").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1285").Select
[Link]
Sheets("Coding").Select
Range("D52").Select
[Link] = False
[Link]
Sheets("VibesReadings").Select
Range("D1307").Select
[Link]
[Link]("$B$1:$Z$3001").AutoFilter Field:=13
Range("B1").Select
Range("D1:D3001").Select
[Link] = False
[Link] Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""H"""
[Link]([Link]).SetFirstPriority
With [Link](1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With [Link](1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
[Link](1).StopIfTrue = False
[Link] Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""M"""
[Link]([Link]).SetFirstPriority
With [Link](1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With [Link](1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
[Link](1).StopIfTrue = False
[Link] Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""L"""
[Link]([Link]).SetFirstPriority
With [Link](1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With [Link](1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
[Link](1).StopIfTrue = False
Range("B1").Select
End Sub

You might also like