I wrote a code that creates two (2) lines of calculations and a delimiter between both sections.
The first iteration looks like this:
1.1
delim
1.2
The second iteration looks like this:
1.1
2.1
delim
1.2
2.2
and so forth.
I posted photos for a visual representation.
My code is below & I want to avoid using .copy, .paste, but when I tried to use an array, the values didn't store because the range changed. I spent four hours on my pre-made solution, but I'm sure there is a much easier way to do precisely what I'm doing with fewer lines of code.
Thanks in advance.
Sub Design_Wind_Force()
Dim calculation As String
Dim ws As Worksheet
Dim results(1 To 3) As Variant
Dim position(1 To 3) As Variant
Dim results_add(1 To 3) As Variant
Dim results_side(1 To 3) As Variant
Dim results_side_readd(1 To 3) As Variant
Set ws = Worksheets("main")
'round all variables
qz = Round(qz, 2)
G = Round(G, 2)
cf = Round(cf, 2)
F = Round(F, 2)
PSAF_equipment = Round(PSAF_equipment, 2)
p = fy_cb.Row - 4
qz_string = qz & "(qz" & p & ")"
g_string = G & "(G" & p & ")"
cf_string = cf & "(Cf" & p & ")"
psaf_equipment_string = PSAF_equipment & "(Af" & p & ") ft^2"
new_p = -p
'Column 1, 2 and 3
results(1) = "(" & p & ")" & Equipment_Full_Name
results(2) = "F" & p & " = " & qz_string & "*" & g_string & "*" & cf_string & "*" & psaf_equipment_string
results(3) = "'= " & TWF_front & " lb"
position(1) = 29
position(2) = 31
position(3) = 34
results_add(1) = "Equip. (side):....................................................................."
results_add(2) = "....................................................................."
results_add(3) = "....................................................................."
bool_loop = 0
' p indicates the position of the equipment, it is defined in a previous module.
'Force on front Results
For i = 1 To UBound(results)
Set r = ws.Cells(Rows.Count, position(i)).End(xlUp) 'first cell in Column AC with data
If r.Value > 0 Then
If r.Offset(-p + 1).Value = results_add(1) Then
Sheets("main").Range("AC" & r.Row - p + 2 & ":AH" & r.Row).Copy
Sheets("main").Range("AC" & r.Row - p + 3 & ":AH" & r.Row - p + 3).PasteSpecial
bool_loop = 1
End If
End If
If bool_loop = 1 And r.Value > 0 Then
Set r = ws.Cells(Rows.Count, position(i)).End(xlUp)
Set r = r.Offset(new_p)
r.Value = results(i)
Else
Set r = r.Offset(1)
r.Value = results(i)
bool_loop = 0
End If
Next
new_p = -p + 1
'Adding force on side text
For i = 1 To UBound(results_add)
Set r = ws.Cells(Rows.Count, position(i)).End(xlUp) 'first cell in Column AC with data
If r.Value > 0 And bool_loop = 1 Then
Set r = ws.Cells(Rows.Count, position(i)).End(xlUp)
Set r = r.Offset(new_p)
r.Value = results_add(i)
Else
Set r = r.Offset(1)
r.Value = results_add(i)
End If
Next
'Force on side Results
results_side(1) = "(" & p & ")" & Equipment_Full_Name
results_side(2) = "F" & p & " = " & qz_string & "*" & g_string & "*" & cf_string & "*" & psaf_equipment_string
results_side(3) = "'= " & TWFS_equipment & " lb"
For i = 1 To UBound(results_side)
Set r = ws.Cells(Rows.Count, position(i)).End(xlUp) 'first cell in Column AC with data
If r.Value > 0 Then
Set r = r.Offset(1)
r.Value = results_side(i)
End If
Next
End Sub
Bookmarks