Help with code & some explanation

M

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
34
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: 17
Sort by date Sort by votes
markusreyes2907 said:
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.
Click to expand...
I tried the code and formatting is exactly what I need! Thank you!
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
mse330 said:
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
Click to expand...
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.
 
Upvote 0
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: 8
  • Capture2.PNG
    Capture2.PNG
    12.2 KB · Views: 7
Upvote 0
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
 
Upvote 0
Solution
mse330 said:
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
Click to expand...
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:
 
Upvote 0
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!
 
Upvote 0
markusreyes2907 said:
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!
Click to expand...
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!
 
Upvote 0
markusreyes2907 said:
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:
Click to expand...
And the Summary table does work exactly how I need it to, so nevermind on that too. Lol oops!
 
Upvote 0
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
334
MARK858
MARK858
J
  • Question
Transferring VBA code to another cell
2 3
Replies
20
Views
570
Joek88
J
J
  • Question
VBA Program not working to merge cells
Replies
1
Views
261
Joe4
Joe4
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
331
RoryA
RoryA
P
  • Question
Rationalise Code
Replies
1
Views
172
mumps
mumps

Forum statistics

Threads
1,214,932
Messages
6,122,334
Members
449,077
Latest member
Jocksteriom

Share this page

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
Back
Top