Help with code & some explanation

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
I am trying to create a code that builds tables based on user input from input boxes. I have most of it down, and thanks to asking a question a few days ago, I am well on my way to having my code complete. I am however, having an issue that I would like some help solving. In the attached image you can see after the Temperature table, there is a table for Mean, Std Dev, Min and Max. I need to recreate this table next to all the other tables that I have built into the code. The next table is for Dissolved Oxygen, (labeled DO), and after tables for pH, Hardness, Alkalinity, and Conductivity. I have tired to create code to replicate the "Means" table, but I can't figure out how to get it so position correctly based on the different values given by the user in the beginning. Below is the code I have so far. If you do provide code that would help me solve my issue, I would also appreciate some explanation so that I can understand how to solve an issue like this in the future. Thanks!

VBA Code:
Dim reps As Long, i As Long, trtmnt As Long, j As Long, Rws As Long
On Error Resume Next
reps = InputBox("Number of reps", "Enter Here")
trtmnt = InputBox("Number of total treatment groups", "Enter Here")
Rws = 6

For i = 1 To trtmnt
   Range("A" & Rws).Value = i
   For j = 1 To reps
      Range("B" & Rws) = " "
        Rws = Rws + 1
            Next j
   Rws = Rws + 1
Next i


With Range("B4:C4")
    .Merge
    .Value = "Day of Test"
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
End With

Dim numdays As Integer, k As Integer
numdays = InputBox("Number of study days", "Enter Here")

For k = 0 To numdays
    With Range("C5").Cells(1, k)
    .Value = k
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    End With
Next k

Dim Rws2 As Long
    Rws2 = 5
    Range("A" & 5).Cells.Offset(0, k + 2).Value = "Treatment"
    For i = 1 To trtmnt
    Range("A" & Rws2).Cells.Offset(1, k + 2).Value = i
    Rws2 = Rws2 + 1
    Next i
    Range("A" & 5).Cells.Offset(0, k + 2).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("A" & 5).Cells.Offset(0, k + 3).Value = "Mean"
    Range("A" & 5).Cells.Offset(0, k + 3).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("A" & 5).Cells.Offset(0, k + 4).Value = "Std Dev"
    Range("A" & 5).Cells.Offset(0, k + 4).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("A" & 5).Cells.Offset(0, k + 5).Value = "Min"
    Range("A" & 5).Cells.Offset(0, k + 5).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("A" & 5).Cells.Offset(0, k + 6).Value = "Max"
    Range("A" & 5).Cells.Offset(0, k + 6).Borders(xlEdgeBottom).LineStyle = xlContinuous
        
    
