IF statement for pasting entries into another sheet

[vpan]

Hi,

I'm creating a new sheet in a spreadsheet that has teh code I'm 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 it's a similar idea except the broker might not exist yet in the "Wishlight - Brokers" sheet column B. So it's first assessing whether the broker listed in sheet "Add Wislist" Column A exists or not and if it doesn't 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


Sub AddToWishlist()
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
With Sheets(WishlistWs)
Set WkRg = Range(.Cells(FR1, "A"), .Cells(Rows.Count, "A").End(3))
Set DataRg = .Range(DataStg)
End With
With Sheets(SortedListWs)
For I = .Cells(Rows.Count, "B").End(3).Row To FR2 Step -1
If (Application.IsError(Application.Match(.Cells(I, "B"), WkRg, 0))) Then
Else
.Cells(I + 1, "B").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
DataRg.Copy
.Cells(I + 1, "B").Offset(0, 1).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
End If
Next
End With
End Sub

Im just not too sure what that coding is or if other things need to be changed within the code first

Any ideas?

[mdmackillop]

Call the code here

Call AddReqBro
With Sheets(WishlistWs)

Code:

Sub AddReqBro()
Dim r As Range, tgt As Range, data As Range, cel As Range, c As Range
Dim ws As Worksheet
Set ws = Sheets("Wishlist - Brokers")
Set r = Columns(1).Find("Requested Brokers")(2)
Set r = Range(r, Cells(Rows.Count, 1).End(xlUp))
Set tgt = ws.Cells(Rows.Count, 2).End(xlUp)(3)
Set data = ws.Columns(2)
For Each cel In r
Set c = data.Find(cel)
If c Is Nothing Then tgt.Value = cel.Value
Set tgt = ws.Cells(Rows.Count, 2).End(xlUp)(3)
Next
End Sub

[vinos]

??? i couldn't get the code yet

[mdmackillop]

Vinos
Please do not post meaningless comments. They will be deleted.