Results 1 to 8 of 8

How can avoid .copy and .paste in this loop?

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Question How can avoid .copy and .paste in this loop?

    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
    Attached Images Attached Images
    Last edited by carlmon; 03-31-2022 at 07:57 AM. Reason: adding excel file

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data Validation - Avoid overwriting due to copy/paste
    By amitgaja007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2015, 01:29 AM
  2. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  3. Macro to Copy and Paste Special Value to avoid Circular Reference
    By alpha608 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2013, 01:41 PM
  4. 1 sheet same data different cells (anyway to copy paste to avoid manual labor)
    By fruitypebbs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2010, 07:06 PM
  5. Replies: 0
    Last Post: 09-10-2007, 10:22 AM
  6. [SOLVED] Copy/Paste how to avoid the copy of formula cells w/o calc values
    By Dennis in forum Excel General
    Replies: 10
    Last Post: 03-02-2006, 06:50 PM
  7. How avoid errors when you Copy chartobjects paste in powerpoint
    By Gunnar Johansson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2005, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1