if Lookup

Hello,
I am creating a commission based macro. Unfortunately the commission is based up a account number which does not have any definition. So I need a stop gap for now.  I have 36 accounts, so right now I have :
Range("H11:J" & lRow).Select
         With Range("H11:H" & lRow)
        .Formula = "=IF(A11=<""4z-BBM060"",0,30)"
        .Value = .Value
    End With


The problem is now my account codes are all over the place.  Before those <60 and those above 60 were charged accordingly. As I said I just want to hard code the client codes. But the codes in H11 order will change from day to day
rsernowskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ardhendu SarangiSr. Project ManagerCommented:
Dear rsernowski,
Can you please rephrase your question? I am not sure I understood what you need,

Thanks,
Ardhendu

p.s: Please post a sample file too for a quicker response.
0
rsernowskiAuthor Commented:
Ok , I need a macro to give commision will be either 30 or 0. the structure will be based upon the client code becuase that is the only thing that is unique. there is no structure to the commison base at this time. for example,
30 commision is going to apply to:
4z-m001, 4z-m001000, 4x- m32, 4r-m606
while the 0 commison would be applied to
4z-m004, 4z-m006s, 4d-m31
I have 36 client codes, and each day the amount of the sales and the client code of the sales will change but amount of sales will always start at cell 11
so I am reading from 1 spreadsheet , and getting my sheet that I can send to the "bank"

lets say I have:
4z-m001, 12
4x- m32,56
4d-m31,25

tomorrow I will have
4x- m32, 24
4x- m3,24
4r-m606, 99

So I just run the macro and it applies the 30 or 0 based upon the code. Yes, I would be willing to hard code in the client codes for now. but as you can see the client code list changes daily
0
rsernowskiAuthor Commented:
Maybe I can declare the client codes at the start of the macro?
Set client_code {4z-m001, 4z-m001000, 4x- m32, 4r-m606,4z-m004, 4z-m006s, 4d-m31}
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello rsernowski,

I'm not sure why you're doing this with code instead of formulas, but I don't know the whole picture. From a formula perspective, since you are coding to write formulas into the sheet, why don't you build a client code list on the worksheet that includes the clients that should get the 30 applied, and then use a formula like

=if(isna(match(A11,clientlist,0)),0,30)

instead of the formula you have above? The list of clients can easily be maintained without touching the code.

cheers, teylyn

0
rsernowskiAuthor Commented:
Hi Tev

The spreadsheet has to be sent away. So I need the program repeatable
0
rsernowskiAuthor Commented:
I start with a blank sheet, import the list from a main frame, then send the completed spreadsheet to another party
Mkae sense?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Well, in that case including the hard coded list in your code seems to be the only option. But I'm not versed enough in VBA to advise you on how to do that. Maybe with a Select Case?

cheers, teylyn
0
rsernowskiAuthor Commented:
Hi Iwas thinking that too, but the client code order changes, and the list is often not the same, I don't know how to refereence the column?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
That's where my lack of VBA is not helpful. If you have an array of valid client codes, a MATCH() would return a number if the value is found, and an #N/A error when there is no match.

I can't do this in VBA, but in a worksheet function it would be

=IF(ISNA(MATCH(A2,{"4z-m001","4z-m001000",",4x- m32","4r-m606","4z-m004","4z-m006s","4d-m31"},0)),0,30)

The order of the list is not important when Match is used with the 0 parameter for an exact match. The list or array should include all codes that trigger a 30 commission, regardless of which codes come down in the current external data.

I don't know if you could use a VBA array in this case, though. Isn't there a way in VBA to find a value in an array?

cheers, teylyn
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi All,
Sorry for being AWOL on this question but tax filing time is w wee bit of crazy time here in United States.

@rsernowski - If i understood this correctly, these are your steps -

1. Step 1 - Extract and copy the list from mainframe
2. Step 2 - Replace or update the Commissions column with 30 or 0 as the case may be
3. Step 3 - Send the updated sheet out.