Dim Rws3 As Long
  Rws3 = 4
    Range("A" & 5).Cells.Offset(0, k + 8).Value = "Treatment"
    For i = 1 To trtmnt
    Range("A" & Rws3).Cells.Offset(2, k + 8).Value = i
    Rws3 = Rws3 + 1
    For j = 1 To reps
      Range("B" & Rws3) = " "
                 Next j
   Rws3 = Rws3 + 2
    Next i
    Range("A" & 5).Cells.Offset(0, k + 8).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 8).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 9).Value = "Temp"
    Range("A" & 5).Cells.Offset(0, k + 9).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 9).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 10).Value = "DO"
    Range("A" & 5).Cells.Offset(0, k + 10).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 10).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 11).Value = "pH"
    Range("A" & 5).Cells.Offset(0, k + 11).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 11).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 12).Value = "Hardness"
    Range("A" & 5).Cells.Offset(0, k + 12).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 12).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 13).Value = "Alkalinity"
    Range("A" & 5).Cells.Offset(0, k + 13).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 13).Borders(xlEdgeBottom).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 13).Value = "Conductivity"
    Range("A" & 5).Cells.Offset(0, k + 13).Borders(xlEdgeTop).LineStyle = xlDouble
    Range("A" & 5).Cells.Offset(0, k + 13).Borders(xlEdgeBottom).LineStyle = xlDouble



  Range("A" & Rws).Cells.Offset(2, 0).Value = "DO"
  Range("A" & Rws).Cells.Offset(2, 0).Font.Bold = True
  
  Range("A" & Rws).Cells.Offset(4, 0).Value = "Treatment"
  Range("A" & Rws).Cells.Offset(4, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
  Range("A" & Rws).Cells.Offset(3, 1).Value = "Day of Test"
For k = 0 To numdays
  With Range("A" & Rws).Cells.Offset(4, k + 1)
  .Value = k
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  End With
Next k
For i = 1 To trtmnt
  Range("A" & Rws).Cells.Offset(5, 0).Value = i
  For j = 1 To reps
    Range("B" & Rws).Cells.Offset(5, 0).Value = " "
        Rws = Rws + 1
    Next j
  Rws = Rws + 1
Next i
 
    
Range("A" & Rws).Cells.Offset(7, 0).Value = "pH"
  Range("A" & Rws).Cells.Offset(7, 0).Font.Bold = True
  
  Range("A" & Rws).Cells.Offset(9, 0).Value = "Treatment"
  Range("A" & Rws).Cells.Offset(9, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
  Range("A" & Rws).Cells.Offset(8, 1).Value = "Day of Test"
  For k = 0 To numdays
  With Range("A" & Rws).Cells.Offset(9, k + 1)
  .Value = k
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  End With
Next k
   
For i = 1 To trtmnt
  Range("A" & Rws).Cells.Offset(10, 0).Value = i
  For j = 1 To reps
    Range("B" & Rws).Cells.Offset(10, 0).Value = " "
        Rws = Rws + 1
    Next j
  Rws = Rws + 1
Next i

Range("A" & Rws).Cells.Offset(12, 0).Value = "Hardness"
  Range("A" & Rws).Cells.Offset(12, 0).Font.Bold = True
  
  Range("A" & Rws).Cells.Offset(14, 0).Value = "Treatment"
  Range("A" & Rws).Cells.Offset(13, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
  Range("A" & Rws).Cells.Offset(13, 1).Value = "Day of Test"
For k = 0 To numdays
  With Range("A" & Rws).Cells.Offset(14, k + 1)
  .Value = k
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  End With
Next k
  Range("A" & Rws).Cells.Offset(15, 0).Value = "NC"
  Range("A" & Rws).Cells.Offset(16, 0).Value = "High"
  
Range("A" & Rws).Cells.Offset(19, 0).Value = "Alkalinity"
  Range("A" & Rws).Cells.Offset(19, 0).Font.Bold = True
  
  Range("A" & Rws).Cells.Offset(21, 0).Value = "Treatment"
  Range("A" & Rws).Cells.Offset(20, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
  Range("A" & Rws).Cells.Offset(20, 1).Value = "Day of Test"
For k = 0 To numdays
  With Range("A" & Rws).Cells.Offset(21, k + 1)
  .Value = k
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  End With
Next k
  Range("A" & Rws).Cells.Offset(22, 0).Value = "NC"
  Range("A" & Rws).Cells.Offset(23, 0).Value = "High"
  
Range("A" & Rws).Cells.Offset(26, 0).Value = "Conductivity"
  Range("A" & Rws).Cells.Offset(26, 0).Font.Bold = True
  Range("A" & Rws).Cells.Offset(28, 0).Value = "Treatment"
  Range("A" & Rws).Cells.Offset(27, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
  Range("A" & Rws).Cells.Offset(27, 1).Value = "Day of Test"
For k = 0 To numdays
  With Range("A" & Rws).Cells.Offset(28, k + 1)
  .Value = k
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  End With
Next k
  Range("A" & Rws).Cells.Offset(29, 0).Value = "NC"
  Range("A" & Rws).Cells.Offset(30, 0).Value = "High"
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    37.7 KB · Views: 16

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Oh I'm so sorry, I forgot I didn't format the sample. So DO and pH should look just like the Temperature table as far as reps and treatment groups go. Hardness, Alkalinity, and Conductivity only need a NC (negative control) and High for the treatment groups.
I tried the code and formatting is exactly what I need! Thank you!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
716
Office Version
  1. 365
Platform
  1. Windows
Ok then my 2nd code works as needed in terms of formatting ?

Edit: looks like we both posted at the same time :) ... Excellent, I’ll see about the formulas
 

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Ok then my 2nd code works as needed in terms of formatting ?

Edit: looks like we both posted at the same time :) ... Excellent, I’ll see about the formulas
Yes, I just had to fix one part. The "Summary" table which has Temperature, DO, pH, Hardness, Alkalinity, and Conductivity was missing Alkalinity. I fixed the the array from 6 to 7 and added the title "Alkalinity". Kind of glad I had to fix it so I got work with the code a tiny bit and better understand everything.
 

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Hi mse330,

I've been running the code and depending on the numbers entered into the input boxes "#N/A" is being entered after the treatment number in the columns of the Temperature, DO, and pH tables. I am going to see if I can solve the issue myself, but if you happen to see this maybe you can see what the issue may be. I've attached an image to show you. It seems to happen when my treatment groups and treatment days are the same. Depending on the numbers being input, "#N/A" appears one or more times. Below I've attached a few images.
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.7 KB · Views: 4
  • Capture2.PNG
    Capture2.PNG
    12.2 KB · Views: 4

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
716
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi markusreyes,

Try the revised below code & let me know how it goes :)

VBA Code:
Sub test_v3()

Dim Reps&, Numdays&, Trtmnt&, a, Rg As Range, Lr&, FR As Range, Fa$, j&, AA$, BB$, r As Range

'On Error GoTo oops
Numdays = InputBox("Number of study days", "Enter Here")
Reps = InputBox("Number of reps", "Enter Here")
Trtmnt = InputBox("Number of total treatment groups", "Enter Here")

'First set of tables : Temp, DO, pH
For x = 1 To 3
   Lr = IIf(x = 1, 5, Range("A" & Rows.Count).End(3).Offset(5 + Reps).Row)
   Set Rg = Cells(Lr, 2).Resize(, Numdays + 1)
   Rg.Cells(1).Offset(-2, -1).Resize(3) = Application.Transpose(Array(IIf(x = 1, "Temprature", IIf(x = 2, "DO", "pH")), "", "Treatment"))
   Rg.Cells(1).Offset(-1) = "Day of Test"
   Rg.Cells(1).Offset(-2, -1).Font.Bold = True
   Rg = Evaluate("column(" & Rg.Address & ")-2")
   Rg.Borders(3).Weight = xlThin: Rg.Offset(, -1).Resize(, Rg.Columns.Count + 1).Borders(4).Weight = xlThin
   a = Evaluate(Replace("if({1},if(mod(row(@)," & Reps + 1 & ")-1=0,roundup(row(@)/" & Reps + 1 & ",0),""""))", "@", Cells(1).Resize(Reps * (Trtmnt + 1)).Address))
   Rg.Cells(1).Offset(1, -1).Resize(UBound(a)) = a
   
   Set Rg = Rg.Cells(1).Offset(, Rg.Columns.Count + 1)
   Rg.Resize(, 5) = Array("Treatment", "Mean", "Std Dev", "Min", "Max")
   Rg.Resize(, 5).Borders(4).Weight = xlThin
   Rg.Offset(1).Resize(Trtmnt) = Evaluate("row(" & Cells(1).Resize(Trtmnt).Address & ")")
   
   'formulas - FR refers to Formula Range & Fa refers to Formula Address
   For i = 1 To Trtmnt
      j = IIf(i = 1, 1, (i - 1) * (Reps + 1) + 1)
      Fa = Cells(Lr, 2).Resize(, Numdays + 1).Resize(Reps).Offset(j).Address
      Set FR = Rg.Offset(i, 1).Resize(, 4)
      FR = Array("=average(" & Fa & ")", "=stdev(" & Fa & ")", "=min(" & Fa & ")", "=max(" & Fa & ")")
   Next
   
   If x = 1 Then
      Set Rg = Rg.Offset(, 6)
      Rg.Resize(, 7) = Array("Treatment", "Temp", "DO", "pH", "Hardness", "Conductivity", "Alkalinity")
      Rg.Resize(, 7).Borders(3).LineStyle = xlDouble: Rg.Resize(, 7).Borders(4).LineStyle = xlDouble
      a = Evaluate(Replace("if({1},if(mod(row(@)," & Reps & ")-1=0,roundup(row(@)/" & Reps & ",0),""""))", "@", Cells(1).Resize(Reps * Trtmnt).Address))
      Rg.Offset(1).Resize(UBound(a)) = a
   End If
   
   'formulas for summary table
   For i = 1 To Trtmnt * Reps Step Reps
      If x > 1 Then If i = 1 Then Set Rg = Rg.Offset(, 6)
      j = IIf(i = 1, 1, j + 1)
      If x = 1 Then Set r = Rg
      AA = Rg.Offset(1 + j - 1, -5).Address: BB = Rg.Offset(1 + j - 1, -4).Address
      r.Offset(i, 1 + x - 1) = "=round(" & AA & ",1)&"" ± ""&round(" & BB & ",2)"
      AA = Rg.Offset(1 + j - 1, -3).Address: BB = Rg.Offset(1 + j - 1, -2).Address
      r.Offset(1 + i, 1 + x - 1) = "=" & AA & "& "" – ""&" & BB
   Next
Next

'Second set of tables : Hardness, Alkalinity, Conductivity
For x = 1 To 3
   Lr = IIf(x = 1, Range("A" & Rows.Count).End(3).Offset(5 + Reps).Row, Lr + 7)
   Set Rg = Cells(Lr, 2).Resize(, Numdays + 1)
   Rg = Evaluate("column(" & Rg.Address & ")-2")
   Rg.Borders(3).Weight = xlThin: Rg.Offset(, -1).Resize(, Rg.Columns.Count + 1).Borders(4).Weight = xlThin
   Rg.Cells(1).Offset(-2, -1).Resize(3) = Application.Transpose(Array(IIf(x = 1, "Hardness", IIf(x = 2, "Alkalinity", "Conductivity")), "", "Treatment"))
   Rg.Cells(1).Offset(-1) = "Day of Test"
   Rg.Cells(1).Offset(-2, -1).Font.Bold = True
   Rg.Cells(1).Offset(1, -1).Resize(2) = [{"NC";"High"}]
   Set Rg = Rg.Cells(1).Offset(, Rg.Columns.Count + 1)
   Rg.Resize(, 5) = Array("Treatment", "Mean", "Std Dev", "Min", "Max")
   Rg.Resize(, 5).Borders(4).Weight = xlThin
   Rg.Offset(1).Resize(2) = [{1;2}]
   
   'formulas - FR refers to Formula Range & Fa refers to Formula Address
   For i = 1 To 2
      Fa = Cells(Lr, 2).Resize(, Numdays + 1).Offset(i).Address
      Set FR = Rg.Offset(i, 1).Resize(, 4)
      FR = Array("=average(" & Fa & ")", "=stdev(" & Fa & ")", "=min(" & Fa & ")", "=max(" & Fa & ")")
   Next
   
   'formulas for summary table
   For i = 1 To Trtmnt * Reps Step Reps
      j = IIf(i = 1, 1, j + 1)
      If i = 1 Then If x = 1 Then Set r = r.Offset(, 4) Else Set r = r.Offset(, 1)
      If i = 1 Or i = ((Trtmnt * Reps) - Reps + 1) Then
         If i = ((Trtmnt * Reps) - Reps + 1) Then j = 2
         AA = Rg.Offset(1 + j - 1, 1).Address: BB = Rg.Offset(1 + j - 1, 2).Address
         r.Offset(i) = "=round(" & AA & ",1)&"" ± ""&round(" & BB & ",2)"
         
         AA = Rg.Offset(1 + j - 1, 3).Address: BB = Rg.Offset(1 + j - 1, 4).Address
         r.Offset(1 + i) = "=" & AA & "& "" – ""&" & BB
      Else
         r.Offset(i) = "--":  r.Offset(1 + i) = "--"
      End If
   Next
Next

Exit Sub
oops: MsgBox "InputBox accepts numeric values only ... Macro aborted", vbExclamation

End Sub
 
Solution

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Hi markusreyes,

Try the revised below code & let me know how it goes :)

VBA Code:
Sub test_v3()

Dim Reps&, Numdays&, Trtmnt&, a, Rg As Range, Lr&, FR As Range, Fa$, j&, AA$, BB$, r As Range

'On Error GoTo oops
Numdays = InputBox("Number of study days", "Enter Here")
Reps = InputBox("Number of reps", "Enter Here")
Trtmnt = InputBox("Number of total treatment groups", "Enter Here")

'First set of tables : Temp, DO, pH
For x = 1 To 3
   Lr = IIf(x = 1, 5, Range("A" & Rows.Count).End(3).Offset(5 + Reps).Row)
   Set Rg = Cells(Lr, 2).Resize(, Numdays + 1)
   Rg.Cells(1).Offset(-2, -1).Resize(3) = Application.Transpose(Array(IIf(x = 1, "Temprature", IIf(x = 2, "DO", "pH")), "", "Treatment"))
   Rg.Cells(1).Offset(-1) = "Day of Test"
   Rg.Cells(1).Offset(-2, -1).Font.Bold = True
   Rg = Evaluate("column(" & Rg.Address & ")-2")
   Rg.Borders(3).Weight = xlThin: Rg.Offset(, -1).Resize(, Rg.Columns.Count + 1).Borders(4).Weight = xlThin
   a = Evaluate(Replace("if({1},if(mod(row(@)," & Reps + 1 & ")-1=0,roundup(row(@)/" & Reps + 1 & ",0),""""))", "@", Cells(1).Resize(Reps * (Trtmnt + 1)).Address))
   Rg.Cells(1).Offset(1, -1).Resize(UBound(a)) = a
  
   Set Rg = Rg.Cells(1).Offset(, Rg.Columns.Count + 1)
   Rg.Resize(, 5) = Array("Treatment", "Mean", "Std Dev", "Min", "Max")
   Rg.Resize(, 5).Borders(4).Weight = xlThin
   Rg.Offset(1).Resize(Trtmnt) = Evaluate("row(" & Cells(1).Resize(Trtmnt).Address & ")")
  
   'formulas - FR refers to Formula Range & Fa refers to Formula Address
   For i = 1 To Trtmnt
      j = IIf(i = 1, 1, (i - 1) * (Reps + 1) + 1)
      Fa = Cells(Lr, 2).Resize(, Numdays + 1).Resize(Reps).Offset(j).Address
      Set FR = Rg.Offset(i, 1).Resize(, 4)
      FR = Array("=average(" & Fa & ")", "=stdev(" & Fa & ")", "=min(" & Fa & ")", "=max(" & Fa & ")")
   Next
  
   If x = 1 Then
      Set Rg = Rg.Offset(, 6)
      Rg.Resize(, 7) = Array("Treatment", "Temp", "DO", "pH", "Hardness", "Conductivity", "Alkalinity")
      Rg.Resize(, 7).Borders(3).LineStyle = xlDouble: Rg.Resize(, 7).Borders(4).LineStyle = xlDouble
      a = Evaluate(Replace("if({1},if(mod(row(@)," & Reps & ")-1=0,roundup(row(@)/" & Reps & ",0),""""))", "@", Cells(1).Resize(Reps * Trtmnt).Address))
      Rg.Offset(1).Resize(UBound(a)) = a
   End If
  
   'formulas for summary table
   For i = 1 To Trtmnt * Reps Step Reps
      If x > 1 Then If i = 1 Then Set Rg = Rg.Offset(, 6)
      j = IIf(i = 1, 1, j + 1)
      If x = 1 Then Set r = Rg
      AA = Rg.Offset(1 + j - 1, -5).Address: BB = Rg.Offset(1 + j - 1, -4).Address
      r.Offset(i, 1 + x - 1) = "=round(" & AA & ",1)&"" ± ""&round(" & BB & ",2)"
      AA = Rg.Offset(1 + j - 1, -3).Address: BB = Rg.Offset(1 + j - 1, -2).Address
      r.Offset(1 + i, 1 + x - 1) = "=" & AA & "& "" – ""&" & BB
   Next
Next

'Second set of tables : Hardness, Alkalinity, Conductivity
For x = 1 To 3
   Lr = IIf(x = 1, Range("A" & Rows.Count).End(3).Offset(5 + Reps).Row, Lr + 7)
   Set Rg = Cells(Lr, 2).Resize(, Numdays + 1)
   Rg = Evaluate("column(" & Rg.Address & ")-2")
   Rg.Borders(3).Weight = xlThin: Rg.Offset(, -1).Resize(, Rg.Columns.Count + 1).Borders(4).Weight = xlThin
   Rg.Cells(1).Offset(-2, -1).Resize(3) = Application.Transpose(Array(IIf(x = 1, "Hardness", IIf(x = 2, "Alkalinity", "Conductivity")), "", "Treatment"))
   Rg.Cells(1).Offset(-1) = "Day of Test"
   Rg.Cells(1).Offset(-2, -1).Font.Bold = True
   Rg.Cells(1).Offset(1, -1).Resize(2) = [{"NC";"High"}]
   Set Rg = Rg.Cells(1).Offset(, Rg.Columns.Count + 1)
   Rg.Resize(, 5) = Array("Treatment", "Mean", "Std Dev", "Min", "Max")
   Rg.Resize(, 5).Borders(4).Weight = xlThin
   Rg.Offset(1).Resize(2) = [{1;2}]
  
   'formulas - FR refers to Formula Range & Fa refers to Formula Address
   For i = 1 To 2
      Fa = Cells(Lr, 2).Resize(, Numdays + 1).Offset(i).Address
      Set FR = Rg.Offset(i, 1).Resize(, 4)
      FR = Array("=average(" & Fa & ")", "=stdev(" & Fa & ")", "=min(" & Fa & ")", "=max(" & Fa & ")")
   Next
  
   'formulas for summary table
   For i = 1 To Trtmnt * Reps Step Reps
      j = IIf(i = 1, 1, j + 1)
      If i = 1 Then If x = 1 Then Set r = r.Offset(, 4) Else Set r = r.Offset(, 1)
      If i = 1 Or i = ((Trtmnt * Reps) - Reps + 1) Then
         If i = ((Trtmnt * Reps) - Reps + 1) Then j = 2
         AA = Rg.Offset(1 + j - 1, 1).Address: BB = Rg.Offset(1 + j - 1, 2).Address
         r.Offset(i) = "=round(" & AA & ",1)&"" ± ""&round(" & BB & ",2)"
        
         AA = Rg.Offset(1 + j - 1, 3).Address: BB = Rg.Offset(1 + j - 1, 4).Address
         r.Offset(1 + i) = "=" & AA & "& "" – ""&" & BB
      Else
         r.Offset(i) = "--":  r.Offset(1 + i) = "--"
      End If
   Next
Next

Exit Sub
oops: MsgBox "InputBox accepts numeric values only ... Macro aborted", vbExclamation

End Sub
Wow this is great! Only thing I need Alkalinity before Conductivity in the Summary table. I'll see If I can figure out what you did and tweak it myself. If not, you might want to check back here tomorrow lol.

Thanks a ton! Working with your code is helping a ton by making me research and tweak. I really appreciate your help. :biggrin:
 

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Also in the Summary table I will only never need a single row between the data for the treatment groups. I'll try to figure this out on my own as well, but we'll see!
 

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Also in the Summary table I will only never need a single row between the data for the treatment groups. I'll try to figure this out on my own as well, but we'll see!
Nevermind it does do this. It only stopped doing it when I switched around the order of the input boxes. Switched them back and all is fine!
 

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Wow this is great! Only thing I need Alkalinity before Conductivity in the Summary table. I'll see If I can figure out what you did and tweak it myself. If not, you might want to check back here tomorrow lol.

Thanks a ton! Working with your code is helping a ton by making me research and tweak. I really appreciate your help. :biggrin:
And the Summary table does work exactly how I need it to, so nevermind on that too. Lol oops!
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
716
Office Version
  1. 365
Platform
  1. Windows
You are welcome & glad to help

So everything is as expected now ? I made the code to do exactly what you have in your sample file ... To be honest, the summary table was a bit of a headache though but got it right :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,725
Messages
5,626,508
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top