Im creating a new sheet in a spreadsheet that has teh code Im basing my new macro off of.
The file is attached. The question i have pertains to sheet 4 and 5, the two wishlist sheets.
The code I have is copied from the code for the Add Data macro in the add request sheet. You can run it to understand what Im saying.
Basically the existing code was used to match the broker name in column A in sheet "add request" and paste the information in the top (B1:B5) to the matched column B in the sorted by broker sheet. Try it for yourself
Now what I need help with:
in the wishlist sheets its a similar idea except the broker might not exist yet in the "Wishlight - Brokers" sheet column B. So its first assessing whether the broker listed in sheet "Add Wislist" Column A exists or not and if it doesnt then paste the broker name and the info from B3:B5. If it already exists and can be matched in Column B in "wishlist - brokers" then do what the macros is already doing and duplicate the information in B3:b5 in "Add Wislist" for a row beneath the broker name in "Wishlight - Brokers"
Here is the code i have. The code assumes that the Broker is already listed and that is what I am trying to fix, adding the additional if statement for when it is not listed
[Blocked Image: http://www.excelforum.com/clientscript/select_all_icon.jpg] [Blocked Image: http://static.beckelman.net/v1/img/page_white_copy.png]
Const WishlistWs As String = "Add Wishlist"
Const SortedListWs As String = "Wishlist - Brokers"
Const FR1 As Integer = 8
Const FR2 As Integer = 2
Const DataStg As String = "B3:B5"
Dim WkRg As Range
Dim Rg As Range
Dim LR As Long
Dim DataRg As Range
Dim I As Integer
Set WkRg = Range(.Cells(FR1, "A"), .Cells(Rows.Count, "A").End(3))
Set DataRg = .Range(DataStg)
For I = .Cells(Rows.Count, "B").End(3).Row To FR2 Step -1
If (Application.IsError(Application.Match(.Cells(I, "B"), WkRg, 0))) Then
.Cells(I + 1, "B").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(I + 1, "B").Offset(0, 1).PasteSpecial _
Im just not too sure what that coding is or if other things need to be changed within the code first