Define Name Range in a VBA Macro

[slohman]

I have 2 worksheets "Cost" (name never changes) and "Option1" (names changes regularly)

I am using this code that works really well but I need it to change to a define name range

"d1:D5000" Named range = "Part_No"

I need it to loop through my "Cost" sheet Column "B30:B78" and place the Part_No that has a Qty amount which is Named range = "Qty" which is in Column G1:G5000".

Then loop through again and put the Qty amount in Column "D30:D78"

LR = Sheets(SheetName).Range("G" & Rows.Count).End(xlUp).Row
MyCol = 2
MyRow = 30
    For i = 1 To 5000
        If Val(Sheets(SheetName).Range("G" & i).Value) > 1 Then
            Do Until Sheets("Cost").Cells(MyRow, MyCol).Value = "" Or MyRow > 78
                MyRow = MyRow + 1
            Loop
            If MyRow <= 78 Then
                Sheets("Cost").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("G" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
                    Exit For
                End If
            End If
    Next i

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Hui]

Can you please attach a sample workbook so that this makes more sense?

 

[slohman]

Sample Blank Work Cost.xlsm attached

 

[slohman]

Sample has been attached

 

[slohman]

Can anybody help with this. TIA

 

[Nebu]

Hi:

Try to set the ranges in the code itself
something like
Dim Rng as range
set rng = sheet1.Range("D1:D5000")

If this did not solve your query upload a sample file removing all other macros but the one you listed in your OP and clearly specify what you want.

Thanks