for loop with named cells

padadof2

New Member
Joined
Jan 11, 2010
Messages
27
I have a workbook that has two worksheets, "Bid Sheet" and "Proposal" I would like to loop through the "bid sheets" rows and paste them over to the proposal sheet. The bid sheet could have 1 row to paste or 20 rows, depending on the job. I have the cells named, item1, Desc1, Quan1, and Unit1, then item2, Desc2, Etc.... My boss will sometimes delete a row in the bid sheet as well, so I have to delete the empty rows as well. I was going to start by copying and pasting the cells down, so I could delete the empty rows before pasting. Not sure if that's the correct method.

I'm new to VBA and google has been my friend. I was trying to loop and change the named cells based on the loop, but again, I'm stuck. Forgive my messy code

Here is my workbook

VBA Code:
Sub proposal()

    Dim i As Long
    Dim j As Long
    Dim x As Range
    Dim RowNumP As Long
    Dim shB As Worksheet
    Dim shP As Worksheet
    Dim r As Long
           
    Application.ScreenUpdating = False
   
    Set shB = Sheets("Bid Sheet")
    Set shP = Sheets("Proposal")
   
    RowNumP = shB.Cells(Rows.Count, 1).End(xlUp).Row
    Range("A9:D50").Copy Destination:=shB.Range("A60")
   
    For i = 110 To 60 Step -1
    If Trim(Cells(i, 2)) = “” Then
        Rows(i).Delete
    End If
   
Next

    shP.Activate
   
    For j = 1 To RowNumP Step 3
   
        For r = 60 To 110
       
            Cells(j, 1).Value = shB.Cells(r - 1, 2)
       
        Next r
       
    Next j
End Sub

The copy paste part works flawless, but that's it. The above code is not finished, of course, but it's where I was stumped.
 

Some videos you may like

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

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
970
Office Version
  1. 2016
Platform
  1. Windows
You do not need to activate to switch between sheet. Since you have already defined shB and shP, you just have to put shB.Cells(row,col) or shP.Cells(row,col) when you run macro. This was it will run faster.

The problem I can see with the code is that when using For - Next loop and you are deleting rows, the count i keep increasing, thus it will skip the next row after deleting.. So, once you delete the row, you need to keep the same i count. Therefore, you need to insert line i = i - 1 after deleting the row. This way you will not skip line.

One more thing to note is that the end of loop row will still the same number even after you delete rows, thus you will keep looping more rows than necessary. For loop will remember the initial count even if the end loop is variable such as RowNumP in this case.

Usually I like to used While (condition) loop when dealing with variable end loop count to be a bit efficient
 

Watch MrExcel Video

Forum statistics

Threads
1,127,210
Messages
5,623,411
Members
415,972
Latest member
SY1234

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