Sub Calcs()
Dim Input2 As Range
Dim Input_Data As Range
Dim Colours As Range
Dim Output As Range
Dim Count_Ref As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim OutputRow As Integer
Dim Colour As String
Dim Foreground As String
Dim Text As String
Dim Val_1 As String
Dim Val_2 As String
Dim Val_3 As String
Dim Ref As String
Dim Name As String
For r = 2 To 1000
t = Timer
Application.ScreenUpdating = False
On Error GoTo ErrHandle:
Error_msg = ""
Set wb = ActiveWorkbook
Set ws = wb.Sheets("output")
'set ranges
Set Input2 = Worksheets("Input 2").Range("Input2")
Set Input_Data = Worksheets("data").Range("Input_data")
Set Colours = Worksheets("Colours").Range("Colours")
Set Output = Worksheets("output").Range("a1:G31")
OutputRow = 2
For Count_Ref = 1 To 5
Ref = Count_Ref
Error_msg = "Doing calcs for " & Count_Ref
'check if Ref is in data set AND has a y against it in the Input 2 sheet, if not return default values
If IsError(Application.VLookup(Count_Ref, Input_Data, 1, False)) Or Not (Application.VLookup(Count_Ref, Input2, 2, False) = "y") Then
'Default values
Name = "blank 1-5"
Foreground = "RGB(255,255,255)"
Text = "RGB(255,0,0)"
Val_1 = "10 mm"
Val_2 = "5 mm"
Val_3 = 1
Else
'Do Calcs for 1-5
Name = Application.VLookup(Count_Ref, Input_Data, 2, False)
Colour = Application.VLookup(Count_Ref, Input_Data, 3, False)
If IsError(Application.VLookup(Colour, Colours, 1, False)) Then Colour = "unknown" Else Colour = Colour
Foreground = Application.VLookup(Colour, Colours, 2, False)
Text = Application.VLookup(Colour, Colours, 3, False)
Val_1 = Application.VLookup(Count_Ref, Input_Data, 4, False) * 2 & " mm"
Val_2 = Application.VLookup(Count_Ref, Input_Data, 5, False) * 1 & " mm"
Val_3 = Application.VLookup(Count_Ref, Input2, 3, False)
End If
'Enter Values in output
PrintOutput ws, "a", OutputRow, Ref
PrintOutput ws, "b", OutputRow, Name
PrintOutput ws, "c", OutputRow, Foreground
PrintOutput ws, "d", OutputRow, Text
PrintOutput ws, "e", OutputRow, Val_1
PrintOutput ws, "f", OutputRow, Val_2
PrintOutput ws, "g", OutputRow, Val_3
OutputRow = OutputRow + 1
Next Count_Ref
For Count_Ref = 6 To 10
Ref = Count_Ref
Error_msg = "Doing calcs for " & Count_Ref
'check if Ref is in data set AND has a y against it in the Input 2 sheet, if not return default values
If IsError(Application.VLookup(Count_Ref, Input_Data, 1, False)) Or Application.VLookup(Count_Ref, Input2, 1, False) = "" Then
'Default values
Name = "blank 6-10"
Foreground = "RGB(255,255,255)"
Text = "RGB(255,0,0)"
Val_1 = "10 mm"
Val_2 = "5 mm"
Val_3 = 1
Else
'Do Calcs for 6-10
Name = Application.VLookup(Count_Ref, Input_Data, 2, False)
Colour = Application.VLookup(Count_Ref, Input_Data, 3, False)
If IsError(Application.VLookup(Colour, Colours, 1, False)) Then Colour = "unknown" Else Colour = Colour
Foreground = Application.VLookup(Colour, Colours, 2, False)
Text = Application.VLookup(Colour, Colours, 3, False)
Val_1 = Application.VLookup(Count_Ref, Input_Data, 4, False) * 1.5 & " mm"
Val_2 = Application.VLookup(Count_Ref, Input_Data, 5, False) * 1 & " mm"
Val_3 = Application.VLookup(Count_Ref, Input2, 3, False)
End If
'Enter Values in output
PrintOutput ws, "a", OutputRow, Ref
PrintOutput ws, "b", OutputRow, Name
PrintOutput ws, "c", OutputRow, Foreground
PrintOutput ws, "d", OutputRow, Text
PrintOutput ws, "e", OutputRow, Val_1
PrintOutput ws, "f", OutputRow, Val_2
PrintOutput ws, "g", OutputRow, Val_3
OutputRow = OutputRow + 1
Next Count_Ref
For Count_Ref = 10 To 20
Ref = Count_Ref
Error_msg = "Doing calcs for " & Count_Ref
'check if Ref is in data set AND has a y against it in the Input 2 sheet, if not return default values
If IsError(Application.VLookup(Count_Ref, Input_Data, 1, False)) Or Application.VLookup(Count_Ref, Input2, 1, False) = "" Then
'Default values
Name = "blank 10-20"
Foreground = "RGB(255,255,255)"
Text = "RGB(255,0,0)"
Val_1 = "10 mm"
Val_2 = "5 mm"
Val_3 = 1
Else
'Do Calcs for 10-20
Name = Application.VLookup(Count_Ref, Input_Data, 2, False)
Colour = Application.VLookup(Count_Ref, Input_Data, 3, False)
If IsError(Application.VLookup(Colour, Colours, 1, False)) Then Colour = "unknown" Else Colour = Colour
Foreground = Application.VLookup(Colour, Colours, 2, False)
Text = Application.VLookup(Colour, Colours, 3, False)
Val_1 = Application.VLookup(Count_Ref, Input_Data, 4, False) * 3 & " mm"
Val_2 = Application.VLookup(Count_Ref, Input_Data, 5, False) + 10 & " mm"
Val_3 = Application.VLookup(Count_Ref, Input2, 3, False)
Ref = Count_Ref
End If
'Enter Values in output
PrintOutput ws, "a", OutputRow, Ref
PrintOutput ws, "b", OutputRow, Name
PrintOutput ws, "c", OutputRow, Foreground
PrintOutput ws, "d", OutputRow, Text
PrintOutput ws, "e", OutputRow, Val_1
PrintOutput ws, "f", OutputRow, Val_2
PrintOutput ws, "g", OutputRow, Val_3
OutputRow = OutputRow + 1
Next Count_Ref
For Count_Ref = 21 To 30
Ref = Count_Ref
Error_msg = "Doing calcs for " & Count_Ref
'check if Ref is in data set AND has a y against it in the Input 2 sheet, if not return default values
If IsError(Application.VLookup(Count_Ref, Input_Data, 1, False)) Or Application.VLookup(Count_Ref, Input2, 1, False) = "" Then
'Default values
Name = "blank 21-50"
Foreground = "RGB(255,255,255)"
Text = "RGB(255,0,0)"
Val_1 = "10 mm"
Val_2 = "5 mm"
Val_3 = 1
Else
'Do Calcs for 21-30
Name = Application.VLookup(Count_Ref, Input_Data, 2, False)
Colour = Application.VLookup(Count_Ref, Input_Data, 3, False)
If IsError(Application.VLookup(Colour, Colours, 1, False)) Then Colour = "unknown" Else Colour = Colour
Foreground = Application.VLookup(Colour, Colours, 2, False)
Text = Application.VLookup(Colour, Colours, 3, False)
Val_1 = Application.VLookup(Count_Ref, Input_Data, 4, False) - 1 & " mm"
Val_2 = Application.VLookup(Count_Ref, Input_Data, 5, False) / 2 & " mm"
Val_3 = Application.VLookup(Count_Ref, Input2, 3, False)
End If
'Enter Values in output
PrintOutput ws, "a", OutputRow, Ref
PrintOutput ws, "b", OutputRow, Name
PrintOutput ws, "c", OutputRow, Foreground
PrintOutput ws, "d", OutputRow, Text
PrintOutput ws, "e", OutputRow, Val_1
PrintOutput ws, "f", OutputRow, Val_2
PrintOutput ws, "g", OutputRow, Val_3
OutputRow = OutputRow + 1
Next Count_Ref
Application.ScreenUpdating = True
t = Timer - t
Worksheets("output").Range("j" & r) = t
Next r
Exit Sub
ErrHandle:
If Err.Number > 0 Then
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
' Worksheets("Calcs for standard settings").Range("E1").Value = "error"
MsgBox "The update has failed due to:" & vbNewLine & Error_msg & vbNewLine & "(error " & Err.Number & " - " & Err.Description & ")" & vbNewLine & "Please check for errors in code or data set", vbOKOnly + vbCritical, "Test Programm"
End Sub
Option Private Module
Sub PrintOutput(ws As Worksheet, sCol As String, OutputRow As Integer, sValue As String)
ws.Range(sCol & OutputRow).Value = sValue
'If Not (ws.Range(sCol & OutputRow).Value = sValue) Then ws.Range(sCol & OutputRow).Value = sValue
End Sub
Option Private Module
Sub PrintOutput(ws As Worksheet, sCol As String, OutputRow As Integer, sValue As String)
ws.Range(sCol & OutputRow).Value = sValue
'If Not (ws.Range(sCol & OutputRow).Value = sValue) Then ws.Range(sCol & OutputRow).Value = sValue
End Sub