title: vba inserting a values based on another column
body: <p>I need assistance in creating a macro that helps me insert a value in a new column i have created</p>
  
  <p>For example i have 3 countries, Belgium(BGD), Switzerland(BHS) and England(ENG) in column B. And if the value in column B is BGD, the new column should insert a value of 8261 and for switzerland, its 8159.</p>
  
  <p>This is what i have tried.
  Thanks.</p>
  
  <pre><code>Sub Entities()
  Dim Found As Range
  Dim LR As Long
  Dim ws As Worksheet
  Dim rng As Range
  Dim Lrow As Long
  Dim cell As Range
  
  
  Set ws = Sheets("Europe")
  
  Set Found = Rows(1).Find(what:="Total Amount in Foreign Currency", LookIn:=xlValues, lookat:=xlWhole)
  If Found Is Nothing Then Exit Sub
  LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
  Found.Offset(, 1).EntireColumn.Insert
  Cells(1, Found.Column + 1).Value = "Entities"
  
  Set rng = Range("B2:B127")
  Select Case rng
    Case "BGD"
    Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Value = 8261
  
    Case "BHS"
    Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Value = 8159
    Case "ENG"
    Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Value = 8550
  
    End Select    
  End Sub
  </code></pre>