How to carry out IF for list of values without using .activecell and .select

Question

I'm a beginner in VBA and just started trying to do up a macro to automate the following process in excel.

I have 2 rates, one BUY RATE (column R) and one SELL RATE (column S). What I have to do is to compare the 2 ratings and select the higher rating unless the Buy currency (Column E) is USD. I then want to input the relevant rating selected into column T.

However, I’m trying to do it for subsequent rows without using '.Activecell' and '.Select'. but I’m not very sure how to go about this.

Actually, there is still a second part to what I want to accomplish. Cause I’m trying to learn and figure it out one step at a time.

Anyway,I have 2 worksheets, In worksheet 1, I have a list of index numbers (Column A) as well as the ratings to be computed above in Column T.

In worksheet 2, I have another list of index numbers (but with additional “00” at the back)(Column A). Each index number then has a corresponding letter in one cell (Column M) and a number in another (Column I).

What I am required to do is to retrieve the matching letter and number from Worksheet 2, put them together and then compare to the ratings in worksheet 1 to see if they match. I have listed out the steps to take in order to complete this task but I can’t seem to figure out how to do it yet

1)Retrieve Index Number from Worksheet 1 and concatenate with “00”

2)Do a lookup function using the value from above, to retrieve the corresponding index number and alphabet.

3)Concatenate the above “Alphabet” and “Number” together (Enter into new cell (column C) in worksheet 1

4)Compare Risk rating with value obtained from step 3

5)If Risk Rating < Value from Step 3 , enter do not match in new cell, Worksheet 1, column D

6)Repeat for each index number in worksheet 1. (Number of index numbers in worksheet one vary every week)

Following the link provided, I came up with the following syntax.

Sub Comparison() Dim sht2 As Worksheet Dim last_row As Integer Dim row_no As Integer Dim buyccy As String Dim selccy As String Dim buyrate As String Dim selrate As String Dim y As String

Set sht2 = Sheets("sheet2")
    last_row = sht2.Cells.Find(What:="*", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For row_no = 2 To last_row
 If IsError(sht2.Range("R" & row_no)) Or IsError(sht2.Range("S" & row_no)) Then
    y = "#N/A"
   Else
   buyrate = sht2.Range("R" & row_no)
   selrate = sht2.Range("S" & row_no)
   buyccy = sht2.Range("E" & row_no)
   selccy = sht2.Range("G" & row_no)

    If buyrate >= selrate And buyccy = "USD" Then
            y = selrate
        ElseIf buyrate >= selrate And buyccy <> "USD" Then y = buyrate
        ElseIf buyrate < selrate And selccy = "USD" Then y = buyrate
        Else: y = selrate
    End If

sht2.Range("T" & row_no).Value = y
Next

Set sht2 = Nothing

End Sub

Not really sure where I'm going wrong though. Apologies but I'm really, really new to VBA. Would sincerely appreciate any insight that can be provided on this.

We'll done for recognising `Select` is to be avoided! [this link](http://stackoverflow.com/a/10717999/445425) might get you started — chris neilsen, Aug 28 '14 at 05:59
Why are you doing this via VBA? Could you not just put a worksheet formula in T2 and copy that down your range? (`=IF(R2=S2,R2,IF(AND(R2>S2,E2="USD"),R2,S2))`) — Captain, Aug 28 '14 at 06:48
@Captain There's actually a second part of the task that I want to acomplish. Still new so trying to figure it out and learn one step at a time. I've actually edited the question above with the entire work process. Would appreciate if you could provide some insight on how I can go about it. Thanks — moodyteddy, Aug 28 '14 at 07:36

Captain · Answer 1 · 2014-08-29T09:22:19.100

0

I made a few little tweaks to your code, and it seems fine...

Set sht2=Application.ActiveWorkbook.Sheets("Sheet1") and at the end Set sht2 = Nothing
Take out the ReDim array_b(row_no - 1, 3) wasn't being used and was using a variable you hadn't set
Changed the mechanism for finding the last_row... All sorts of strange behaviours occur with the different methods, but the one I have always used is last_row = sht2.Cells.Find(What:="*", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Alternative addressing is also possible using sht2.Cells(rownum,colnum) rather than making a string of the cell address...

Finally - I think all that you mention above would be simple to achieve with just worksheet functions - but maybe less fun ;-)

Whole code:

Sub Comparison()

 Dim sht2 As Worksheet
 Dim array_b()
 Dim last_row As Integer
 Dim row_no As Integer
 Dim buyccy As String
 Dim buyrate As String
 Dim selrate As String
 Dim y As String

 Set sht2 = Application.ActiveWorkbook.Sheets("Sheet1")

 'last_row = sht2.Range("A1").End(xlDown).Row
 last_row = sht2.Cells.Find(What:="*", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 'ReDim array_b(row_no - 1, 3)

 For row_no = 2 To last_row
   If IsError(sht2.Range("R" & row_no)) Or IsError(sht2.Range("S" & row_no)) Then
     y = "#N/A"
   Else
     buyrate = sht2.Range("R" & row_no)
     selrate = sht2.Range("S" & row_no)
     buyccy = sht2.Range("E" & row_no)

     If buyrate >= selrate And buyccy <> "USD" Then y = buyrate Else y = selrate
   End If
   sht2.Range("T" & row_no).Value = y
 Next

 Set sht2 = Nothing

End Sub

edited Aug 29 '14 at 09:22

answered Aug 28 '14 at 11:00

Captain

2,148
15
13

Thanks for the assistance. I'm actually just trying learning VB at the same time. Anyway, the buyrate and selrate are derived from formulas and as such, may be error values. If it is an error value, how can i set "#N/A" into variable y? I tried using `IF iserror()` but can't seem to get about it. – moodyteddy Aug 29 '14 at 07:41
What do you want it to do if either/both rate is an error? – Captain Aug 29 '14 at 07:47
If either is an error, I want the corresponding cell (same row) in column T to display an error as well. I was thinking along the lines of `IF iserror(buyrate) Or iserror(selrate) then else` and continue with the formula above. – moodyteddy Aug 29 '14 at 07:57
Along the right lines - unfortunately the setting of buyrate with an Error breaks it - so we do the test prior to setting the values (referencing the ranges directly)... I have updated the code... – Captain Aug 29 '14 at 09:23
thanks for the help so far. Actually, I've changed the original `If` criteria and it worked previously. However, when i include the new test that you have included, I get an error, "NEXT WITHOUT FOR". I've updated the code in my original post. Would greatly appreciate your assistance – moodyteddy Sep 01 '14 at 01:05
Looks like you are missing an `End If` before the line `sht2.Range("T" & row_no).Value = y`... There needs to be 2: 1 for the check for `IsError` and the other for your logic block... – Captain Sep 01 '14 at 07:29
It works!!! Thank you so much, you're a real life saver. Anyway, sorry to bother you but I have another question, [link](http://stackoverflow.com/questions/25602712/lookup-or-find-with-multiple-values-and-multiple-return-values) , would it be possible to trouble you to help me take a look at it? Thanks again!!! – moodyteddy Sep 01 '14 at 09:28
Depends if you give me points by accepting this answer or not ;-) – Captain Sep 01 '14 at 09:34

How to carry out IF for list of values without using .activecell and .select

1 Answers1