did i miss anything?

Solution -

Assuming you have the client codes on a hidden worksheet within the workbook, a macro can update the values everytime it is executed. Whenever the client codes change, you need to update the Clients & Commissions Tbl sheet. Please see attached code and excel file.

Please revert back if you still have any more questions,
Thanks,
Ardhendu



Sub updateComm()
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Integer
    Dim rngX As Range
    Set ws1 = Sheets("Client Codes & Commissions Tbl")
    Set ws2 = Sheets("Data")
    

    For i = 11 To ws2.Cells(65536, "H").End(xlUp).Row
        Set rngX = ws1.Range("A:A").Find(ws2.Range("H" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            ws2.Range("J" & i) = rngX.Offset(0, 1).Value
        End If
    Next
End Sub

Open in new window

QID---25857970.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Ardhendu,

if the client codes are on a hidden sheet, as I have also suggested above, one line of code to insert a Vlookup formula instead of the asker's initial IF function would be sufficient. But that idea was rejected, because a hidden sheet seems to be out of the question.
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Teylyn,

I have also included the VLOOKUP as one option within my spreadsheet attached.

I feel if this is a simple lookup then VLOOKUP is the way to go. VBA would be an overkill.

Cheers,
Ardhendu
0
rsernowskiAuthor Commented:
Hello
Thanks for the help,
yes Teylyn is correct, I can't have a hidden sheet, the spreadsheet from the main frame is new. I guess I can have  a macro to insert the hiden sheet then remove it prior to sending the new sheet to the "bank." But it seems easiest to have a vlookup
I will give the attached spreadsheet a go
Thanks
0
rsernowskiAuthor Commented:
can I put the client codes in my vda script?
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
you can put your client codes in the VBA script .

Another option is to even read them off from another spreadsheet that you maintain. The codes need not be stored within this spreadsheet.

let me know if you have any questions,

Thanks,
Ardhendu
0
rsernowskiAuthor Commented:
Hi Pari123

Thanks for the info. Where do I add the clients to my VBA code.?
0
rsernowskiAuthor Commented:
Hi Ardhendu

Ok I have created another spredsheet , that will have the account codes, names, and the commish rate. I am going to keep the account codes on the user desktop. How do I reference another sheet?



0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
Can you please post your spreadsheet here? It would help to give you a correct answer.

Thanks,
Ardhendu
0
rsernowskiAuthor Commented:
Hello

I am going to paste in a good section of the entire code
Thanks
Robert

Dim-FileName-As-String.doc
0
rsernowskiAuthor Commented:
Hello Ardhendu
did you have a chance to review?
Regards
RS
0
rsernowskiAuthor Commented:
Hello Ardhendu

I have another suggestion from above,  the commision table will be required to have certain accounts with a commison for buy but no commison for sell. Others would be either 0 or 30 for both. I have attached a spreadsheet with commison table
thanks
Robert

commison-table.xls
0
Ardhendu SarangiSr. Project ManagerCommented:
Dear Robert,

Thanks for the code and the commissions table. I have created the following macro based on the logic. Please note this is not a complete macro but just my first step towards your final solution. we still need to work on this based on the inputs you give me.

1. I have kept the macro within your commission-table spreadsheet and you can then use this as a reference to assign the commissions into any other spreadsheet.

2. I have assumed that in your spreadsheet the codes are in the column A. If this is not true then the code has to be changed.

3. The 2nd assumption is that you have only one sheet within the spreadsheet. If this is not right, then I would have to tweak the macro again.

4. The commissions table that you attached has the codes as "M001", "M002" etc whereas the client-codes from mainframe seems to have a combination of numbers and text. Therefore I have applied a partial search to match up the commission codes with the client codes as you can see below.

Please test this and see if this works for you.

I apologize for the delay but we had a busy previous week and I was not able to respond to you.

thanks,
Ardhendu
Option Explicit
Sub UpdateCodes()
    Dim i As Integer, j As Integer, t As Integer
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim srchStr As String, flName As String, TryAgain As String
    Dim rngX As Range

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
    Set ws1 = ActiveSheet
    MsgBox ("Please select the file you want to match up...")
StartHere:
    flName = Application.GetOpenFilename
    If flName = "False" Then
        TryAgain = MsgBox("You did not select a file.  Do you want to try again?", vbYesNo + vbCritical, "Invalid Entry")
        If TryAgain = vbNo Then
            MsgBox "No file was selected. Exiting From Macro.", vbCritical + vbOKOnly
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            Exit Sub
        Else
            GoTo StartHere
        End If
    Else
        Workbooks.Open FileName:=flName
    End If
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveSheet
    For i = 2 To ws2.Cells(65536, "A").End(xlUp).Row
        t = Len(ws2.Range("A" & i)) - InStr(1, ws2.Range("A" & i), "-")
        srchStr = UCase(Right(ws2.Range("A" & i), t))
        MsgBox (srchStr)
        ''Search Code & Declarations
        Set rngX = ws1.Range("A:A").Find(srchStr, lookat:=xlWhole)
        If Not rngX Is Nothing Then
            MsgBox "Found at " & rngX.Address
            'ADD REST OF THE MACRO HERE
        Else
            MsgBox "No Record Found"
        End If
    Next

End Sub

Open in new window

0
rsernowskiAuthor Commented:
Hi ,

Thanks for the quick response,

I am going to work on the solution today,. so first yes I will have two spreadsheets. One for the commiosn table, the other for the data. One thing, Can I have the macro open, read the required fields, and then close the commison table.
With point 3 , no there will always need to be two different spreadsheets
Sorry, my mistake, codes for sure will be the same from commison table to the data file
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Robert,

If required, you can have the option to read the required fields and close the commissions table. But I don't think that is necessary. Since your commission tables would be in a different spreadsheet, keeping them open or closed won't matter.

Anyways, please test the code and see if it works for you. Remember, this is just a partial solution and I still need to work on it based on your inputs,

Thanks,
Ardhendu
0
rsernowskiAuthor Commented:
Hi

The data file is not blank to start with,  it does contain data which a macro needs to manipulate, In effect the macro just copies and pastes data. It has been working fine. So before your macro works, I need to copy and paste in the account codes. Here is my code as is:

'copy required cells from original file'
       n = ws1.Cells(65536, "B").End(xlUp).Row
       Set Rng1 = ws1.Range("B11:B" & n + 1)
       RngValue1 = Rng1.Value
       Set Rng2 = ws1.Range("d11:D" & n + 1)
       RngValue2 = Rng2.Value
        With ws1
        T_Bulk = .Cells(7, 2)
        T_Buy = .Cells(7, 3)
        T_DTC = .Cells(7, 4)
        T_Date = .Cells(7, 7)
        S_Date = .Cells(7, 8)
        T_Shares = .Cells(7, 9)
        S_Name = .Cells(7, 10)
        S_Number = .Cells(7, 11)
        T_Comm = .Cells(7, 13)
        T_Price = .Cells(7, 14)
        T_Curr = .Cells(7, 15)
        T_Total = .Cells(7, 16)
        End With
        wb1.Close True
   
   'Open template and fill out required info'
    With wb2.Worksheets("TD-complete")
        .Cells(11, 2) = S_Number
        .Cells(11, 3) = S_Name
        .Cells(11, 4) = T_Buy
        .Cells(11, 5) = "Hamp"
        .Cells(11, 7) = T_Price
        .Cells(11, 9) = "=IF(RC[4]=""USD"",IF(RC[-5]=""Sell"",RC[-3]*RC[-2]*R8C9,0),0)"
        .Cells(11, 10) = "=IF(RC[-6]=""Sell"",(RC[-4]*RC[-3])-RC[-2]-RC[-1],IF(RC[-6]=""Buy"",(RC[-4]*RC[-3])+RC[-2],0))"
        .Cells(11, 11) = T_Date
        .Cells(11, 12) = S_Date
        .Cells(11, 13) = T_Curr
         End With
    'paste in client code and qty
       wb2.Worksheets("TD-complete").Range("a11:a" & n + 1).Value = RngValue1
       wb2.Worksheets("TD-complete").Range("f11:f" & n + 1).Value = RngValue2
   
'copy down the rows based upon qty number in col f
  With wb2.Worksheets("TD-complete")
        lRow = Range("F" & Rows.Count).End(xlUp).Row
        If n > 11 Then
        Range("B12:E" & lRow) = Range("B11:E11").Value
        Range("G12:G" & lRow) = Range("G11:G11")
        Range("I11:J" & lRow).Select
        Selection.FillDown
        Selection.NumberFormat = "$#,##0.00"
        Range("K12:M" & lRow) = Range("K11:M11").Value
   End If
       
I was going to set the commsion table as wb3
   
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
If you are going to set commissions table as wb3, then you need to replace all the wb1 in my code with wb3. See below,

- Ardhendu
      Option Explicit
Sub UpdateCodes()
    Dim i As Integer, j As Integer, t As Integer
    Dim wb3 As Workbook, wb4 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim srchStr As String, flName As String, TryAgain As String
    Dim rngX As Range

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set wb3 = ActiveWorkbook
    Set ws1 = ActiveSheet
    MsgBox ("Please select the file you want to match up...")
StartHere:
    flName = Application.GetOpenFilename
    If flName = "False" Then
        TryAgain = MsgBox("You did not select a file.  Do you want to try again?", vbYesNo + vbCritical, "Invalid Entry")
        If TryAgain = vbNo Then
            MsgBox "No file was selected. Exiting From Macro.", vbCritical + vbOKOnly
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            Exit Sub
        Else
            GoTo StartHere
        End If
    Else
        Workbooks.Open FileName:=flName
    End If
    Set wb4 = ActiveWorkbook
    Set ws2 = ActiveSheet
    For i = 2 To ws2.Cells(65536, "A").End(xlUp).Row
        t = Len(ws2.Range("A" & i)) - InStr(1, ws2.Range("A" & i), "-")
        srchStr = UCase(Right(ws2.Range("A" & i), t))
        MsgBox (srchStr)
        ''Search Code & Declarations
        Set rngX = ws1.Range("A:A").Find(srchStr, lookat:=xlWhole)
        If Not rngX Is Nothing Then
            MsgBox "Found at " & rngX.Address
            'ADD REST OF THE MACRO HERE
        Else
            MsgBox "No Record Found"
        End If
    Next

End Sub

Open in new window

0
rsernowskiAuthor Commented:
ok, does all my other code go in the spot " add rest of the macro here'
Regards
RS
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
It depends on your code.

The "add rest of the macro here" was meant for code related to the commissions code assignment.  

- Ardhendu

0
rsernowskiAuthor Commented:
Hi,

A couple responses back is the rest of my code. Please have a look
Thanks
Robert'
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Robert,
I did see your code but I am not sure what it was doing. Can you please explain? :)

- Ardhendu
0
rsernowskiAuthor Commented:
Hi
Sorry
here is the first part

Dim FileName As String
    Dim i As Integer, lstrow As Integer
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, j As Integer
   Dim T_Buy, T_Date, S_Date, T_Shares, S_Name, S_Number, T_Comm, T_Price, T_Curr
    Dim Rng1 As Range, Rng2 As Range, RngValue1() As Variant, RngValue2() As Variant
    Dim rngX As Range
    Dim iEnd As Long
    Dim u As Integer
    Dim k As Long
    Dim lRow As Long
    Dim kRow As Long
    Dim OutApp As Object
    Dim OutMail As Object
   
                   
    FileName = InputBox("Enter Original File Name")
    Set wb1 = Workbooks.Open("C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\" & FileName & ".xls")
    Set wb2 = Workbooks.Open("C:\Documents and Settings\rsernowski.R-SERNOWSKI\Application Data\Microsoft\Templates\Hampton Trade Advice.xlt")
    Set wb3 = Workbooks.Open("C:\Documents and Settings\rsernowski.R-SERNOWSKI\Desktop\Hampton Accounts.xlsx")

Originally  the file is received from a main frame.
 the file is opened up with excel and all the cells copied
the original file is closed.
the contents are copied into a template

since the orginal file only included 1 line, I then had to copy down details of the "trade"

From the original file, the client codes are kept in a sepaerate part of the spreadsheet than the header details so I had to copy all the clients code

This is where I was going to add in my commison amounts, I think I needs to come after I paste in details about account code.

Hope this helps
RS
0
rsernowskiAuthor Commented:
Hi Ard
Perhaps I am looking at the second lookup values wrong? Certain accounts get 30 $ or others 0$, if the transaction is buy or sell. Only a few will have 30$ if it is buy and 0$ if it is a sell.Do you think I should use and if statement there is a transaction type in col r
0
Ardhendu SarangiSr. Project ManagerCommented:
hi Robert,

I think you need a if condition here to determine whether this is a buy or a sell.  how are you going to determine that?

- Ardhendu

p.s: where are you located? are u in NYC?
0
rsernowskiAuthor Commented:
Hi Ardhendu

No I am in toronto, you?

I was going to use a case like this:

With ws2
 Case Range("D11").Value = "Buy"
    For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
        Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            ws2.Range("H" & i) = rngX.Offset(0, 4).Value
        End If
    Next
   Case Range("D11").Value = "Sell"
If .Cells("D12") = "Sell" Then
    For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
        Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            ws2.Range("H" & i) = rngX.Offset(0, 5).Value
        End If
    Next

I am getting a select case required.
0
rsernowskiAuthor Commented:
Hi Ard
Ok I am closer but what am I missing


    Dim ws1 As Worksheet, ws2 As Worksheet, i As Integer
    Dim rngX As Range
    Dim trantype As String
    Set ws1 = Sheets("Client Codes & Commissions Tbl")
    Set ws2 = Sheets("Data")
   
   
With ws2
Set trantype = rng.Range("D11")
Select Case trantype
 Case Is = "Buy"
    For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
        Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            ws2.Range("H" & i) = rngX.Offset(0, 4).Value
        End If
    Next
   Case Is = "Sell"
   For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
        Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
        If Not rngX Is Nothing Then
            ws2.Range("H" & i) = rngX.Offset(0, 5).Value
        End If
    Next
End Select
End With
End Sub
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
Let me take a look. Looks like you are pretty close here.

- Ardhendu
0
Ardhendu SarangiSr. Project ManagerCommented:
Ok try this once...


Sub Robert()
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Integer
    Dim rngX As Range
    Set ws1 = Sheets("Client Codes & Commissions Tbl")
    Set ws2 = Sheets("Data")


    Select Case ws2.Range("D11")
    Case "Buy"
        For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws2.Range("H" & i) = rngX.Offset(0, 4).Value
            End If
        Next
    Case "Sell"
        For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws2.Range("H" & i) = rngX.Offset(0, 5).Value
            End If
        Next
    End Select

End Sub

Open in new window

0
rsernowskiAuthor Commented:
works like a charm!
now I would like to put Set ws1 = Sheets("Client Codes & Commissions Tbl")
on a different spreadsheet called Client Codes and worksheet labled accounts
0
Ardhendu SarangiSr. Project ManagerCommented:
To open a separate file, you would need the code to be something like this -

- Ardhendu
Sub Robert()
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Integer
    Dim wb1 As Workbook, wb2 As Workbook
    Dim rngX As Range
     
    Set wb2 = ThisWorkbook
    Workbooks.Open ("C:\TEMP\Client Codes.xls")   ' ENTER THE CORRECT PATH HERE
    Set wb1 = Workbooks("Client Codes.xls")
    Set ws1 = wb1.Sheets("Accounts")
    Set ws2 = Sheets("Data")
    
    Select Case ws2.Range("D11")
    Case "Buy"
        For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws2.Range("H" & i) = rngX.Offset(0, 4).Value
            End If
        Next
    Case "Sell"
        For i = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws1.Range("A:A").Find(ws2.Range("A" & i), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws2.Range("H" & i) = rngX.Offset(0, 5).Value
            End If
        Next
    End Select

End Sub

Open in new window

0
rsernowskiAuthor Commented:
Excellent, Thank you I got it to work, I use a filename = Inputbox  to have the user enter the original file name.
I can work on closing the client codes spreadsheet , that is no problem.  But now where do I insert this in the original code?
0
rsernowskiAuthor Commented:
Hi
I was thinking that if I use with and end with in each of my loops then I can run the "lookup" after I past in the data. Everythiing is running fun up to my commision lookup. I had to adjust the ws and wb refernces but I get an error stopping on:
Select Case ws2.Range("D11")
saying:
runtime error 91, object  variable or with block variable not set

here is the amended code to "fit"  

Look up commission

With wb2.Worksheets("TD-complete")
Set wb3 = Workbooks.Open("C:\Documents and Settings\rsernowski.R-Sernowski\Desktop\accounts.xls")
Set ws3 = wb3.Sheets("accounts")
   
Select Case ws2.Range("D11")
   Case "Buy"
        For j = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws3.Range("A:A").Find(ws2.Range("A" & j), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws2.Range("H" & j) = rngX.Offset(0, 4).Value
            End If
        Next
    Case "Sell"
        For j = 11 To ws2.Cells(65536, "A").End(xlUp).Row
            Set rngX = ws3.Range("A:A").Find(ws2.Range("A" & j), lookat:=xlWhole)
            If Not rngX Is Nothing Then
                ws1.Range("H" & j) = rngX.Offset(0, 5).Value
            End If
        Next
    End Select
End With
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Robert,
you are getting an error in the select statement because the ws2 is not assigned any worksheet. you need to assign ws2 some value similar to Set ws3 = wb3.Sheets("accounts").

As regards to adding it to your original code, you can add it as a separate function to the end of your code and just use a Call statement whenever you it. It would be easier and cleaner to manage too.

- Ardhendu
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rsernowskiAuthor Commented:
Hi Ardhendu
Thanks , I got it and I added some means to total columns.Would you have some time to review and make changs to make it more efficient?
0
rsernowskiAuthor Commented:
Thanks for all your help and assistance. Appreciate your time and effort! Thanks again!
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Robert,
Thanks for the grade. I can definitely review your code and see if any more changes are required. You can post your code here.
0
rsernowskiAuthor Commented:
Hi ,
thanks , I just posted another question how to fix some save errors on the same code. I will make the changes and then take you up on the review
0
rsernowskiAuthor Commented:
Hi
Can you help, I can't figure out why this is not working. It looks like it is but nothing gets pasted. I just want to copy from ws1 to ws2. everything is declared
Set wb1 = ActiveWorkbook
 Set ws1 = ActiveSheet

     
'copy required cells from original file'
    n = ws1.Cells(65536, "B").End(xlUp).Row
    Set Rng1 = ws1.Range("B11:B" & n + 1)
    RngValue1 = Rng1.Value
    Set Rng2 = ws1.Range("d11:D" & n + 1)
    RngValue2 = Rng2.Value

'paste in client code and qty
 Set wb2 = Workbooks.Open("C:\Documents and Settings\rsernowski.R-SERNOWSKI\Application Data\Microsoft\Templates\ Advice Two.xlt")
       wb2.Worksheets("TD-complete").Range("a11:a" & n + 1).Value = RngValue1
       wb2.Worksheets("TD-complete").Range("f11:f" & n + 1).Value = RngValue2
   
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi Robert,
Can you paste the complete code or email me at ardhendu.s@gmail.com

Thanks,
Ardhendu
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.