CODEWITHSUNDEEP
excelvba
T. Mccallum
T. Mccallum

Reputation: 13

VBA to check cells and display value found on another sheet

Currently working in Excel VBA for the first time and am looking to use a command button that when pushed will check values in 3 cells and then display a value found on another sheet in a cell below. So far i have:

Private Sub CommandButton1_Click()
= IF cells(b,1).Value2 = "A"
And cells(b,2).Value2 = "OO"
And cells(b,3).Value2 = "<48"
Then calls(b,10).Value2 = Worksheets("Rates"). cells(b,4)
End Sub

This was my first attempt although it is not working and im unsure how to move forward. Any help is greatly appreciated

Upvotes: 1

Views: 1364

Answers (1)

urdearboy
urdearboy

Reputation: 14580

A couple things to note:

  1. This can be done with a concatenated string and VLOOKUP (if you are open to non-VBA solution)
  2. Add Option Explicit to the top of your code, this will catch typos like calls(b, 10)
  3. You do not start an If statement with =
  4. The proper syntax for range reference with cells is Cells(Row Index, Column Index) - you have this backwards but since you are using static ranges, Range may be a little more intuitive
  5. Try to qualify all objects with worksheets. Notice your Cells don't state which sheet each time. Be explicit and you are less likely to have errors in the future
Option Explicit

Private Sub CommandButton1_Click()

Dim ws as Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update

If ws.Range("B1") = "A" And ws.Range("B2") = "OO" and ws.Range("B3") = "<48" Then
    ws.Range("B10").Value = Worksheets("Rates").Range("B4").Value
End If

End Sub

Upvotes: 1

Related Questions