CODEWITHSUNDEEP
excelvba
moodyteddy
moodyteddy

Reputation: 11

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

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.

Upvotes: 1

Views: 162

Answers (1)

Captain
Captain

Reputation: 2218

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

  1. Set sht2=Application.ActiveWorkbook.Sheets("Sheet1") and at the end Set sht2 = Nothing
  2. Take out the ReDim array_b(row_no - 1, 3) wasn't being used and was using a variable you hadn't set
  3. 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

Upvotes: 0

Related Questions