CODEWITHSUNDEEP
excelif-statementvba
FotoDJ
FotoDJ

Reputation: 351

VBA if conditions

I wrote if condition which is shown below, it looks for value "Current Status:" in row A and copy B value from that row to other sheet, if not not found "0" is placed in a cell, it works fine. Sometimes value "Current Status:" might be in a different cell than A18, it might show up in the range from A16 to A20, how can I modify that code to find it within the range and copy corresponding value?

If ws.Range("A18") = "Current Status:" Then
    .Range("V" & NewRow) = ws.Range("B18")
     Else
        .Range("V" & NewRow) = "0"
      End If

Upvotes: 0

Views: 55

Answers (2)

u8it
u8it

Reputation: 4296

Just put your code in a For loop... or use VLookup like Scotty suggested. It's basically the same thing. A For loop is more flexible but less optimized (VLookup is faster). They both run on the order of fractions of a μs/cell.

For Each c In Range("A16:A20")
    If c.Value2 = "Current Status:" Then
        .Range("V" & NewRow) = c.Offset(0, 1)
        Exit For
    Else
        .Range("V" & NewRow) = "0"
    End If
Next

If using a For loop, this is a little bit more code than what's above but a better structure...

'Define a value holder variable where it's scope makes sense
Dim NewValue As String

'... other code here ...

'Default: NewValue = ""
NewValue = ""
For Each c In Range("A16:A20")
    If c.Value2 = "Current Status:" Then
        NewValue = c.Offset(0, 1)
        'Exit For is optional in this case. It matters if
        'there are multiple matches... do you want first or last result?
        Exit For
    End If
Next
'Assign NewValue to cell
.Range("V" & NewRow) = NewValue

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

Use Vlookup:

.Range("V" & NewRow) = "0"
On Error Resume Next
.Range("V" & NewRow) = Application.WorksheetFunction.VLookup("Current Status:", ws.Range("A:B"), 2, False)
On Error GoTo 0

This will put 0 in the cell then try to replace it with the value returned from the vlookup. If "Current Status:" is not found in column A on ws then it will throw an error and be ignored leaving 0 in the cell.

If the value is found it will return the value in Column B and put that in place of the 0

Upvotes: 1

Related Questions