The formula for A20 is...
=(A1-MIN(A$1:A$5))/(MAX(A$
... Drag & Fill or Copy & Paste as required.
Please note that I've not included code to avoid Divide by Zero errors. Would you like something for that?
Regards,
Brian.
.
Option Explicit
Sub Chunk_III()
Dim i As Long
Dim xRange As Range
Dim xSheet_List As String
Dim xRange_List As String
Dim xBook_List As String
Dim xSource As Worksheet
xBook_List = "Lots_of_Columns.xlsb"
xSheet_List = "Sheet1"
xRange_List = "$A$2:$A$500001"
If Not Book_Exists(xBook_List) Then
MsgBox ("Please open """ & xBook_List & """ and retry - run cancelled.")
Exit Sub
End If
If Not Sheet_Exists(xSheet_List, xBook_List) Then
MsgBox ("Sheet """ & xSheet_List & """ not found in """ & xBook_List & """ - run cancelled.")
Exit Sub
End If
If Range(xRange_List).Rows.Count Mod 5 <> 0 Then
MsgBox ("The source range (" & xRange_List & ") must be a multiple of 5 rows - run cancelled.")
Exit Sub
End If
Set xSource = Sheets.Add
xSheet_List = "[" & xBook_List & "]" & xSheet_List & "!"
Range("A2") = "=IFERROR((" & xSheet_List & "A1-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A3") = "=IFERROR((" & xSheet_List & "A2-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A4") = "=IFERROR((" & xSheet_List & "A3-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A5") = "=IFERROR((" & xSheet_List & "A4-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A6") = "=IFERROR((" & xSheet_List & "A5-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Sheets.Add
Application.ScreenUpdating = False
For i = 0 To 38
Debug.Print i & " - " & Now()
xSource.Range("A2:A6").Copy Destination:=Range(xRange_List).Offset(0, i)
Range(xRange_List).Offset(0, i).Formula = Range(xRange_List).Offset(0, i).Value
Next
Application.DisplayAlerts = False
xSource.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Function Book_Exists(xBook_Name As String) As Boolean
Book_Exists = False
On Error Resume Next
Book_Exists = (Workbooks(xBook_Name).Name = xBook_Name)
On Error GoTo 0
End Function
Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ActiveWorkbook.Name
Sheet_Exists = False
On Error Resume Next
Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function
Regards,Xn = (X0 – Xmin) / (Xmax – Xmin)
Where,
Xn = new value for variable X
X0 = current value for variable X
Xmin = minimum value in data set
Xmax = maximum value in data set
Title | # Comments | Views | Activity |
---|---|---|---|
Excel and Formulas | 8 | 30 | |
IF Statement | 3 | 23 | |
VBA Excel: Replace a range of values on a sheet with a range of values from another sheet | 10 | 29 | |
Input box prompt to specify column letter using VBA | 4 | 13 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!