markusreyes2907
New Member
- Joined
- Jul 14, 2020
- Messages
- 34
- Office Version
- 2013
- Platform
- 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"