0

I am trying to find a oneliner VBA code that will use structured table reference to edit values in a column given as "United States of Amercia - USA". The aim is to delete everything before the hyphen (-) and only the "USA" part left. The code so far I have written is as follows:

Dim Rng As Range
Dim Cell As Range
Set Rng = Range("CNSTime[[#Data],[Country]]")
    For Each Cell In Rng
    Cell.Value = Right(Cell, Len(Cell) - InStr(Cell, "-") - 1)
Next Cell
End

Anyone who can help replace this code with a one liner structured formula for example:

Range("CNSTime[[#Data],[Country]]").formula = xxxxxxxxxxxxxx

My table name is CNSTime Set CNSTime = ActiveSheet.ListObjects("CNSTime") and the header of the column I am trying to fix is Country.

braX
  • 11,506
  • 5
  • 20
  • 33
Shoaib
  • 11
  • 1
  • What happens when you write the formula in the worksheet and then use that as a guide? You already have the architecture of the formula, you just need to apply it in a table context and then use that. – teylyn Oct 03 '21 at 20:39
  • I tried that but it doesn't work. – Shoaib Oct 04 '21 at 21:44
  • The other such formulas that I have used in VBA based on structured table formats work seemlessly, this is the one which is designed as it its in one column and looking at another ccolumn to see what needs to be calculated. So I may need to create another column, calculate that column and delete the original one as one solution, but I was hoping that someone may have a better solution here. Thanks. – Shoaib Oct 05 '21 at 10:26
  • Okay so I have resolved this bit that I a simple =Right(RC[-1],3) is better than a Lens and Instr formula as my text string only have three letters after the hyphen. But I am still struggling to run it directly on the column, rather than add a new column and then do a RC[-1] bit. – Shoaib Oct 05 '21 at 11:13

0 Answers0