0

I'm having trouble starting this VBA code.

I have two spreadsheets Sheet 1 and Sheet 2 that have data for clients. The one column in sheet 2 has a value set to a number + Sales Office location(ie 750-UNKNOWN, 841-UNKNOWN, 160-NC - GREENVILLE). I want to search that column for the "Unknown" value and use the number from that value ie 750 to match it from another worksheet (Sheet 1) by looking up the appropriate value and updating the cell in sheet 2 to that number and the name in the next cell by concatenating it in the cell.

Sheet 2

  • Reference

-771-UNKNOWN (cell H2)

  • 720-UNKNOWN (Cell h3)

    • Sheet 1 A B
      New FSO New FSO Name
      105 (A2) Kentucky (B2)
      60 (A3) Taft-Hartley (B3)

I don't know where to begin with doing this.

Additionally, I need to set a rule that if the value isn't found during the search ie if 750 isn't in the list, then return "No Sales Office located"

Thank you!

Improve this question
1
  • Kimberly, welcome to SO. You should do some research on VBA, learn how it works and how to use. Then, if you have an specific question, you should post it here.
    – GCSDC
    Commented Apr 11, 2019 at 19:50

1 Answer 1

Reset to default
0

If(mid(h2, len(h2)-7),7) = "unknown", left(h2, len(h2)-7),"no sales office") the 7 would change if unknown is Utah (then it would be 4).

Improve this answer
2
  • Thank you! Is there a way to automate this by adding code to a button to go through each line automatically and make the updates?
    – Kimberly Mitchell
    Commented Apr 11, 2019 at 19:58
  • I've tried this and it doesn't add the value from the vlookup to the cell=IF(MID(K3,(LEN(K3)-5),7)="unknown",VLOOKUP(LEFT(K3,3),FinalFSOTbl!A:B,1,FALSE),"no sales office")
    – Kimberly Mitchell
    Commented Apr 11, 2019 at 20:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.