If column A equals a certain value, generate a value in column B. Check each cell in column A --> Generate a value in each cell in column B

Question

I have a very simple question that I can't find the answer to.

Here is an example of what I'd like to do:

Sample Data

If a row in column A = 'Sandwiches', I would like column B to display 'It's a Sandwich'
If a row in column A = 'Wraps', I would like column B to display 'It's a Wrap'
etc. etc.

So far, I am able to do this for the first row. I'm having trouble creating a for loop to loop through all the available cells.

Here is what I have so far (was thinking of adding Else If statements for different values later):

Current If Statement

No pictures of code here. Please [edit] your question with the code as text. — BigBen, Nov 08 '21 at 18:57
If you want vba, first find the [last row with data](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) then you would do a simple for: `For i = 2 to lastrow` — Scott Craner, Nov 08 '21 at 18:59

score 0 · Answer 1 · answered Nov 08 '21 at 19:50

0

Akash you do not need to write vba codes for this. You may use a lookup table and apply Vlookup formula as @BigBen said.

Apart from this, this formula can make you happy if you ignore some spelling errors. Enter this in cell B2 and paste along the way down.

="It's " & A2

answered Nov 08 '21 at 19:50

Ozgun Senyuva

466
5
12

Thank you for your help, Ozgun. I'm looking to build upon a macro and would like to write the logic in VBA. This is a start though, I will keep this solution in mind. – Akash Panchal Nov 08 '21 at 21:38

Mohamad TAGHLOBI · Accepted Answer · 2021-11-08T21:58:38.347

0

If you prefer the use of VBA code instead a formule as Ozgun Senyuva suggested. You can try this code:

Sub Food_and_thing()
  Dim myLine As Double
  Dim myFood As String
  Set SavedData = Sheets("SavedData")
  myLine = 2
  
  With SavedData
    Do While .Cells(myLine, 1) <> "" 'Assume that Category is in column A, and Generated Asset Name is in column B
      myFood = " an other thing"
      If .Cells(myLine, 1) = "Sandwiches" Then
         myFood = " a Sandwich"
      ElseIf .Cells(myLine, 1) = "Wraps" Then
         myFood = " a Wrap"
      ElseIf .Cells(myLine, 1) = "Salads" Then
        myFood = " a salad"
      End If
      .Cells(myLine, 2) = "It's" & myFood
      myLine = myLine + 1
    Loop
  End With
End Sub

edited Nov 08 '21 at 21:58

answered Nov 08 '21 at 20:15

Mohamad TAGHLOBI

581
5
11

Thank you for this solution Mohamad. The logic for saying "It's another thing" populates, but it doesn't generate asset names from the If, else if statements – Akash Panchal Nov 08 '21 at 21:37
Sorry, There were two errors: " Wraps" instead "Wraps" and myFood=" an other thing was outside of the loop. I correct the code above and tested it, it's working.. – Mohamad TAGHLOBI Nov 08 '21 at 22:01
Awesome, this works. Thanks, Mohamad! – Akash Panchal Nov 09 '21 at 19:44
Akash! I'm glad it helped. – Mohamad TAGHLOBI Nov 09 '21 at 20:09

If column A equals a certain value, generate a value in column B. Check each cell in column A --> Generate a value in each cell in column B

2 Answers2