VBA - How to write a code for: IF column A = "milk" then column B = "2.50".

[Justyna P]

Hello, I'm a very new (I mean literally an hour) in using VBA and Macros and I'm trying to write my very first if statement. The issue is that when I try on a single cell everything is fine, but I am trying to apply it to the whole column! More specifically - if something occurs in column A then a value pops out in column B next to it. Like when there is a name of product in A then the price pops out in B. Can anyone help with that please?

 

[Logit]

Option Explicit

Sub srchCpy()
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(A2,Sheet2!A:B,2,FALSE)"
    End With
End Sub

The above assumes you are seeking dollar amounts to appear on sheet 1 in column B. The product names are located in sheet 1/column A beginning with row #2. Cell A1 has the term "Product". Your master price list is located in sheet 2 with product name in column A and associated price in column B. Neither column A or column B in sheet 2 has a header.

 

[Logit]

Here is a strictly VBA approach without using formula :

Option Explicit

Sub srchCpy()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim searchValue As Variant
    Dim resultValue As Variant
    Dim lastRow1 As Long, lastRow2 As Long
    Dim searchRange As Range, foundCell As Range
    Dim i As Long

    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' Get the last row in each worksheet
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Set the range to search in Sheet2
    Set searchRange = ws2.Range("A1:A" & lastRow2)
    
    ' Loop through each cell in column A of Sheet1
    For i = 2 To lastRow1
        searchValue = ws1.Cells(i, "A").Value
        ' Search for the value in Sheet2 column A
        Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            ' If found, copy the corresponding value from column B
            resultValue = foundCell.Offset(0, 1).Value
        Else
            ' If not found, set the result to empty
            resultValue = ""
        End If
        ' Write the result to column B of Sheet1
        ws1.Cells(i, "B").Value = resultValue
    Next i
End Sub